LinkBack URL
About LinkBacks
johnexceljohn 
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
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
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
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.
Try:
copied down.
=IF(OR(B2>SMALL($B$2:$B$25,5),AND(B2=SMALL($B$2:$B$25,5),COUNTIF($B$2:$B$25,SMALL($B$2:$B$25,5))>1)),".",A2)
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.
Maybe then?
=IF(OR(B2>SMALL($B$2:$B$25,5),AND(B2=SMALL($B$2:$B$25,5),COUNTIF($B$2:$B$25,SMALL($B$2:$B$25,5))>1,OR(COUNTIF($B$2:$B$25,SMALL($B$2:$B$25,5))<4,COUNTIF($B$2:$B$25,SMALL($B$2:$B$25,5))=5))),".",A2)
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.
Here is what I put in for the relative strengths. Does it look sound?
=IF(OR(B2<LARGE($B$2:$B$25,5),AND(B2=LARGE($B$2:$B$25,5),COUNTIF($B$2:$B$25,LARGE($B$2:$B$25,5))>1,OR(COUNTIF($B$2:$B$25,LARGE($B$2:$B$25,5))<4,COUNTIF($B$2:$B$25,LARGE($B$2:$B$25,5))=5))),".",A2)
It works, but I'm not 100% comfortable manipulating such a big formula.
Last edited by johnexceljohn; 07-21-2009 at 02:05 PM.
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?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks