Works: Statistical Calculations Performed on NA() Function Return N/A (126030)


The information in this article applies to:

  • Microsoft Works 2.0
  • Microsoft Works 3.0
  • Microsoft Works 3.0a
  • Microsoft Works 3.0b
  • Microsoft Works 4.0
  • Microsoft Works 4.0a
  • Microsoft Works 4.5
  • 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.

REFERENCES

For more information about how to perform this task in Works 4.0, 4.0a, 4.5, or 4.5a, see your Works printed documentation or online Help.

"Microsoft Works User's Guide," version 3.0, pages 543 and 548.

"Microsoft Works User's Guide," version 2.0, pages 383 and 386.
Modification Type:MajorLast Reviewed:11/24/2003
Keywords:kbprb KB126030
©2003 Microsoft Corporation. All rights reserved.