| The LARGE function of
Excel returns the nth-largest value in a given range, in
which n is the function's second argument. Let us assume
we wish to sum up the Top 5 values in a given range of 50 cells. We
need a formula that calls the LARGE function five times and then sums
up the results. The following formula, which assumes the numbers are
located in the range consisting of 50 cells and have range name TheRange,
will do the job:
=LARGE(TheRange,1)+
LARGE(TheRange,2)+ LARGE(TheRange,3)+ LARGE(TheRange,4)+ LARGE(TheRange,5)
If U are familiar with using array
formula then here is another solution:
=SUM(LARGE(TheRange,{1,2,3,4,5}))
The formula first passes an array of
five values to the LARGE function, and then uses the SUM function to add
the values returned by the LARGE function. Please note that the values 1
through 5 are enclosed in brackets rather than parentheses. Since
it is an array formula be sure after typing press Ctrl+Shift+Enter
instead of Enter.
As n gets larger
the formula gets much more bloated . For example, to sum the top 20
values in a range, a formula must contain a list of integers from 1 to 20.
A more general version of the array formula:
=SUM(LARGE(TheRange,ROW(INDIRECT
("1:20"))))
Important: The formula
given above is an Array Formula. Pressing Enter
instead of Ctrl+Shift+Enter will provide a wrong
output. This formula uses the ROW function to generate a series of
integers between 1 and 20, and uses this array as the second argument for
the LARGE function. To sum a different quantity of numbers, just change
the 20 to the desired number.
|