Excel Non VBA solution to Determine Winners - Low Gross/Low Net

[Richard2542]

 

[StephenCrump]

1. Top right hand side there's an Upload button that you can use to upload and store your file.

2. To upload Golf rank 2.xlsx for Post#17, I hovered over the file name in the list above. This displays a couple of buttons, including Link and Share. You can use either of these to access the url (e.g. as shown below) which you can paste as a link in this forum.

 

[Richard2542]

Tournament Worksheet - Fix Ties.xlsx | Powered by Box

app.box.com

 

[Richard2542]

I believe I sent the above link 5 minutes ago...hopefully 1 will get the info to you.

 

[StephenCrump]

I believe I sent the above link 5 minutes ago...hopefully 1 will get the info to you.

Sorry, I was writing #31 when you posted #30.

Downloaded your file now, thanks, and will take a look later today.

 

[Richard2542]

Thanks sooooooo much

 

[Richard2542]

Stephen...just a point of clarification - I need to retain ties in Round 1; it's only Round 2 where the problem exists. I resolve all of Round 2 ties with a scorecard playoff. The combined scorecard playoff (columns AP:BL), adds both Round 1 and Round 2 scores by Player/by Hole (we look back starting on Hole 18 to Hole 14). The Tie Breaker matrix (columns BS:DC) is determined by the results in columns BN:BO (this is where I believe the circular reference is when we try to alter the formula in Final Ranking (columns AD:AN)...I hope I didn't make this explanation too confusing.

 

[StephenCrump]

I think the nub of your problem is that your ranks look like this ...

Tournament Worksheet - Fix Ties.xlsx
	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN
25	1		2		3		4		5
26	Gross	Net	Gross	Net	Gross	Net	Gross	Net	Gross	Net
27
28								4
29
30										5
31		1
32
33
34
35					3
36
37
38					3
39	1
40
41
42								4
43				2
44			2
45
46									5
47
48						3
49							4
League Tournament

... and you need to split the ties highlighted for 3/Gross and 4/Net?

My Post #17 was based on a previous layout that had tie-breaker rankings in column CU. I'm not clear which column(s) you want to use to split these ties?

If you get circular references when you attempt to split ties, that would suggest the tiebreaker calculations are dependent on the results shown? If so, you'll need a 2-step approach:
- Calculate tiebreakers based on the results shown
- Have a separate block of formulae pointing to the same Gross/Net raw data, and also to the tie-breaker column(s), to come up with final rankings.

 

[StephenCrump]

On looking again, I think your tiebreaker columns are CJ for gross and DC for net?

In which case these formulae will do the tiebreaking:

Tournament Worksheet - Fix Ties - SJC fixed.xlsx
	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN
26	Gross	Net	Gross	Net	Gross	Net	Gross	Net	Gross	Net
27
28										5
29
30
31		1
32
33
34
35							4
36
37
38					3
39	1
40
41
42								4
43				2
44			2
45
46
47
48						3
49									5
50
League Tournament

Cell Formulas
Range	Formula
AE27:AE49,AG27:AG49,AI27:AI49,AK27:AK49,AM27:AM49	AE27	=IF((SUM($AD27:AD27)=0)*(SUM($I27)*100+SUM($CJ27)=MIN(IF(NOT(MMULT(--ISNUMBER($AD$27:AD$72),TRANSPOSE(COLUMN($AD$27:AD$72)^0))),IF($I$27:$I$72="",99999,$I$27:$I$72*100+IF($CJ$27:$CJ$72="",0,$CJ$27:$CJ$72))))),AE$75,"")
AF27:AF49,AH27:AH49,AJ27:AJ49,AL27:AL49,AN27:AN49	AF27	=IF((SUM($AD27:AE27)=0)*(SUM($J27)*100+SUM($DC27)=MIN(IF(NOT(MMULT(--ISNUMBER($AD$27:AE$72),TRANSPOSE(COLUMN($AD$27:AE$72)^0))),IF($J$27:$J$72="",99999,$J$27:$J$72*100+IF($DC$27:$DC$72="",0,$DC$27:$DC$72))))),AE$75,"")

