Microsoft KB Archive/126030

= Works: Statistical Calculations Performed on NA Function Return N/A =

Article ID: 126030

Article Last Modified on 11/24/2003

-

APPLIES TO


 * Microsoft Works 2.0 Standard Edition
 * Microsoft Works 3.0 Standard Edition
 * Microsoft Works 3.0a
 * Microsoft Works 3.0b
 * Microsoft Works 4.0 Standard Edition
 * Microsoft Works 4.0a
 * Microsoft Works 4.5 Standard Edition
 * Microsoft Works 4.5a

-



This article was previously published under Q126030



SYMPTOMS
If the statistical function Sum, Average (AVG), Minimum (MIN), Maximum (MAX), Standard Deviation (STD), or Variance (VAR) is used in a database report summary or in a spreadsheet, N/A is displayed as the result.



CAUSE
This occurs if one or more records or cells within the computation range contain N/A (as a result of using the NA function). For example:

=IF(A1=0,NA,A1)



RESOLUTION
To perform calculations without receiving N/A as the result, use text rather than the function, or exclude the cells containing the NA function. Below are some examples:

Works 3.0, 4.0, 4.5, and 4.5a
In the Spreadsheet module:

Use the following IF function, which displays the text "N/A." For example, if you want a cell to display N/A if cell A1 is 0, then the formula should be:

=IF(A1=0,"N/A",A1)

In the Database module:

Enter N/A as text instead of using the NA function, or use an IF statement like the one above referencing field names instead of cell references. For example:

=IF(Field1=0,"N/A",Field1)

NOTE: The cells containing N/A are treated as zeros.

Works 2.0 for Windows
In the Spreadsheet module:

Exclude the cell(s) containing the NA function from the range reference in your formula. For example, if you have numbers in cells A1 through A9, with an NA function in cell A4, and you want to find the sum of the cells containing values, use the following formula:

=SUM(A1:A3,A5:A9)

In the Database module:

Enter N/A as text in the field instead of using the NA function.

NOTE: This will not work with the function ISNA(x). This function requires the use of the NA function.

