There are several situations where the AVERAGE and COUNT functions will count blank values as 0, thus returning an incorrect value.
In ClarisWorks Spreadsheet or ClarisImpact Table:
1. Individual cell references are used instead of cell ranges.
2. If the cell values are the result of an IF function which returns a blank value. The only known solution is to use the Find or Match Records feature of the database to meet the criteria you were trying to meet with the IF function.
In the database:
3. In calculation and summary fields.
For example, given:
Cell or Field Value
A1 2
A2 blank
A3 4
Spreadsheet or Table formula = AVERAGE(A1ÉA3) = 3 Correct: (2+4)/2=3
Spreadsheet or Table formula or Database Calculation = AVERAGE(A1,A2,A3) = 2 Incorrect: (2+4)/3=2
Solution one:
In a database, the Summary field returns values for the records you are currently viewing. So you can use the following method to discount blank values:
1. Choose Match Records from the Organize menu.
2. Type this formula: ISBLANK ('Field you're averaging').
3. Click the OK button.
4. Choose Hide Selected from the Organize menu.
Now the only records in the found set are ones that have data in the field you need to average. Since Summary fields only total records that are in the found set, all blank fields will be excluded from the average.
Solution two:
When it is not possible to AVERAGE a cell range (which returns the correct AVERAGE), the following formulas will create the correct divisor using the COUNT function, even without isolating the records with blank values:
Spreadsheet or Table = SUM(A1,A2,A3)/COUNT(A1,A2,A3)
Database Calculation field (which should be used to calculate the average of two or more number fields on a per record basis):
Average (Calculation, Number result) =SUM('num1','num2','num3')/COUNT('num1','num2','num3')
Database Summary field (which should be used to calculate the average of a single number field across a range of records):
Average (Summary, Number result) = SUM('num1')/COUNT('num1')
Note: A Summary field must be placed in a Leading or Trailing Grand Summary Part, or in a Sub-Summary when Sorted by (Some Field) Part on the Layout. Additionally, you must sort (if the field is in a Sub-Summary Part) and select View menu, Page View (or print) to see the results of a Summary field.