But clearly, Clearly CJ and DC depend on the first iteration, with ties. So you'll need to do this as two steps.

 

[Richard2542]

Stephen...not sure how to perform "two steps". My hope is to avoid manual user intervention after the scores are entered. Where the present issue resides is in columns BN:BO (this is where I believe the circular reference exists). After the alternate Low Gross/Low Net rankings are created (columns AE:AN). the formulas in columns BN:BO actually ranks the the Low Gross and Low Net winner's (1 to 5)...this ranking "fuels" the Tie Breaker section (columns BR:DC); if a tie exists for the Flight, it is so designated by a "Yes"/"No" in cell BT25 or BT75, etc., if "Yes", the scorecard playoff is in place, until a "No" appears.
The easiest way to correct this problem when a tie exists is to view the worksheet Tie Breaker and determine which player won the specific place and then delete the other tied player(s) formula in the specific columns AE:AN. I am trying to eliminate this manual process...every attempt to restructure the formula in AE:AN results in a circular calculation reference.
I can't begin to tell you how much I appreciate your hard work and diligence in helping me resolve this issue.

 

[StephenCrump]

You're getting circular errors because you've got:

A. Formulae to determine ranks, with the possibility of ties.
B. Tiebreaker formulae depending on A, i.e. you calculate tiebreaks only if the ranks in A <=5 (the number of prizes).

So if you try to feed back B into A to break the ties, you'll get circular errors.

Looking at your tiebreak formulae (for the first time, sorry - I've been avoid digging into all your formulae) it looks like you're just looking for lowest scores counting back hole by hole from the 18th? In which case, why not calculate tiebreaks for all players on this basis?

Tiebreaker.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	N	10
2
3		HOLE
4	PLAYER	1	2	3	4	5	6	7	8	9
5	A	6	6	10	11	6	4	12	8	4
6	B	5	12	9	4	3	9	5	10	6
7	C	6	5	10	8	7	11	5	5	9	<-------
8	D	7	9	10	6	11	3	12	9	6
9	E	9	12	12	9	11	7	8	10	9	<-------
10	F	4	8	5	9	11	12	3	11	9	<-------
11	G	9	10	12	5	3	12	8	10	9	<-------
12	H	11	3	10	8	6	6	4	9	6
13	I	8	3	12	9	5	6	7	7	4
14	J	12	8	4	9	4	8	4	9	6
15
16	RANK
17	A	2	2	2	2	2	2	2	2	1
18	B	6	6	6	6	6	6	6	6	3
19	C	7	7	7	7	7	7	7	7	7	<-------
20	D	5	5	5	5	5	5	5	3	3
21	E	8	8	8	8	8	8	8	8	7	<-------
22	F	10	10	10	10	10	10	10	10	7	<-------
23	G	9	9	9	9	9	9	8	8	7	<-------
24	H	3	3	3	3	3	3	3	3	3
25	I	1	1	1	1	1	1	1	1	1
26	J	4	4	4	4	4	4	3	3	3
Sheet1

Cell Formulas
Range	Formula
B17:I26	B17	=IF(SUMPRODUCT(--(C$17:C$26=C17))>1,C17+N-SUMPRODUCT(--(IF(C$17:C$26=C17,B$5:B$14,999)>=B5)),C17)
J17:J26	J17	=RANK(J5,J$5:J$14,-1)

Named Ranges
Name	Refers To	Cells
N	=Sheet1!$B$1	B17:I26

The highlighted cells illustrate, for a hypothetical 9 holes, the countback for Players C, E, F and G. The rankings are fully resolved by the time we count back to round 6, and hence don't change before the final results in B17:B26.

In my Post#38, I provided formulae to resolve the tiebreaks, assuming the tiebreak rankings were in $CJ$27:$CJ$72 (for Gross) and $DC$27:$DC$72 (for Net). If you calculate tiebreaks ranks for all players, and put them in these cells, my formulae should work? And if (as it appears?) the countback is the same for Gross and Net, you'll only need the one set, rather than two, of tiebreak ranks.