Selecting the Top 5

[FlashGordy]

Good Afternoon

I'm new here, but have been using Excel for a while. I've never, however used macros or written code in it before.

What I'm trying to do is build a spreadsheet that has several sheets.

One of these is an Inventory Report, where food figures are entered, and the cost of overusing and underusing each item is calculated.

What I'd like is for my front page (essentially a summary) to display the top 5 over used items, and the top 5 under used items, with the values as well. I would like this to automatically update when I enter new information into my "Inventory Report".

I think this should be possible, I just don't know how.


Thanks in advance
Gordon

 

[Gerald Higgins]

Hi, welcome to the board.

Take a look at the =LARGE() and =SMALL() functions for identifying the largest and smallest values in a range. They can be used to find, for example, the nth largest value.

 

[number kruncher]

Use the LARGE and SMALL function to harvest the Top/Bottom five from your lists.

=LARGE(array,n)

n being 1 to 5, for top 5.

And likewise, =SMALL(array,n)

Beware of ZERO's, uding SMALL. Use a seperate =COUNTIF(array,0), then add to n, to adjust.

Then use a lookup, if more info required from lists using the results from above.

 

[FlashGordy]

Hi Gerald

That does look like it would work. How would I go about having the Inventory Item's name (from a different column) returned as well without a marathon of IF() functions?

Gordon

 

[Gerald Higgins]

Let's say your source table looks like this

Item name (col A)....Value (col B)
Item 1...................10
Item 2.....................9
Item 3...................12

and let's say you want to return the second highest value (item 1).

=large(b2:b4,2)

will return the value 10.

Then this formula will return the name of the item that has a value of 10.

=INDEX(A2:A4,MATCH(LARGE(B2:B4,2),B2:B4,0))

Watch out for multiple items having the same value - this may give you unexpected results.

 

[FlashGordy]

Thanks very much guys.

Worked perfectly!

Gordon