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!
A couple of other 365/Dynamic array alternatives
in N3:
=XMATCH(Totals, SORT(Totals,,-1))in O3:
=XMATCH(Totals, SORT(UNIQUE(Totals),,-1))
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