Finding top [5] values in list

[AndyE]

Hi - I have a list of values which changes from day to day, but I want Excel
to be able to perform calculations based on the top 5 (and bottom 5) values
in the list. Is there any set of functions that will let me do this?

Thanks!

 

[JE McGimpsey]

One way:

Use the LARGE() function, e.g.,:

=SUM(LARGE(A:A,{1,2,3,4,5})

 

[JE McGimpsey]

Oops, hit Send too fast:


And for the bottom 5, use SMALL()

 

[AndyE]

Thanks - this looks great (and simple for once!). Can I use the OFFSET
function to return a value in column B if the search is applied to column A?
E.g. column B is names, column A is weights, and I want to show the weight of
the second and third heaviest people?

 

[JE McGimpsey]

If they are all of different weights, you can use:

C1: =VLOOKUP(LARGE(A:A,1),A:B,2,FALSE)
C2: =VLOOKUP(LARGE(A:A,2),A:B,2,FALSE)
C3: =VLOOKUP(LARGE(A:A,3),A:B,2,FALSE)