Picking 5 highest/lowest numbers from a list

**johnexceljohn** · 07-17-2009, 01:38 PM

@NBVC

OK. this might take me a while to digest, my eyes are starting to go crosseyed with all the formulas. Will I have to somehow remove the scores that are higher or lower than the parameters that you set?

**NBVC** · 07-17-2009, 01:41 PM

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.

**johnexceljohn** · 07-17-2009, 01:46 PM

ahh, I get it. What I'm looking for though is for it to include the absolute strengths in with the relative. for example, on the example sheet, there are exactly 5 absolute strengths, so the top 5 relative strengths should be the same ones. As for the relative weaknesses, there are 11 absolute, but it should pick out the lowest 5.

**NBVC** · 07-17-2009, 02:02 PM

So since you ranked them, you can use that column...

In H2: =IF(C2<=5,A2,".")

and in I2: =IF(C2>=LARGE($C$2:$C$25,5),A2,".")

copied down.

**johnexceljohn** · 07-17-2009, 02:15 PM

YES!

you did it. Thank you so much for the help, and not making me feel like a complete idiot. I knew I ranked them for a reason. Thank you so so much!