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]

Thanks

 

[Fluff]

You're welcome.

 

[golfer22]

Hey, I running into the tie issue now with the new formula. I believe it may be due to excel converting the numbers in column X to UPC (12 digits) when doing the conversion/formula in column Y. So since the first 12 digits match, it's showing them as a TIE. Any ideas how to correct this?

Here's the sample data:

Row 36:
X formula: =BYROW(FILTER(C4:W63,D4:D63<>""),LAMBDA(br,CONCAT(CHOOSECOLS(SORTBY(TEXT(br,"000"),C2:W2),-1,SEQUENCE(,18)))))
X value: 073003004004005003004004004004004004004005005003003004006
Y formula: =IFERROR(XMATCH(X36,SORT(X$4#))+COUNTIFS(X$4:X36,X36)-1,"")
Y value: 32
Expected Y value: 33

Row 60:
X formula: =BYROW(FILTER(C4:W63,D4:D63<>""),LAMBDA(br,CONCAT(CHOOSECOLS(SORTBY(TEXT(br,"000"),C2:W2),-1,SEQUENCE(,18)))))
X value: 073003004004005003003004004004005004003005005003004005005
Y formula: =IFERROR(XMATCH(X60,SORT(X$4#))+COUNTIFS(X$4:X60,X60)-1,"")
Y value: 32
Expected Y value: 32

Since the score on hole 12 ( 6 hdcp) is 3 on row 60 and 4 on row 36, row 60 would be the lower place (32) and row 36 would be 33.

 

[golfer22]

Here's a condensed version of the data to make it easier:

Formula's:

Row	Formula
X	=BYROW(FILTER(C4:W6,D4:D6<>""),LAMBDA(brt,CONCAT(CHOOSECOLS(SORTBY(TEXT(brt,"000"),C2:W2),-1,SEQUENCE(,18)),ROW())))
Y	=IFERROR(XMATCH(X4,SORT(X$4#))+COUNTIFS(X$4:X4,X4)-1,"")
Z	=BYROW(X4#,LAMBDA(br,SUM(--(br>X4#))))+1

 

[Fluff]

Try

=XMATCH(X4,SORT(X$4#))+ROWS(FILTER(X$4:X4,X$4:X4=X4))-1

 

[golfer22]

Amazing!