Forum Discussion
Help creating a conditional leaderboard
I need a formula (I think it should be SMALL(IF)) to create a leaderboard of times from column D, based on the number of players in column B.
In other words, a leaderboard for just the groups of 2, and then separately just for groups of 3 etc.
I've tried =SMALL(IF(B3:B="2", D3:D), 1) but that doesn't work. I've also tried =SMALL(IF(B3:B=H2, D3:D), 1) where H2 =2, that works, but ignores the condition. Gives me the best overall time, instead of the best time for groups of 2.
Any help would be appreciated
3 Replies
These upper links describes the formula syntax and usage of the RANK function in Microsoft Excel.
Example link:
How to rank based on two columns in Excel?
Supposing there are three columns, one with names, the other two with the first scores and second scores, now you want to rank the names based on the scores in two columns, how can you deal with this problem in Excel? Formula and informations you will find in the overlying link.
- I don't think this works in my case. I'm building a database with new data coming in every day (hence B3:B, as there's new info in B regularly). I currently have a database of 60 results. I don't want to find the rank of an individual result, I want excel to find the top 10 results, of a specific group size.
From what I read about rank, I don't think that achieves it.
Does this make sense?- I have found a solution. Not that I completely understand it but it works.
=ARRAY_CONSTRAIN(ARRAYFORMULA(SMALL(IF($B$3:$B=$H44,$D$3:$D),1)), 1, 1)
B3:B is the group size
H44 = "2"
D3:D is the times
And the first of the three 1's at the end is for the first (smallest times). Change that to 2 for the second smallest time, etc.
