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
Search JabSto ::




Custom Search