Chapter 16: Using Formulas for Statistical Analysis
16
To determine the rank of Gianna Ruiz, we pass into RANK the time in C3, the total list of
times in C3:C14, and the order. The order is 1 in this example, because we want the lowest
number to have rank 1. If we want the highest number to be ranked 1, the final argument
would be 0.When there is a tie, RANK will return the same result for the tied values. If two racers had a time of 20:35, RANK
would return 1 for both. The next lowest time would receive a rank of 3. None of the values would rank 2 because the
tied values take up both the 1 and 2 ranking.
Excel 2010 introduced two new functions for handling ties with ranking: RANK.AVG and RANK.EQ. The RANK.
EQ function mirrors the results of RANK from prior versions. That is, the same value returns the same ranking. The
RANK.AVG function works differently. It returns the average ranking for all values that match.
Assume the data in Figure 16.10 showed four racers with a time of 21:38, the second lowest time. RANK.AVG would
return 1 for the best time and 3.5 for four matching second-place times. Those four times occupy the ranks 2, 3, 4,
and 5. The average of those four rankings is 3.5.
Unlike LARGE and SMALL, which return the actual values, RANK returns the position of the
value in the list if the list were sorted according to the last argument. To get the actual
values, we need to use INDEX and MATCH just as we did for the names. The formula in cell
G3 to return the time of the first-place competitor is shown here:
=INDEX($C$3:$C$14,MATCH(ROW(A1),$D$3:$D$14,FALSE),1)See Chapter 14 for more information on the MATCH and INDEX functions.Calculating mean, median, and mode
When someone refers to average, they are usually referring to the arithmetic mean: the
sum of the values divided by the count of the values. There are two other averages, median
and mode, that you can calculate in Excel.Figure 16.11 shows a list of 20 students and their grade on an assignment. We want to
analyze the grades by finding the mean, median, and mode and draw conclusions from the
results.
=AVERAGE(C3:C22)
=MEDIAN(C3:C22)
=MODE(C3:C22)You can see from Figure 16.11 that the mean is 85.1, the median is 90.5, and the mode is
93.0. The mean is computed using the AVERAGE function, which sums all the values in the
range and divides by the number of values. How the median and mode compare to the aver-
age may provide some insights into the data.The median is computed using the aptly named MEDIAN function. If all the grades are
listed in order, MEDIAN returns the value that’s right in the middle. Because we have an