Microsoft Office Tutorials and References

In Depth Information

**Chapter 17: Functions for analyzing statistics**

the Formulas tab on the ribbon and then click Statistical to display a menu of statistical

functions.

Note

Excel also offers the advanced statistical functions LINEST, LOGEST, TREND, and

GROWTH, which operate on arrays. For more information, see “Understanding linear

and exponential regression” later in this chapter.

The AVERAGE functions

The AVERAGE and AVERAGEA functions compute the arithmetic mean, or average, of the

numbers in a range by summing a series of numeric values and then dividing the result

by the number of values. These functions take the arguments
number1
,
number2
, and so

on, and they can include up to 255 arguments. The AVERAGE function ignores blank cells

and cells containing logical and text values, but the AVERAGEA function includes them. For

example, to calculate the average of the values in cells B4 through B15, you could use the

formula =(B4+B5+B6+B7+B8+B9+B10+B11+B12+B13+B14+B15)/12, but it’s obviously

more efficient to use =AVERAGE(B4:B15).

For more information about this function, see the sidebar “AVERAGE vs. AVG” in Chapter 14,

“Everyday functions.” Also, see “The AGGREGATE function” in Chapter 14, which you can also

use to apply this function with options to ignore hidden rows and error values.

The AVERAGEIF function takes the arguments
range
,
criteria
, and
average_range
, where

range
represents the cells to average;
criteria
is a number, expression, cell reference, or

text used to select the cells within
range
to include; and
average_range
is an optional

argument specifying an alternate range of cells to evaluate. For example, the formula

=AVERAGEIF(sales,">20",A2:C30) averages all cells in the range A2:C30 with values greater

than 20. If the third argument is omitted, the formula performs the same operation on the

named range
sales
instead. The
average_range
criterion becomes useful when you want to

select cells on the basis of the contents of one row or column and actually perform the

calculation on numbers contained in adjacent rows or columns.

The AVERAGEIFS function is similar to the AVERAGEIF function, but it allows you to

specify multiple criteria. This function takes the arguments
average_range
,
criteria_range1
,

criteria1
,
criteria_range2
,
criteria2
, and so on, where
average_range
specifies the cell range

you want to average,
criteria_range
specifies the cells containing the values you want to

compare, and
criteria
is a value, expression, cell reference, or text defining the cells within

average_range
that you want to include. You can add up to 127 sets of
criteria_range
and

criteria
arguments. This function is useful for finding the average sale prices of particular

automobile models on an online auction site, where you limit the results to include specific