Forum Discussion
Sensei1948
May 21, 2022Copper Contributor
Quiz Score Sheet
I’m a novice, I have created a excel worksheet to use at our church quiz night.
I have 10 teams in first column, then 10 columns to put results in as the quiz rounds are played. I then have a total common so as each result is added it adds them up. How can I then have this column ranked from 1st to last, or either have another total column, that shows the ranking 1-10
- PeterBartholomew1Silver Contributor
Warning: this is simply playing with 365 and represents total overkill! What I did with Lorenzo data was to total, rank and sort by rank in a single formula.
This proved to be surprisingly difficult because the obvious ranking formulas RANK.EQ and COUNTIFS require range references and not arrays. My final formula was
= LET( total, BYROW(score, Sumλ), rank, MAP(total, Rankλ(total)), SORTBY(HSTACK(Team,total), rank) )
where the two lambda functions were defined in Name Manager to be
Rankλ = LAMBDA(arr, LAMBDA(x,1+SUM(SIGN(arr>x)))) Sumλ = LAMBDA(x, SUM(x));
The double LAMBDA is used to pass the total array and the specific instance to be ranked as separate parameters, with MAP generating individual values to be ranked.
Oops. I missed a trick there! I do not need to rank the teams. The sort will do that for me.
= LET( total, BYROW(score, Sumλ), SORTBY(HSTACK(Team,total), total, -1) )
In fact, SORTBY is an effective way of determining the ranking!
- LorenzoSilver Contributor
A couple of other 365/Dynamic array alternatives
in N3:
=XMATCH(Totals, SORT(Totals,,-1))
in O3:
=XMATCH(Totals, SORT(UNIQUE(Totals),,-1))
- giganticCopper Contributor
Hello! I stumbled across your sheet and think it would work well for a neighborhood trivia night I'm setting up. I can't figure out how to adjust the rank area so I can add more teams. I'm also a bit puzzled by the "dense rank" column. What was the intended purpose? Thanks in advance!
Michael
- Arnaud1010Copper ContributorJust use the RANK.EQ or RANK.AVG function in another column.
=RANK.AVG(L1,$L$1:$L$10,0)- Sensei1948Copper Contributor
- LorenzoSilver Contributor