Golf Tournament Score Card Ranking

[golfer22]

Needing assistance with calculating the ranking for golf scores bases on total score, and then each hole based on the hole handicap.

Here's the current data I'm working with (ignore the highlighted cells):

In column W, the lowest score will be ranked 1, so with row 6 being 59, it get's 1. Since rows 4 and 5 are both 65, the ranking is determined by the handicap of the hardest hole, which is hole 9 (row K), then hole 18 (row U), etc. If the values in row W are equal, then check row 9 and if the value is lower, it should get rank 2, etc.

I am using the rank.eq with countifs function, and because row 5 has a lower value in both K and U, it's adding 1 each time so the rank of row 4 is 4 instead of 3. Wanting to only add row 18 if row 9 is the same (if that makes sense).

Any help is much appreciated.

Here's the current formula I'm using (will obviously need to add all 18 holes):
=RANK.EQ($W4,$W$4:$W$6,1)+COUNTIFS($W$4:$W$6,$W4,$K$4:$K$6,"<"&$K4)+COUNTIFS($W$4:$W$6,$W4,$U$4:$U$6,"<"&$U4)

 

[golfer22]

Here's more data with an expected rank:

 

[Fluff]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1
2	17	3	5	11	9	13	15	7	1		18	4	6	12	10	14	16	8	2
3
4	4	4	3	4	3	4	3	4	5	34	4	4	3	3	3	3	3	3	5	31	65	65050504040303040303030403040303030404	3
5	5	4	2	4	3	5	2	3	4	32	4	3	2	4	6	4	3	3	4	33	65	65040404030202030303060404050402030504	2
6	3	3	3	3	4	4	3	3	3	29	3	4	2	4	3	4	3	4	9	36	65	65030903040302030404030304040403030303	1
7	5	4	3	4	4	5	3	4	3	35	5	4	3	4	4	5	3	4	3	35	70	70030304040303040404040404050503030505	5
8	4	4	3	4	4	5	3	4	3	34	4	4	3	4	4	5	3	4	3	34	68	68030304040303040404040404050503030404	4
Master

Cell Formulas
Range	Formula
L4:L8,V4:V8	V4	=SUM(M4:U4)
W4:W8	W4	=SUM(V4,L4)
X4:X8	X4	=BYROW(C4:W8,LAMBDA(br,CONCAT(CHOOSECOLS(SORTBY(TEXT(br,"00"),C2:W2),-1,SEQUENCE(,18)))))
Y4:Y8	Y4	=BYROW(X4#,LAMBDA(br,SUM(--(br>X4#))))+1
Dynamic array formulas.

 

[Fluff]

Realised that won't work is someone scores over 100 try this instead

Fluff.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1
2	17	3	5	11	9	13	15	7	1		18	4	6	12	10	14	16	8	2
3
4	4	4	3	4	3	4	3	4	5	34	4	4	3	3	3	3	3	3	5	31	65	065005005004004003003004003003003004003004003003003004004	3
5	5	4	2	4	3	5	2	3	4	32	4	3	2	4	6	4	3	3	4	33	65	065004004004003002002003003003006004004005004002003005004	2
6	3	3	3	3	4	4	3	3	3	29	3	4	2	4	3	4	3	4	3	30	59	059003003003004003002003004004003003004004004003003003003	1
7	5	4	3	4	4	5	3	4	3	35	5	4	3	4	4	5	3	4	3	35	70	070003003004004003003004004004004004004005005003003005005	5
8	4	4	3	4	4	5	3	4	3	34	4	4	3	4	4	5	3	4	3	34	68	068003003004004003003004004004004004004005005003003004004	4
Master

Cell Formulas
Range	Formula
L4:L8,V4:V8	V4	=SUM(M4:U4)
W4:W8	W4	=SUM(V4,L4)
X4:X8	X4	=BYROW(C4:W8,LAMBDA(br,CONCAT(CHOOSECOLS(SORTBY(TEXT(br,"000"),C2:W2),-1,SEQUENCE(,18)))))
Y4:Y8	Y4	=BYROW(X4#,LAMBDA(br,SUM(--(br>X4#))))+1
Dynamic array formulas.

 

[golfer22]

This works great! Thank You. I really appreciate it

 

[Fluff]

You're welcome & thanks for the feedback.

 

[golfer22]

Hey, me again. I've added a 2nd sheet, as the tournament spans 2 days. The formula above works great for day 1, when I do the same/similar formula for day 2, it does not calculate how I want/am expecting.

Here's the day 2 data (column AC is the expected results)

For rows 4 and 5, since L4 and L5 are the same value, and V4 and V5 are the same value, the ranking should then go off of row E, and since E4 is Less Than E5, row 4 should be 5 instead of 6.

for Day 2, I want the ranking to go off the Total (column Z) and then any ties go off of the hdcp D2 - V2.

Here's the formulas
AA: =BYROW(D4:Z9,LAMBDA(br,CONCAT(CHOOSECOLS(SORTBY(TEXT(br,"000"),D2:V2),-1,SEQUENCE(,18)))))
AB: =BYROW(AA4#,LAMBDA(br,SUM(--(br>AA4#))))+1

 

[Fluff]

since E4 is Less Than E5, row 4 should be 5 instead of 6.

But row 4 is 5 not 6

 

[golfer22]

But row 4 is 5 not 6

? in AB, row 4 is 6, row 5 is 5. It should be row 4 is 5, row 5 is 6.

for both rows 4 and 5, the total (Z) is the same, 139. So we go off of handicup, which is the lowest value by hdcp hole (in order L,V,E,O,F,P,K,U,H,R,G,Q,I,S,J,T,D,N).

Since the value in L4 and L5 is 4, then look at the value in V4 and V5, since that is 4, go to E4 and E5. Since E4 is less than E5, row 4 should be rank 5, row 5 rank 6.

Hopefully that makes sense

 

[Fluff]

Oops, I was looking at the wrong column.
The formula should be

=BYROW(D4:X9,LAMBDA(br,CONCAT(CHOOSECOLS(SORTBY(TEXT(br,"000"),D2:X2),-1,SEQUENCE(,18)))))