# 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

9 Replies

# Re: Quiz Score Sheet

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

=RANK.AVG(L1,\$L\$1:\$L\$10,0)

# Re: Quiz Score Sheet

How do I do that # Re: Quiz Score Sheet in N3 and copy down:

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

# Re: Quiz Score Sheet

Warning: this is simply playing with 365 and represents total overkill!  What I did with @L z.  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!

# Re: Quiz Score Sheet

A couple of other 365/Dynamic array alternatives in N3:

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

in O3:

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

# Re: Quiz Score Sheet

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

# Re: Quiz Score Sheet

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

# Re: Quiz Score Sheet

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?

# Re: Quiz Score Sheet

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