Quiz Score Sheet

Copper Contributor

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

9 Replies
Just use the RANK.EQ or RANK.AVG function in another column.



How do I do that






in N3 and copy down:

=RANK.EQ(M3, M$3:M$10)


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



= LAMBDA(arr, LAMBDA(x,1+SUM(SIGN(arr>x))))

= 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!



A couple of other 365/Dynamic array alternatives




in N3:

=XMATCH(Totals, SORT(Totals,,-1))

in O3:

=XMATCH(Totals, SORT(UNIQUE(Totals),,-1))



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!


Hi @gigantic 

In attached file a Table with 10 teams:


If you need more teams just add them starting row 13, the Table size will auto-adjust


Dense Rank is just another ranking option. If you don't want/like it just delete that column

In Dense Ranking, items that compare equally receive the same ranking number, and the next item(s) receive the immediately following ranking number

This has worked great! With a few months of use there are a couple things that would be cool to add but I can't figure it out without messing up the table's calculations. I'm trying to add a halftime total as well as a final total (pre-final question). Any suggestions?
No time these days (sorry) + quite old thread. Better you create a new one with a realistic sample attached (or shared) + the expected result. Thanks