 | Excel 2007 includes one of the most complete sets of statistical functions available outside a dedicated statistics software program. When you want to access these functions from the Ribbon's Formulas tab instead of from the Insert Function dialog box, you need to click the More Functions command button and then highlight the Statistical option at the very top of the drop-down menu (or press Alt+MQS). Doing this displays a continuation menu listing all the Statistical functions in alphabetical order. |
The AVERAGE, MAX (for maximum), and MIN (for minimum) functions are the most commonly used of the statistical functions because they are useful to both the average number cruncher as well as the dedicated statistician. All three functions follow the same syntax as the good old SUM function. For example, the syntax of the AVERAGE function uses the following arguments, just as the SUM, MAX, and MIN functions do:
AVERAGE(number1,[number2],[...])
Just as in the SUM function, the number arguments are between 1 and 30 numeric arguments for which you want the average. And of course, the arguments could be references to other cells that contain numbers, as well as a mix, like these:
=AVERAGE(18, 65, A4)
=MAX(A2:A12)
=MIN(A2:B5, A1, 17)
The AVERAGE function computes the arithmetic mean of the values in the range by summing them and then dividing them by the number of values in the range. The following two formulas produce the same answer:
=AVERAGE(C4:C8)
=SUM(C4:C8)/COUNT(C4:C8)
 | Note that the latter formula uses the SUM function to total the values and then uses another statistical function called COUNT to determine the number of values in the list. The MAX and MIN functions simply compute the highest and lowest values in the cell range used as the number argument. The MEDIAN function computes the value that is in the middle of the range of values; that is, the one where half the values are greater and half are less. |
| One formula that might be useful can reveal the value right in the middle between the highest and the lowest number in a range. You can do this by finding the average of the highest and lowest number, like this: |
=AVERAGE(MIN(C4:C8), MAX(C4:C8))
Note that this is a bit different from the MEDIAN function because it can be "thrown off" by one extremely high or extremely low value, but that might be exactly what you want!
|
