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

[Richard2542]

 

[StephenCrump]

What do you want to do about ties, e.g. what if three people have equal lowest gross? In this case, is first place decided on other factors, e.g. lowest net?

What if you can't split? Do all three get equal 1 rank, and all get eliminated from further prizes?

 

[Richard2542]

Stephen...we do a scorecard playoff...after the initial ranking, we enter and verify all winners scores; if OK and ties remain, we start at Hole 18 (check hole scores for those players that are tied) and move backwards until the tie is broken; this process will then change the balance of Prize Winners...the above process may need to done until we have clear (non tied winners).

 

[Richard2542]

Stephen...hope this better illustrates my above reply

 

[StephenCrump]

I think this should work. I have extended to iteration 5 (rather than the 3 required) just to test fully.

The two formulae will need array-entering unless you have the new Office365 dynamic functions.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1				Iteration	1		2		3		4		5
2	Player	Gross	Net		Gross	Net	Gross	Net	Gross	Net	Gross	Net	Gross	Net
3	Player 1	80	67	Keep					3
4	Player 3	79	66	blank			2
5	Player 3	86	69										5
6	Player 4	84	66							3
7	Player 5	85	68									4
8	Player 6	81	70								4
9	Player 7	87	64			1
10	Player 8	78	70		1
11	Player 9	81	68								4
12	Player 10	85	65					2
Sheet1

Cell Formulas
Range	Formula
E3:E12,G3:G12,I3:I12,K3:K12,M3:M12	E3	=IF((SUM($D3:D3)=0)*($B3=MIN(IF(NOT(MMULT(--ISNUMBER($D$3:D$12),TRANSPOSE(COLUMN($D$3:D$12)^0))),Gross))),E$1,"")
F3:F12,H3:H12,J3:J12,L3:L12,N3:N12	F3	=IF((SUM($D3:E3)=0)*($C3=MIN(IF(NOT(MMULT(--ISNUMBER($D$3:E$12),TRANSPOSE(COLUMN($D$3:E$12)^0))),Net))),E$1,"")

Named Ranges
Name	Refers To	Cells
Gross	=Sheet1!$B$3:$B$12	M3:M12, K3:K12, I3:I12, G3:G12, E3:E12
Net	=Sheet1!$C$3:$C$12	N3:N12, L3:L12, J3:J12, H3:H12, F3:F12

The results don't display properly above, but look like this:

			Iteration	1		2		3		4		5
Player	Gross	Net		Gross	Net	Gross	Net	Gross	Net	Gross	Net	Gross	Net
Player 1	80	67	Keep					3
Player 3	79	66	blank			2
Player 3	86	69										5
Player 4	84	66							3
Player 5	85	68									4
Player 6	81	70								4
Player 7	87	64			1
Player 8	78	70		1
Player 9	81	68								4
Player 10	85	65					2

For this particular example, there is a tie between Player 6 and Player 9 for 4/Gross. Let's say Player 9 wins on countback. The quickest way to deal with this is to clear the formula for Player 6 in K8. The results will then automatically adjust to:

			Iteration	1		2		3		4		5
Player	Gross	Net		Gross	Net	Gross	Net	Gross	Net	Gross	Net	Gross	Net
Player 1	80	67	Keep					3
Player 3	79	66	blank			2
Player 3	86	69											5
Player 4	84	66							3
Player 5	85	68									4
Player 6	81	70										5
Player 7	87	64			1
Player 8	78	70		1
Player 9	81	68								4
Player 10	85	65					2

 

[Richard2542]

Stephen...thanks sooooo much - it may take me a bit of time to "wrap my arms" around your solution. I'll keep you updated as to my progress and further questions (if necessary).

 

[Richard2542]

Stephen...absolutely brilliant - it really works; I am attempting to work out a solution for the ties - I have a method that I have used in the past (where I enter the hole scores from 18 to 1) until the tie is broken - a bit cumbersome, but it works...I would prefer not to clear/delete the formula. If you have further thoughts on this I would appreciate your recommendations.

