Microsoft KB Archive/71169

Works: Finding the Average Value of Numbers in the Spreadsheet

PSS ID Number: Q71169 Article last modified on 06-12-1996

1.00 1.05 2.00 3.00

MS-DOS

The information in this article applies to:

- Microsoft Works for MS-DOS, version 2.0

SUMMARY

To find the average of the values of cells that actually contain data, use the COUNT function to find the number of cells in a range or set. For example, to calculate the number of non-blank cells in cells A3, A5, A7, use the following formula:

=COUNT(A3:A3)+COUNT(A5:A5)+COUNT(A7:A7)

Similarly, the equation, COUNT(C1:C10) will return the number of non-blank cells in the range C1 through C10. If you reduce the range of cells in the COUNT function to just one cell, the function will return the number 1 if there is a number in that cell, and 0 if there is no number in that cell.

To compute the average of the values contained in the non-blank cells, use a formula similar to the following:

  =(A3+A5+A7)/(COUNT(A3:A3)+COUNT(A5:A5)+COUNT(A7:A7))

MORE INFORMATION

A situation when the above procedure would be desirable would be if you to wanted to find the average of a variable set of numbers. For example, suppose there are two or three numbers in various places in your spreadsheet, and you want to obtain the average of only those numbers that exist. Blank cells shouldn’t count.

The averaging problem is easy to solve if counting blank cells as 0 (zero) is acceptable. If the three numbers are in cells A3, A5, and A7, you could use the following formula:

=(A3+A5+A7)/3

The problem becomes more complicated if you want to obtain an average of only those cells that contain data. For example, assume cell A3 contains the number 2, cell A7 contains the number 3, and cell A5 is blank.

KBCategory: kbother KBSubcategory: dworkskb

Additional reference words: 1.00 1.05 2.00 3.00

Copyright Microsoft Corporation 1996.