Excel Rank Function with 18 Decimal Places

[jlhoover3]

Hello everyone,

I have a golf leaderboard sheet that I'm creating that will help rank and flight the tournament as needed. One thing that comes up often is the tie breaker that can change the flight you may be in (depending on ties). The way we do our tie breaker is pick a hole to start on and continue to the next hole until the tie breaker has been decided. For example, if the tie breaker hole starts on hole 3, then the tie breaker goes in this hole sequence (3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 1, and 2).

So far I have it all planned out by getting the Initial Rank on the total scores, and then adding the scores of each hole as a decimal. For example, Team A scores 69 and is initially ranked 2nd, and their hole by hole scores (starting on hole 3) go 4-4-4-4-4-4-4-4-4-4-4-4-1-4-4-4-4-4, with a final Tie Break Value of 2.444444444444144444. Then I would just use the rank function on the Tie Break value to find the answers. However, Excel only carries out 15 digits for the number and pulls the number as such, 2.444444444444100000. Does anybody have any suggestions on how I can get this to read all digits? Below is my equation to pull the Tie Breaking Formula.

=VALUE(CONCAT([@[I Rank]],".",TEXTJOIN("",TRUE,MensLeaderboard[@[T1]:[T18]])))

Thanks again for the help!

 

[Peter_SSs]

1. Can a team score for a hole be double-digit? If so that would ruin the system anyway wouldn't it?
2. Can the 'I Rank' value for a team be more than one digit?

Assuming both could possibly be double-digits and that you are looking for the lowest score as best could we use text sorting instead of numerical sorting due to Excel's significant figures limit?

Something like this

jlhoover3.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1
2	I Rank	T1	T2	T3	T4	T5	T6	T7	T8	T9	T10	T11	T12	T13	T14	T15	T16	T17	T18	Tiebreak	Rank
3	2	4	4	4	4	4	4	4	4	4	3	4	4	4	4	4	4	4	4	02.040404040404040404030404040404040404	2
4	2	4	4	4	4	4	4	4	4	4	3	4	4	4	4	4	4	4	4	02.040404040404040404030404040404040404	2
5	3	4	4	4	4	4	4	4	4	4	4	4	5	4	4	4	4	4	4	03.040404040404040404040405040404040404	4
6	2	3	5	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	02.030504040404040404040404040404040404	1
7
Rank

Cell Formulas
Range	Formula
U3:U6	U3	=CONCAT(TEXT([@[I Rank]],"00."),TEXT(MensLeaderboard[@[T1]:[T18]],"00"))
V3:V6	V3	=MATCH([@Tiebreak],SORT([Tiebreak]),0)

In the above the T1, T2 etc are your tiebreak scores as you stated so I guess starting at hole 3. To save you manually getting those numbers from a particular hole, perhaps it would help to simply enter the hole-by-hole scores holes 1 to 18 as normal and have the formulas rearrange them for starting at a particular hole like this where this time the holes are 1-18 and the starting hole is indicated in a separate cell (V10)

jlhoover3.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
10																				Tiebreak Start Hole -->	5
11
12	I Rank	H1	H2	H3	H4	H5	H6	H7	H8	H9	H10	H11	H12	H13	H14	H15	H16	H17	H18	Tiebreak	Rank
13	2	4	4	4	4	4	4	4	4	4	3	4	4	4	4	4	4	4	4	02.040404040403040404040404040404040404	1
14	2	4	4	4	4	4	4	4	4	4	3	4	4	4	4	4	4	4	4	02.040404040403040404040404040404040404	1
15	3	4	4	4	4	4	4	4	4	4	4	4	5	4	4	4	4	4	4	03.040404040404040504040404040404040404	4
16	2	3	5	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	02.040404040404040404040404040403050404	3
17
Rank

Cell Formulas
Range	Formula
U13:U16	U13	=LET(Holes,CONCAT(TEXT(MensLeaderboard2[@[H1]:[H18]],"00")),CONCAT(TEXT([@[I Rank]],"00."),MID(Holes&Holes,V$10*2-1,36)))
V13:V16	V13	=MATCH([@Tiebreak],SORT([Tiebreak]),0)

 

[jlhoover3]

Peter,

Awesome help with a great solution. You are correct, that my idea or thought would not have worked in case there was a double digit score. Although, it shouldn't happen, never leave any doubt!

I initially had everything you stated in the first solution, except I had helper cells doing the tiebreak start hole for me based on a cell value (like you did in solution 2). I didn't think about using a different sort function with text strings. Very Very Smart!

I ended up using your last solution which doesn't require any helper cells. It is working flawlessly and a huge help with the amount of tournaments I run each year. This is the first time I have used the LET function, and definitely now won't be the last! Again, thanks so much for the help!

 

[Peter_SSs]

You're welcome. Glad it is working for you. Thanks for the follow-up.