Works SS and DB: AVG and COUNT Functions Include Blank Cells (128750)


The information in this article applies to:

  • Microsoft Works 2.0
  • Microsoft Works 2.0a
  • Microsoft Works 3.0
  • Microsoft Works 3.0a
  • Microsoft Works 3.0b
  • Microsoft Works 4.5
  • Microsoft Works 4.5a
  • Microsoft Works for Windows 95, version 4.0
  • Microsoft Works for Windows 95, version 4.0 4.0a
This article was previously published under Q128750

SUMMARY

In a Works Spreadsheet or Database, COUNT() and AVG() functions are based on a range of cells. If a cell is blank, it is not included in the COUNT or AVG function. However, if the cell contains a formula that returns a blank, such as an IF statement, that cell is included in the calculation as a zero. In this case, the blank is not treated as an empty cell.

MORE INFORMATION

If a cell contains a formula that returns a blank (""), that cell is treated by the COUNT() and AVG() functions as if it had a numeric entry of 0. It is not treated the same way as an empty cell. Most functions available in Works cannot distinguish between a text blank and a value of zero. A text blank is not the same as an empty cell.

WORKAROUNDS

The methods listed below work around this situation. The first method doesn't work with Works version 2.0x for Windows.

Method 1

Any cell containing a formula that returns a numeric value of zero will be included in the calculation of the average or count, but a blank cell will not. Because an empty cell will not be treated properly with this method, you must ensure that all the cells in the range contain a formula.

Assume the range A4:A10 contains formulas for which you want to calculate an average.

  1. In cell B4, enter this formula to test whether a cell contains a formula returning a blank or text:

    =ISERR(EXACT(A4,""))

    A formula returning a number will give a value of 1, a blank or text will return a zero, and an empty cell will return a value of 1.
  2. Fill the formula down for the same range as you are testing, in this case from B4 to B10.
  3. In the cell you want to contain the average, enter the following:

    =SUM(A4:A10)/SUM(B4:B10)

    For a count, use:

    =SUM(B4:B10)

Method 2

Empty cells are treated properly with this method, but values of zero are ignored and not included in the average or count. Do not use this method if a value of zero is possible, and should be included in calculating the average or count.

For example, let's assume you want to calculate an average for the formulas in the range of cells A4:A10.

  1. In cell A11 enter the following formula to test if the value in each cell is zero. Replace the cell references with the proper references for your spreadsheet.

    =SUM(A4<>0,A5<>0,A6<>0,A7<>0,A8<>0,A9<>0,A10<>0)

    The result of this formula is the count.
  2. In cell A12, enter the following formula. (Note: "A11" is the cell address A11, not the word "All".)

    =SUM(A4:A10)/A11

    The result of this formula is the average.
Modification Type:MajorLast Reviewed:11/15/2004
Keywords:kbhowto kbinfo KB128750
©2004 Microsoft Corporation. All rights reserved.