Would you mind sharing a written description as to how the below formula works? There are a number of items that I am not familiar with.

{=IF((SUM($D3:D3)=0)*($B3=MIN(IF(NOT(MMULT(--ISNUMBER($D$3:D$12),TRANSPOSE(COLUMN($D$3:D$12)^0))),Gross))),E$1,"") }

Thanks again for all your help.

 

[StephenCrump]

Another way you could tie-break without touching the formulae would be to adjust the Player scores. In the example above, Player 6 and Player 9 tie for 4/Gross on 81. If Player 9 wins on countback, simply change Player 6's score (and the score of any other similarly tied Players) by +0.1.

Let's look at the formula in J6, which awards the 3/Net prize to Player 4:
=IF((SUM($D6:I6)=0)*($C6=MIN(IF(NOT(MMULT(--ISNUMBER($D$3:I$12),TRANSPOSE(COLUMN($D$3:I$12)^0))),Net))),I$1,"")

The Player satisfies two criteria:
1. The Player has not already won a prize, i.e. SUM($D6:I6)=0
2. The Player's net score is the lowest of those Players still to win a prize, i.e. =$C6=MIN(IF(NOT(MMULT(--ISNUMBER($D$3:I$12),TRANSPOSE(COLUMN($D$3:I$12)^0))),Net))
i.e. $C6 = MIN({FALSE;FALSE;69;66;68;70;FALSE;FALSE;68;FALSE}) which is TRUE.

All quite straightforward, except for getting the vector {FALSE;FALSE;69;66;68;70;FALSE;FALSE;68;FALSE}, which we do as follows:

Step A: =--ISNUMBER($D$3:I$12) shows a 1 where Players have already won a Prize.

To identify if a Player has already won, we need to summarise the rows, which we can do using matrix multiplication.

Step B: a long-winded way of producing a column of 1's 6 high (5 is the number of prizes already awarded, and I have added one blank column D at the start so that I can use the same formula for the first prize). (In Excel 365, there is a SEQUENCE function which would make this step cleaner).

Step C: Effectively, the MMULT collapses the columns, so you can see at a glance that Players 1, 2, 7, 8 and 10 have already won.

Step D: The net scores for the Players still to win a prize.

If you have the new dynamic functions in Excel 365, you can enter these formulae into a cell, and see the resulting array spilling into adjoining cells. If not, when you enter, say, =--ISNUMBER($D$3:I$12) into a single cell, the cell will display only 0 (the top, left hand value) but actually contain (if you use F2 to evaluate) the entire array.

	A: =--ISNUMBER($D$3:I$12)							B: =TRANSPOSE(COLUMN($D$3:I$12)^0)
Player 1	0	0	0	0	0	1		1
Player 3	0	0	0	1	0	0		1
Player 3	0	0	0	0	0	0		1
Player 4	0	0	0	0	0	0		1
Player 5	0	0	0	0	0	0		1
Player 6	0	0	0	0	0	0		1
Player 7	0	0	1	0	0	0
Player 8	0	1	0	0	0	0
Player 9	0	0	0	0	0	0
Player 10	0	0	0	0	1	0
	C: =MMULT(A,B)			=IF(NOT(C),NET SCORES)
	1			FALSE
	1			FALSE
	0			69
	0			66
	0			68
	0			70
	1			FALSE
	1			FALSE
	0			68
	1			FALSE

 

[Richard2542]

Stephen...my sincerest gratitude for your help. If you are interested, I would love to send you the finished excel worksheet (after I have completed the integration of your hard work - probably spend next week on the final development); unfortunately, to my knowledge, the website doesn't allow anything other than PGN, etc...your thoughts.

 

[StephenCrump]

If you're happy to share your workbook, and it doesn't contain personal information, you can upload to a third party site (I use box.com) and post the link to the Forum.

I don't speak Golf myself, but those who do will probably be keen to take a look.