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]

Awesome! That works.

I appreciate the quick response again.

 

[golfer22]

oops. sorry. spoke to soon. That changes the values of the other rows. Row 9 should still be rank 1 since it has the lowest value in row z. Looks like the new formula is ordering by row x first

 

[Fluff]

Can you post some sample data rather than images.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[golfer22]

oops. sorry. spoke to soon. That changes the values of the other rows. Row 9 should still be rank 1 since it has the lowest value in row z. Looks like the new formula is ordering by row x first

View attachment 97388

nvm, I got it.

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

 

[Fluff]

Glad you sorted it.

 

[golfer22]

One last question and I'll stop bugging you. what would be the formula to only calculate the rank if the total is greater than 0?

Here's the data:

I increased the range for the formula in AA to 63:
=BYROW(D4:Z63,LAMBDA(br,CONCAT(CHOOSECOLS(SORTBY(TEXT(br,"000"),D2:Z2),-1,SEQUENCE(,18)))))

I assume we can modify the formula in AB to not calculate if AA result is 0:
=BYROW(AA4#,LAMBDA(br,SUM(--(br>AA4#))))+1

 

[Fluff]

How about

=BYROW(FILTER(D4:Z63,D4:D63<>""),LAMBDA(br,CONCAT(CHOOSECOLS(SORTBY(TEXT(br,"000"),D2:Z2),-1,SEQUENCE(,18)))))

 

[golfer22]

that worked

 

[golfer22]

Hey, me again. I have one more issue where I'm not wanting to show ties in the PLACE.

For example, in the data below, rows 14 and 15 got the exact same score on every hole, so they tied, so they are in a tie for 9th. due to how I'm using the place in other parts of the excel, I'm not wanting to show them as tied, and instead have the first row be 9, then the second row be 10.

I tried to get around this by adding the row() function to the concat in the formula in X4, but that is just concatenating 4 to every row instead of the row number.

Any assistance is appreciated.

Current Formulas:
X4: =BYROW(FILTER(C4:W63,D4:D63<>""),LAMBDA(br,CONCAT(CHOOSECOLS(SORTBY(TEXT(br,"000"),C2:W2),-1,SEQUENCE(,18)))))
Y4: =BYROW(X4#,LAMBDA(br,SUM(--(br>X4#))))+1

what I tried:
X4: =BYROW(FILTER(C4:W63,D4:D63<>""),LAMBDA(br,CONCAT(CHOOSECOLS(SORTBY(TEXT(br,"000"),C2:W2),-1,SEQUENCE(,18)),row())))
Y4: =BYROW(X4#,LAMBDA(br,SUM(--(br>X4#))))+1

screenshot with expected result

 

[Fluff]

Replace the formula in Y4 with

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

and drag down.