Picking 5 highest/lowest numbers from a list

Hello everyone,

I just found this forum, and it looks like a great resource.

I am trying to generate a list of the 5 highest and 5 lowest numbers from a list of scores that range from 1 to 10. I have found the highest and lowest absolute values (numbers over 7.5, and less than 5, out of 10), but I would also like to generate the 5 highest and lowest relative numbers, ie. if there are no scores over 7.5, then the 5 next highest numbers. I have used if/then formulas for the absolute highest and lowest numbers, and a rank/countif formula to rank them. I have no idea how to generate a list of the "relative strengths and weaknesses".

Any help would be greatly appreciated. I have attached the file.

Thanks,
John

are you looking for the
=large
and
=small
functions?

You can designate a range, and the k highest or lowest value found in that range. IE 3rd highest number from a1:a100.

Note:
If you're doing this for multiple ascending values, use somethin like row() for your k value in the first row if it's in row 1, or row()-3 if it's in row 4 etc. This will let you drag the formula down, and the row() function will update per row to give you 1, 2, 3, 4, 5 etc. as it goes.

Not sure if these are what you are looking for

In H2:

=IF(AND(MAX($B$2:$B$25)>5.04,MAX($B$2:$B$25)<7.45),IF(B2>=LARGE($B$2:$B$25,5),A2,"."),"")

copied down

and in I2:

=IF(MIN($B$2:$B$25)>5.04,IF(B2<=SMALL($B$2:$B$25,5),A2,"."),"")

copied down

these will fill in only if all numbers in B2:B25 are out of the ranges.

Attached is how mine is meant to work...

If all numbers in column B are less than 7.45 and greater than 5.04, then column H fills in...

If all numbers in column B are greater than 5.04, then column I fills in....

Base on your original post:

I would also like to generate the 5 highest and lowest relative numbers, ie. if there are no scores over 7.5, then the 5 next highest numbers.

These columns only populate if all values in column B are outside the parameters that would populate column F & G

I replaced some of your values in column B for testing.. all of the numbers in column B of my sheet are outside your 5.04 to 7.45 parameters.

You are welcome.

You can also avoid column C, i guess and use these

=IF(B2>=LARGE($B$2:$B$25,5),A2,".")

and

=IF(B2<=SMALL($B$2:$B$25,5),A2,".")

Wasn't sure if you were ranking for other reason too.. that is why I used that column...

Please remember to mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

What if, out of the 5 smallerst, there are 2 or 3 similar scores and then the 5th score is different..

e.g

5.38
5.60
5.60
5.60
5.75