Quiz Score Sheet

%3CLINGO-SUB%20id%3D%22lingo-sub-3409123%22%20slang%3D%22en-US%22%3EQuiz%20Score%20Sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3409123%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSTRONG%3EI%E2%80%99m%20a%20novice%2C%20I%20have%20created%20a%20excel%20worksheet%20to%20use%20at%20our%20church%20quiz%20night.%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3EI%20have%2010%20teams%20in%20first%20column%2C%20then%2010%20columns%20to%20put%20results%20in%20as%20the%20quiz%20rounds%20are%20played.%20I%20then%20have%20a%20total%20common%20so%20as%20each%20result%20is%20added%20it%20adds%20them%20up.%20How%20can%20I%20then%20have%20this%20column%20ranked%20from%201st%20to%20last%2C%20or%20either%20have%20another%20total%20column%2C%20that%20shows%20the%20ranking%201-10%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3409123%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3409141%22%20slang%3D%22en-US%22%3ERe%3A%20Quiz%20Score%20Sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3409141%22%20slang%3D%22en-US%22%3EJust%20use%20the%20RANK.EQ%20or%20RANK.AVG%20function%20in%20another%20column.%3CBR%20%2F%3E%3CBR%20%2F%3E%3DRANK.AVG(L1%2C%24L%241%3A%24L%2410%2C0)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3409329%22%20slang%3D%22en-US%22%3ERe%3A%20Quiz%20Score%20Sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3409329%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1396740%22%20target%3D%22_blank%22%3E%40Arnaud1010%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20do%20I%20do%20that%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%222022-05-21.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F373883i3F686AEF25618B97%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%222022-05-21.png%22%20alt%3D%222022-05-21.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3409365%22%20slang%3D%22en-US%22%3ERe%3A%20Quiz%20Score%20Sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3409365%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1396744%22%20target%3D%22_blank%22%3E%40Sensei1948%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22_Screenshot.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F373885i29B5430075466DAD%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22_Screenshot.png%22%20alt%3D%22_Screenshot.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ein%26nbsp%3B%3CSTRONG%3EN3%3C%2FSTRONG%3E%20and%20copy%20down%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DRANK.EQ(M3%2C%20M%243%3AM%2410)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3410270%22%20slang%3D%22en-US%22%3ERe%3A%20Quiz%20Score%20Sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3410270%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1396744%22%20target%3D%22_blank%22%3E%40Sensei1948%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWarning%3A%20this%20is%20simply%20playing%20with%20365%20and%20represents%20total%20overkill!%26nbsp%3B%20What%20I%20did%20with%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F75890%22%20target%3D%22_blank%22%3E%40L%20z.%3C%2FA%3E%26nbsp%3B%20data%20was%20to%20total%2C%20rank%20and%20sort%20by%20rank%20in%20a%20single%20formula.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20140px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F373919i41C2652A40959505%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThis%20proved%20to%20be%20surprisingly%20difficult%20because%20the%20obvious%20ranking%20formulas%20RANK.EQ%20and%20COUNTIFS%20require%20range%20references%20and%20not%20arrays.%26nbsp%3B%20My%20final%20formula%20was%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%20LET(%0A%20%20%20%20total%2C%20BYROW(score%2C%20Sum%CE%BB)%2C%0A%20%20%20%20rank%2C%20%20MAP(total%2C%20Rank%CE%BB(total))%2C%0A%20%20%20%20SORTBY(HSTACK(Team%2Ctotal)%2C%20rank)%0A%20%20)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewhere%20the%20two%20lambda%20functions%20were%20defined%20in%20Name%20Manager%20to%20be%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3ERank%CE%BB%20%0A%3D%20LAMBDA(arr%2C%20LAMBDA(x%2C1%2BSUM(SIGN(arr%26gt%3Bx))))%0A%0ASum%CE%BB%20%0A%3D%20LAMBDA(x%2C%20SUM(x))%3B%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20double%20LAMBDA%20is%20used%20to%20pass%20the%20total%20array%20and%20the%20specific%20instance%20to%20be%20ranked%20as%20separate%20parameters%2C%20with%20MAP%20generating%20individual%20values%20to%20be%20ranked.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOops.%20I%20missed%20a%20trick%20there!%26nbsp%3B%20I%20do%20not%20need%20to%20rank%20the%20teams.%26nbsp%3B%20The%20sort%20will%20do%20that%20for%20me.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3D%20LET(%0A%20%20%20%20total%2C%20BYROW(score%2C%20Sum%CE%BB)%2C%0A%20%20%20%20SORTBY(HSTACK(Team%2Ctotal)%2C%20total%2C%20-1)%0A%20%20)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20fact%2C%20SORTBY%20is%20an%20effective%20way%20of%20determining%20the%20ranking!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3410810%22%20slang%3D%22en-US%22%3ERe%3A%20Quiz%20Score%20Sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3410810%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1396744%22%20target%3D%22_blank%22%3E%40Sensei1948%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20couple%20of%20other%20365%2FDynamic%20array%20alternatives%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22_Screenshot.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F373940iD698A8649FD4F3BD%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22_Screenshot.png%22%20alt%3D%22_Screenshot.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ein%26nbsp%3B%3CSTRONG%3EN3%3C%2FSTRONG%3E%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DXMATCH(Totals%2C%20SORT(Totals%2C%2C-1))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Ein%26nbsp%3B%3CSTRONG%3EO3%3C%2FSTRONG%3E%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DXMATCH(Totals%2C%20SORT(UNIQUE(Totals)%2C%2C-1))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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

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

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

@Arnaud1010 

How do I do that

2022-05-21.png

@Sensei1948 

 

_Screenshot.png

 

in N3 and copy down:

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

@Sensei1948 

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.

image.png

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!

@Sensei1948 

 

A couple of other 365/Dynamic array alternatives

 

_Screenshot.png

 

in N3:

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

in O3:

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