Search Min/Max 3 numbers in a column

**opsman** · 10-30-2010, 08:15 PM

I'm wondering if using the Min/Max formula we can find the highest 3 numbers or lowest 3 numbers (or other alternatives for that matter) in a column rather than just the minimum and maximum on their own?

Anybody know an easy way without simply resorting to a sort or writing a massively long formula? If I have to do an individual formula per cell that would be acceptible.

It's one of those late night ideas so I don't have an example to post, but simply envisage a long column of positive numbers with the top number being less than a thousand. The column might well be in excess of 5,000 rows long though. I'm after the highest 3 and lowest 3 in the column.

**peterjuhnke** · 10-30-2010, 08:49 PM

hello opsman
Use the large, and small functions. Eg to find the 2nd smallest number, =small(A1:A50,2)
the third largest =large((A1:A50,3)
Please have a look and let me know how you go. if still having probs post a dummy sheet.
Regards
Peter

**opsman** · 10-31-2010, 11:08 AM

Pete

Many thanks and how bizarre I never came across that one before!

I think it may need a little tweaking though to work for what I have in mind as I was after the second smallest/largest unique number, yet when I tried this on a column with second largest, as the largest and second largest were the same it gave the same outcome. I think I may well get around it by building a simple if statement referencing the largest answer.

I'll leave the post open for a while just in case, but I think I can get the small/large scenario to do what is required.

Many thanks

**DonkeyOte** · 10-31-2010, 11:54 AM

If you want uniques then use COUNTIF (based prior result) to generate k

Assume values in A1:A50 as before with 3 LARGE and 3 SMALL to go in B1:B3 and C1:C3 respectively

Please Login or Register  to view this content.

both assume a min of 3 unique numbers in existence of course

**martindwilson** · 10-31-2010, 11:57 AM

this array formula(confirmed with ctrl +shift+enter will give largest (drag down three rows) note 2 things you cant have blanks at the end or in the middle
and the second bit $A$1:$A$12 is correct it need to be 1 more than $A$1:$A$11
=LARGE(((IF(FREQUENCY($A$1:$A$11, $A$1:$A$11)>=1,1,0))*($A$1:$A$12)),ROW(A1))
similar for small
but
=SMALL(((IF(FREQUENCY($A$1:$A$11, $A$1:$A$11)>=1,1,10^99))*($A$1:$A$12)),ROW(A2))
this starts from second smallest as smallest will always be 0

**opsman** · 11-02-2010, 08:31 PM

Thanks Guys, I just came back to close the post and now I have new solutions!

Peter - Thanks for your help.
Donkey - Thats the way I was going!
Martin - I "think" your formula returns only unique numbers and therefore skips repeated numbers altogether?, I was looking at the top 3 and bottom 3 numbers in a string of numbers but where numbers were duplicated illustrating them once only.

Donkey OTE this was one of my crazy late night ideas. Mathematically speaking and having slept on it, I think I may potentially end up misleading going down this route so decided against the original idea and went with the small/large that Peter suggested for the time being. I think my original thoughts might work if applied differently by listing the unique numbers and alongside a count of those numbers if that makes sense? It would portray the data visually as I wanted but with supporting data alongside. I think it's one for the weekend for me!

Thanks to you all (as usual). I think the "add-on" mentioned will be fairly simple but I have certainly learnt alot from what I thought was a relatively simple idea.

Thanks All.