Works SS and DB: AVG and COUNT Functions Include Blank Cells

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.