Golf Tournament Score Card Ranking

golfer22

New Member
Joined
Aug 16, 2023
Messages
15
Office Version
  1. 365
Platform
  1. Windows
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):
1692207854090.png


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)
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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

1692384314052.png
 
Upvote 0
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.
 
Upvote 0
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)))))
 
Upvote 0
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:
1692386081421.png


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
 
Upvote 0
How about
Excel Formula:
=BYROW(FILTER(D4:Z63,D4:D63<>""),LAMBDA(br,CONCAT(CHOOSECOLS(SORTBY(TEXT(br,"000"),D2:Z2),-1,SEQUENCE(,18)))))
 
Upvote 0
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.
1695661400627.png


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
1695661698903.png
 
Upvote 0
Replace the formula in Y4 with
Excel Formula:
=XMATCH(X4,SORT(X$4#))+COUNTIFS(X$4:X4,X4)-1
and drag down.
 
Upvote 0

Forum statistics

Threads
1,223,885
Messages
6,175,184
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top