Q. I need to calculate the sum of the three largest values in a range of 100 cells. The range isn’t sorted, so I can’t use a SUM function. Do you have any suggestions about how I could handle this problem?
Excel’s LARGE function returns the nth-largest value in a range, in which n is the function’s second argument. You need a formula that calls the LARGE function three times and then sums the results. The following formula, which assumes the numbers are located in the range A1:A100, will do the job:
=LARGE(A1:A100,1)+ LARGE(A1:A100,2)+ LARGE(A1:A100,3)
Another approach is to use an array formula like this one:
=SUM(LARGE(A1:A100,{1,2,3}))
The formula first passes an array of three values to the LARGE function, and then uses the SUM function to add the values returned by the LARGE function. Notice that the values 1 through 3 are enclosed in brackets rather than parentheses. After typing an array formula, press Ctrl-Shift-Enter instead of Enter.
Formulas of this type can become unwieldy as n gets larger. For example, to sum the top 30 values in a range, a formula must contain a list of integers from 1 to 30. Here is a more general version of the array formula:
=SUM(LARGE(A1:A100,ROW(INDIRECT ("1:30"))))
This formula uses the ROW function to generate a series of integers between 1 and 30, and uses this array as the second argument for the LARGE function. To sum a different quantity of numbers, just change the 30 to the desired number.
Post a Comment