The following are Excel’s common statistical functions:
AVERAGE: Returns the average (mean) of the arguments. The arguments must be num- bers or names, arrays of cells, or cell references that contain numbers. For example,
=AVERAGE(10,2,3)returns 5.
LARGE: Returns the kth largest value in a data set. For example, =LARGE({100,75,120,95}, 2)returns the second largest value (the number 2in the function represents the second largest value) in the given data set, or 100.
MAX: Returns the largest value in a set of values. For example, =MAX(100,75,120,95)
returns 120.
MEDIAN: Returns the median number in the set of given numbers. The median is the number in the middle of a set of numbers; that is, half the numbers have values that are greater than the median, and half have values that are less. For example,
=MEDIAN(20,100,10,80,90)returns 80.
MIN: Returns the smallest value in a set of values. For example, =MIN(100,75,120,95)
returns 75.
MODE: Returns the most frequently occurring, or repetitive, value in an array or range of data. For example, =MODE(45,60,45,70,65,100,65,45,100)returns 45.
PERCENTILE: Returns the kth percentile of values in a range. You can use this function to establish a threshold of acceptance. For example, you can determine all sales figures that fall above or below a particular percentile. For example, =PERCENTILE({20,40,95,60,100}, 0.3)returns 44 (44 is the thirtieth percentile—0.3, or 30%—for the given list of values).
149
PERCENTRANK: Returns the rank of a value in a data set as a percentage of the data set. You can use this function to evaluate the relative standing of a value within a data set, such as the standing of a specific sales figure among all sales figures for a sales region. For example, =PERCENTRANK({20,40,95,60,100}, 40)returns 0.25 (40 is in the twenty-fifth percentile—0.25, or 25%—of the given list of values).
QUARTILE: Returns the quartile of a data set. Quartiles often are used to divide data into groups, such as the top 25% of sales figures for a sales region. For example,
=QUARTILE({20,40,95,60,100}, 3)returns 95 (the third quartile, or seventy-fifth per- centile, of the given list of values—0 for minimum, 1 for twenty-fifth percentile, 2 for fiftieth percentile, 3 for seventy-fifth percentile, and 4 for maximum).
RANK: Returns the rank of a number in a list of numbers. The rank of a number is its size relative to other values in a list. (If you were to sort the list, the rank of the number would be its position in the list.) For example, =RANK(60,Values,1)returns the number 2 (the second number in the list, where Valuesis a named cell group containing the values 100, 60, 10, 95, and 100; and 1means to sort the list in ascending order (specify 0 or omit the last argument to sort the list in descending order).
SMALL: Returns the kth smallest value in a data set. For example, =SMALL({100,75,120,95}, 2) returns the second smallest value (the number 2in the function represents the second smallest value) in the given data set, or 95.
STDEV: Estimates standard deviation based on a sample. For example, =STDEV(20,40,95,60,100)
returns around 34.6 (dispersed from the average value of 63). STDEVassumes that the list is not the entire list of values. If this list is indeed the entire list of values and not just a por- tion, use STDEVPinstead.
■Note
The standard deviation is another measure of how widely values are dispersed from the average value (the mean). Standard deviation is the square root of the variance (described in the next note). For example, given the three sets {0,0,21,21}, {0,7,14,21}, and {9,10,11,12}, each has an average of 10.5. Their standard deviations are 10.5, about 7.8, and about 1.1, respectively. The third set has a much smaller stan- dard deviation than the other two because its values are all close to 10.5. Most business data analysts use standard deviation instead of variance because standard deviation results are simpler to understand and interpret than variance.STDEVP: Similar to STDEV, calculates standard deviation, but based on the entire popula- tion given as arguments. The standard deviation is a measure of how widely values are dispersed from the average value (the mean). For example, =STDEVP(20,40,95,60,100)
returns around 30.9 (dispersed from the average value of 63). STDEVPassumes that the list is the entire list of values. If this list is not the entire list of values but just a portion, use
STDEVinstead.
VAR: Estimates variance based on a sample. For example, =VAR(20,40,95,60,100)returns 1,195. VARassumes that the list is not the entire list of values. If this list is indeed the entire list of values and not just a portion, use VARPinstead.
■Note
The variance is one measure of how widely values are dispersed from the average value (the mean). Variance is the square of the standard deviation (described in the previous note). For example, given the three sets {0,0,21,21}, {0,7,14,21}, and {9,10,11,12}, each has an average of 10.5. Their variances are 110.25, 61.25, and 1.25, respectively. The third set has a much smaller variance than the other two because its values are all close to 10.5.VARP: Similar to VARP, estimates variance, but based on the entire population given as arguments. For example, =VARP(20,40,95,60,100)returns 956. VARPassumes that the list is the entire list of values. If this list is not the entire list of values but just a portion, use
VARinstead.