Ranking a set of scores

[Skiffie]

Hi there, I was searching for basically the question raised in this thread: Ranking a medal tally

While I'm not looking at medals the premise is the same. Over a sailing season, we are looking for the boat that has the "most fastest times". If one boat has a clear number more than any other, it is the winner. If there are more than one, then you move to the ranking the second places ad so on until the tie is broken.

In the example, Avro, Truant III and Uncle Fred all have 3 x 1sts, so we move to the 2nd places. Avro and Uncle Fred have 2 x 2nds each, so need to be split by the third places. Avro has 3 x 3rds to Uncle Fred's 2, so Avro would be ranked first and Uncle Fred second, Truant III third. Merle would be 4th overall to Truant II in 5th due to the split happening at the 4th places. Then this leaves Wrecker in 6th.

H10s Race Results 2022-23.xlsx
	A	B	C	D	E	F	G	H	I	J	K
56	Most Fastest Times
57	Skiffs	1sts	2nds	3rds	4ths	5ths	6ths	DNF	DNS	DNC	Rank
58	Avro	3	2	3	1	1	0	2	0	0
59	Merle	1	4	2	3	1	1	0	0	0
60	Truant II	1	4	2	0	0	0	0	0	5
61	Truant III	3	0	0	2	2	0	5	0	0
62	Uncle Fred	3	2	2	5	0	0	0	0	0
63	Wrecker	1	0	1	1	2	3	1	0	3
Summary

It's one that's been a puzzle to me for some years!!

Thanks in advance for your consideration!

 

[Fluff]

One option

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K
56	Most Fastest Times
57	Skiffs	1sts	2nds	3rds	4ths	5ths	6ths	DNF	DNS	DNC	Rank
58	Avro	3	2	3	1	1	0	2	0	0	1
59	Merle	1	4	2	3	1	1	0	0	0	4
60	Truant II	1	4	2	0	0	0	0	0	5	5
61	Truant III	3	0	0	2	2	0	5	0	0	3
62	Uncle Fred	3	2	2	5	0	0	0	0	0	2
63	Wrecker	1	0	1	1	2	3	1	0	3	6
Main

Cell Formulas
Range	Formula
K58:K63	K58	=SUMPRODUCT(--($B$58:$B$63*10000000000+$C$58:$C$63*100000000+$D$58:$D$63*1000000+$E$58:$E$63*10000+$F$58:$F$63*100+$G$58:$G$63>=B58*10000000000+C58*100000000+D58*1000000+E58*10000+F58*100+G58))

 

[Legacy 143009]

I am sure someone will show up with a solution without a helper column needed. My solution requires using colum L as helper column.

L58 = SUMPRODUCT(($B58:$E58)/COLUMN($B58:$E58))
K58 =RANK(L58,$L$58:$L$100)

Sorry for inconvience. After checking @Fluff 's formula, I've realized that my formula calculates totally something different.

 

[Fluff]

@Flashbond if you change the helper to

=SUMPRODUCT(($B58:$G58)/(COLUMN($B58:$G58)^10))

it gives the same result.

 

[Fluff]

Based on Flashbonds approach that doesn't need a helper column

=SUMPRODUCT(--(MMULT(($B$58:$G$63)/(COLUMN($B$58:$G$58)^10),SEQUENCE(6,,,0))>=SUM(($B58:$G58)/(COLUMN($B$58:$G$58)^10))))

 

[Fluff]

The formula in post#5 may not be accurate is you have a lot of results, this should be better

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
56	Most Fastest Times
57	Skiffs	1sts	2nds	3rds	4ths	5ths	6ths	DNF	DNS	DNC	Rank
58	Avro	3	2	3	1	1	0	2	0	0	1	1	1
59	Merle	1	4	2	3	1	1	0	0	0	4	4	5
60	Truant II	1	4	2	0	0	0	0	0	5	5	5	6
61	Truant III	3	0	0	2	2	0	5	0	0	3	3	3
62	Uncle Fred	3	2	2	5	0	0	0	0	0	2	2	2
63	Wrecker	1	0	75	1	2	3	1	0	3	6	6	4
Main

Cell Formulas
Range	Formula
L58:L63	L58	=LET(r,B58:G63,c,COLUMNS(r),m,MMULT(r*10^SEQUENCE(,c,(c-1)*2,-2),SEQUENCE(c,,,0)),BYROW(m,LAMBDA(br,SUM(--(br<=m)))))
K58:K63	K58	=SUMPRODUCT(--($B$58:$B$63*10000000000+$C$58:$C$63*100000000+$D$58:$D$63*1000000+$E$58:$E$63*10000+$F$58:$F$63*100+$G$58:$G$63>=B58*10000000000+C58*100000000+D58*1000000+E58*10000+F58*100+G58))
M58:M63	M58	=SUMPRODUCT(--(MMULT(($B$58:$G$63)/(COLUMN($B$58:$G$58)^10),SEQUENCE(6,,,0))>=SUM(($B58:$G58)/(COLUMN($B$58:$G$58)^10))))
Dynamic array formulas.

 

[Legacy 143009]

@Flashbond if you change the helper to

=SUMPRODUCT(($B58:$G58)/(COLUMN($B58:$G58)^10))

it gives the same result.

Ahh my bad math

 

[Skiffie]

The formula in post#5 may not be accurate is you have a lot of results, this should be better

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
56	Most Fastest Times
57	Skiffs	1sts	2nds	3rds	4ths	5ths	6ths	DNF	DNS	DNC	Rank
58	Avro	3	2	3	1	1	0	2	0	0	1	1	1
59	Merle	1	4	2	3	1	1	0	0	0	4	4	5
60	Truant II	1	4	2	0	0	0	0	0	5	5	5	6
61	Truant III	3	0	0	2	2	0	5	0	0	3	3	3
62	Uncle Fred	3	2	2	5	0	0	0	0	0	2	2	2
63	Wrecker	1	0	75	1	2	3	1	0	3	6	6	4
Main

Cell Formulas
Range	Formula
L58:L63	L58	=LET(r,B58:G63,c,COLUMNS(r),m,MMULT(r*10^SEQUENCE(,c,(c-1)*2,-2),SEQUENCE(c,,,0)),BYROW(m,LAMBDA(br,SUM(--(br<=m)))))
K58:K63	K58	=SUMPRODUCT(--($B$58:$B$63*10000000000+$C$58:$C$63*100000000+$D$58:$D$63*1000000+$E$58:$E$63*10000+$F$58:$F$63*100+$G$58:$G$63>=B58*10000000000+C58*100000000+D58*1000000+E58*10000+F58*100+G58))
M58:M63	M58	=SUMPRODUCT(--(MMULT(($B$58:$G$63)/(COLUMN($B$58:$G$58)^10),SEQUENCE(6,,,0))>=SUM(($B58:$G58)/(COLUMN($B$58:$G$58)^10))))
Dynamic array formulas.

Thanks @Fluff and @Flashbond for your replies.

Fluff - just noticed the column "M" formula gives a different result for the last 3 ranked boats though?

 

[Fluff]

That's right, it was to demonstrate that that formula could give the wrong result in certain situations.

 

[Skiffie]

Ahh got it.

I think I'm running with the "L58" formula, I like that it's not relying upon a multiplier like the others. Many thanks for this!