Picking 5 highest/lowest numbers from a list

Thanks guys,

I think the large function is working,I just need to figure out how to use the Row function

@NVBC: I tried these but they didn't work for me.

@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?

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.

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!

ohh that's even better. Thanks

Hmm, I just found a problem. Let's say I'm trying to find the 5 lowest relative numbers in the list, but the fifth and sixth numbers are the same. In this case, I would only like it to pick out the lowest 4, or to get even more complicated, I would like to find the lowest scores, up to 5, without including matching scores if the matching scores push the total over 5. for example, if the lowest score is 1, and the next 5 scores are 2, then I would only like it to show me the 1. Does that make sense?

I have attached a sheet with this problem. So in the RDA column, I want it to disregard the 3 and 23, as they push the total over 5.

Thanks in advance for the help!

That would be fine. but, if there is a sixth number that is 5.75, becaust that pushes the total over 5, then I would just like to see:

5.38
5.6
5.6
5.6

So duplicates aren't bad, but they are if they make the total exceed 5. So, as unlikely as it might be, if there were 6 lowest scores all tied, then I wouldn't use any of them.

Wow, that's a monster, but it sure works!

Thank you so much!

Well, I found another loophole. In this new data set, with the new formula, it returns 1.33 (# 15) as the only RDA, when it should also grab the four # 4's, as that would still make a total of 5 RDA's. Make sense?

So if there was one more score of 4 in the scores, then I wouldn't use any of them, but since there are only four, plus the lower score, they can be used.

Yup, works. Well, I sure hope we have our bases covered now. Now for the 5 highest scores, I guess I can just go through this formula and change the smalls to large and direction signs and such...

Thanks again, I never could have figured this out without you.

Dangit, now I have to change the absolute columns to reflect these changes. I guess they need to show absolute scores (values >=7.5, and <=5), to a maximum of 5.

Man, I'm really struggling with this. I'm trying to get it to return the top five scores over 7.5, up to a maximum of 5. My original formula is here, but it returns everything over 7.5, without limiting the amount of returns.I'm trying to have the same rules apply that we did for the "relative" columns, that if there are similar scores that push the total over 5, then to disregard them.

Any Ideas?

These don't seem to be working for me. I wrote these formulas last night , and they seem to be working:

For absolute strength:
=IF(OR(AND(B2>=LARGE($B$2:$B$25,5),B2>7.45),AND(B2=LARGE($B$2:$B$25,5),COUNTIF($B$2:$B$25,AND(LARGE($B$2:$B$25,5),B2>7.45))>1,OR(COUNTIF($B$2:$B$25,AND(LARGE($B$2:$B$25,5),B2>7.45))<4,COUNTIF($B$2:$B$25,AND(LARGE($B$2:$B$25,5),B2>7.45))=5),B2>7.45)),A2,".")

And for the weaknesses:
=IF(OR(AND(B2<=SMALL($B$2:$B$25,5),B2<5.04),AND(B2=SMALL($B$2:$B$25,5),COUNTIF($B$2:$B$25,AND(SMALL($B$2:$B$25,5),B2<5.04))>1,OR(COUNTIF($B$2:$B$25,AND(SMALL($B$2:$B$25,5),B2<5.04))<4,COUNTIF($B$2:$B$25,AND(SMALL($B$2:$B$25,5),B2<5.04))=5),B2<5.04)),A2,".")

They seem to cover all the bases. You think so?

Thanks again NBVC. This resource has been indispensable.