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.