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

[Richard2542]

 

[Richard2542]

I have a small problem and am hoping you can manage a solution asap. Your solution works perfectly with 1 small problem, if a player within the list does not record a score or the score field (Gross Score) is blank, the array will show all blanks in the entire column. One work around is for me to hand enter an outrageous score (999) and the array works. I've tried modifying the array to show this as blank (if cell A1 ="","",A1, but that doesn't solve it.

 

[StephenCrump]

Using the layout of Post#5, try:

E3: =IF((SUM($D3:D3)=0)*($B3=MIN(IF(NOT(MMULT(--ISNUMBER($D$3:D$12),TRANSPOSE(COLUMN($D$3:D$12)^0))),IF(Gross="",999,Gross)))),E$1,"")
F3: =IF((SUM($D3:E3)=0)*($C3=MIN(IF(NOT(MMULT(--ISNUMBER($D$3:E$12),TRANSPOSE(COLUMN($D$3:E$12)^0))),IF(Net="",999,Net)))),E$1,"")

 

[Richard2542]

Stephen...it works, you are a "genius, thanks again for a workable solution - enjoy your time "off the grid"...

 

[Richard2542]

Stephen...hope you enjoyed your time "off the grid"...I have put your brilliant solution to work this week and it works great with 1 small problem. The problem has to do with ties - earlier, you suggested that I clear the formula for the Player who lost in a scorecard playoff...it definitely worked, but I would appreciate a "work around formula suggestion". I have created a tie breaker matrix that automatically breaks ties on a "hole by hole look back", the problem is that I would like to eliminate that Player rank in the matrix without having to manually delete the formula (as it turns out, the Player who lost the Low Gross playoff actually took last place in the Low Net, but only after I manually deleted him from the Low Gross ranking). Thanks in advance.

 

[StephenCrump]

If you have a single tiebreak table, then perhaps something like this:

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

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

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

The results highlighted show that Player 6 and Player 9 tie for the 4/Gross position, both scoring 81. Player 6 gets the award because s/he ranks 3, rather than 5, in the tiebreaker table.

 

[Richard2542]

Stephen...I must be missing something or not interpreting your solution correctly:

As soon as I enter the Array (based on my interpretation of your above recommended formula) in my cell AK78 (he's 1 of the 3 tied Players), I get a circular calculation. My Tie Break table is CD75:CU122 (this table uses the recorded strokes by Player from hole 18 to Hole 14 (I've hidden a number of columns for clarity sake). I believe all the relevant rows and columns are shown above.
We're soooo close to solving this problem thanks to your excellent help...

 

[StephenCrump]

Here's my spreadsheet using your layout: Golf 2.xlsx

My formula for AK78 (yours is a bit different!): =IF((SUM($AD78:AJ78)=0)*($I78*100+$CU78=MIN(IF(NOT(MMULT(--ISNUMBER($AD$77:AJ$122),TRANSPOSE(COLUMN($AD$77:AJ$122)^0))),IF($I$77:$I$122="",99999,$I$77:$I$122*100+$CU$77:$CU$122)))),AK$75,"")

Note too that my tie-breaker ranks are values, not formulae. I am assuming there is some sort of countback formulae that will uniquely rank all participants independent of the ranking for gross/net prizes (?)

If you are using formulae to identify ties, then calculating a final tiebreaker rank, to feed back into the formulae, you'll necessarily get circular errors.

So if your tiebreaker ranks rely on you first identifying ties, then you'll need to adopt a two step approach.
- Use my original formulae
- Identify ties and calculate tiebreaker ranks
- Use my new formula in a separate work area, pointing also to to the tiebreaker ranks.

 

[Richard2542]

Stephen...I still have a few issues:
I used the revised formula in AK77:AK122 (I assume the formula should still be an Array)…unfortunately it changed all the tied Players (A78,A81 and A92) to 0. I believe that the circular reference is being caused by column BY (Matrix Rank). The Tie Breaker matrix for Low Gross (column CD:CU) uses the Matrix Rank (column BY – the formula is a vLookup – range name: MatrixRank_B = $A$77:$AN$122) to list Ranked values and then breaks ties based on a scorecard playoff…the Final Ranks are displayed in column CU.

 

[StephenCrump]

It's a little hard to see what's going on from screenshots. Can you please post either

- your formulae, using XL2BB: XL2BB - Excel Range to BBCode
- the relevant bits of your workbook (removing any personal information)

 

[Richard2542]

Stephen...I have tried every approach to install this Add-On and still can't get it to work - Office Pro 2013. Keep getting "System Error &H8007007E (-2147024770). The specific module could not be found." I have followed the written instructions, tried the Alternative Installation, and watched the video...still no luck.
I reported the above above problem and they don't have a fix. I'll keep trying.