You are currently offline, waiting for your internet to reconnect

How to use A functions in Excel

Support for Office 2003 has ended

Microsoft ended support for Office 2003 on April 8, 2014. This change has affected your software updates and security options. Learn what this means for you and how to stay protected.

This article was previously published under Q205548
For a Microsoft Excel 97 version of this article, see 156445.
SUMMARY
Microsoft Excel includes a set of functions that are designed to be 100 percent compatible with a similar set of functions in Lotus 1-2-3. This article describes the functions, which are called A functions (because the function names all end in A).
MORE INFORMATION
The A functions in Microsoft Excel are:
   AVERAGEA   COUNTA   MINA   MAXA   STDEVA   STDEVPA   VARA   VARPA				
In general, the A functions treat text and logical values differently than the equivalent non-A functions in Microsoft Excel. The breakdown is as follows:
   Value type            Non-A functions       A functions   ------------------------------------------------------------------   Numbers               Treated normally      Treated normally   Text                  Text is ignored       Text has a value of 0   TRUE Logical Value    TRUE is ignored       TRUE has a value of 1   FALSE Logical Value   FALSE is ignored      FALSE has a value of 0				
Each A function is described in detail in the following sections.

AVERAGEA and AVERAGE

The AVERAGEA function returns the average of its arguments, including numbers, text, and logical values. This differs from the AVERAGE function, which only returns the average of arguments that are numbers. Text and logical values are ignored by the AVERAGE function.

Example:

If you type the following example data:
   A1: 1   A2: 2   A3: 6   A4: TRUE   A5: hello				
the formula =AVERAGEA(A1:A5) returns the value 2, because TRUE has a value of 1, the text "hello" has a value of 0 (zero), the sum of 1, 2, 6, 1, and 0 is 10. Ten divided by five is two.

The formula =AVERAGE(A1:A5) returns the value 3, which is the average of the numbers in the range.

COUNT and COUNTA

The COUNTA function returns the number of cells or items in a list of arguments that contain any value at all, including numbers, text, and logical values. This differs from the COUNT function, which only returns the number of cells or arguments that contain numbers. Text and logical values are not counted by the COUNT function.

Using the example data in the AVERAGEA and AVERAGE section, the formula =COUNTA(A1:A5) returns the value 5, since all five cells contain values. The formula =COUNT(A1:A5) returns the value 3, because only three cells contain numerical values.

MINA and MAXA, and MIN and MAX

The MINA and MAXA functions return the minimum and maximum values in a list of arguments, including numbers, text, and logical values. The standard MIN and MAX functions, return the minimum and maximum values in a list of arguments, but they only consider numeric values. Text and logical values are ignored by the MIN and MAX functions.

Using the example data in the AVERAGEA and AVERAGE section, consider the following formulas:
   Formula       Return value   Reason   --------------------------------------------------------------------   =MINA(A1:A5)  0              The text "hello" has a value of 0.   =MIN(A1:A5)   1              The smallest numeric value in the range                                is 1.   =MINA(A1:A3)  1              The range includes only numeric values,                                and the smallest value is 1.   =MAXA(A1:A5)  6              The largest value in the range is 6.   =MAX(A1:A5)   6              The largest value in the range is 6.				

STDEVA and STDEV

The STDEVA function returns an estimate of the standard deviation of a sample. If the sample includes text or logical values, these are included in the standard deviation calculation. The STDEV function also returns the standard deviation of a sample, but only numeric values within the sample are considered.

Using the sample data in the AVERAGEA and AVERAGE section, the formula =STDEVA(A1:A5) returns the value 2.3452; the formula =STDEV(A1:A5) returns the value 2.6458. The difference between the results is due to the fact that the STDEVA function considers all values in the range when creating its sample, not just the numeric values.

STDEVPA and STDEVP

The STDEVPA function returns the standard deviation of the entire population of a range, including text and logical values. The STDEVP function returns the standard deviation of all of the numeric values in the range.

Using the sample data in the AVERAGEA and AVERAGE section, the formula =STDEVPA(A1:A5) returns the value 2.0976, and the formula =STDEVP(A1:A5) returns the value 2.1602.

VARA and VAR

The VARA function returns an estimate of the variance of a sample, including text and logical values. The VAR function returns an estimate of a sample using only numeric values contained in the sample.

Using the sample data in the AVERAGEA and AVERAGE section, the formula =VARA(A1:A5) returns the value 5.5, and the formula =VAR(A1:A5) returns the value 7.

VARPA and VARP

The VARPA function returns the variance of a range of values, including text and logical values. The VARP function also returns the variance of a range of values, but only numeric values are used in the calculation.

Using the sample data in the AVERAGEA and AVERAGE section, the formula =VARPA(A1:A5) returns the value 4.4, and the formula =VARP(A1:A5) returns the value 4.6667.
9.0 XL20 1-2-3 purecount tfe transition evaluation XL2000 XL2002 XL2003 xl2007
Properties

Article ID: 205548 - Last Review: 01/23/2007 22:18:03 - Revision: 5.1

Microsoft Office Excel 2007, Microsoft Office Excel 2003, Microsoft Excel 2002 Standard Edition, Microsoft Excel 2000 Standard Edition

  • kbinfo kbfunctions kbfaq kbhowto KB205548
Feedback
var varAutoFirePV = 1; var varClickTracking = 1; var varCustomerTracking = 1; var Route = "76500"; var Ctrl = ""; document.write("