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

R

Richard2542

Board Regular
Joined
Apr 27, 2017
Messages
102
Office Version
  1. 365
  2. 2013
1588886445998.png
 
Sort by date Sort by votes
Can you post a link to a cut-down version of your workbook, i.e. corresponding to the pictures shown in Post #18?
 
Upvote 0

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Stephen...I would be glad to do the above, but I'm not sure how to do this...please be more specific and include steps I need to take. Thanks for your patience - I appreciate all you have done.
 
Upvote 0
Stephen...I think the below explanation may be less confusing:
The original formula in cell AK78: {=IF($I78="","",IF((SUM($AD78:AJ78)=0)*($I78=MIN(IF(NOT(MMULT(--ISNUMBER($AD$77:AJ$122),TRANSPOSE(COLUMN($AD$77:AJ$122)^0))),IF($I$77:$I$122="",999,$I$77:$I$122)))),AK$75,""))}

AK 78 and AK81 are tied (actually there were 3 tied players).
1598381398999.png


Revised the below to reflect your recommended formula change: AK78 and AK81: {=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,"")}…this change place a “0” in cells AK78 and AK81 and created a circular reference error.
1598381481501.png


I believe the circular reference is due to how I am determining ties. The Matrix Rank formula uses the Players rank (1 to 5) and shows ties. If ties are present the “Tie Breaker” cell CE75 will show “Yes”/”No”…a scorecard playoff is automatically calculated for “Yes” until the Final Rank is obtained (column CU).
1598381522283.png
 
Upvote 0
Is there an alternate way I can send you the Excel worksheet (I would be happy to email a copy)
 
Upvote 0
Stephen...I finally got XL2BB to work...here is Part 1:
SignUp and Winners Sheet Master 08-13-2020 Rev 15S - Round 4 - Fix Matrix.xlsm
ADAEAFAGAHAIAJAKALAMANBYBZCDCECFCGCHCICJCKCU
74Player Rank - Round 2 - Ties B Flight
7512345Break Below TiesTies:YesTie Breaker - Hole 18YesTie Breaker - Hole 17No 
76GrossNetGrossNetGrossNetGrossNetGrossNetMatrix Rank GrossMatrix Rank NetLow Gross:RankBreak TiesAdj Rank 2RankBreak TiesAdj Rank 3RankFinal Rank
77                    
78      0   4 00.164.1650.135.13  
79                    
80    3     3 30.003.0030.003.0033
81      0   4 40.164.1650.025.0255
82       4   4        
831         1 10.001.0010.001.0011
84                    
League Tournament
Cell Formulas
RangeFormula
AE74AE74="Player Rank - Round 2 - Ties "&A76
BY75BY75=IF(OR(SUM(BY77:BY122)<>$CF$24,SUM(BZ77:BZ122)<>$CF$24),"Break Below Ties","")
CE75CE75=IF($CE123="","",IF($CE123<>$CF$24,"Yes","No"))
CF75CF75=IF(OR(CE75="Yes",CH75="Yes"),"Tie Breaker - Hole 18","")
CH75CH75=IF($CH123="","",IF($CH123<>$CF$24,"Yes","No"))
CI75CI75=IF(OR(CH75="Yes",CK75="Yes"),"Tie Breaker - Hole 17","")
CK75CK75=IF($CK123="","",IF($CK123<>$CF$24,"Yes","No"))
CU75CU75=IF(OR(CT75="Yes",CW75="Yes"),"Tie Breaker - Hole 13","")
AK82:AK84,AK79:AK80,AE77:AE84,AM77:AM84,AK77,AI77:AI84,AG77:AG84AE77=IF($I77="","",IF((SUM($AD77:AD77)=0)*($I77=MIN(IF(NOT(MMULT(--ISNUMBER($AD$77:AD$122),TRANSPOSE(COLUMN($AD$77:AD$122)^0))),IF($I$77:$I$122="",999,$I$77:$I$122)))),AE$75,""))
AF77:AF84,AN77:AN84,AL77:AL84,AJ77:AJ84,AH77:AH84AF77=IF($J77="","",IF((SUM($AD77:AE77)=0)*($J77=MIN(IF(NOT(MMULT(--ISNUMBER($AD$77:AE$122),TRANSPOSE(COLUMN($AD$77:AE$122)^0))),IF($J$77:$J$122="",999,$J$77:$J$122)))),AE$75,""))
AK78AK78=IF($I78="","",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,""))
AK81AK81=IF($I81="","",IF((SUM($AD81:AJ81)=0)*($I81*100+$CU81=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,""))
BY77:BY84BY77=IF(VLOOKUP(A77,MatrixRank_B,31,FALSE)<>"",VLOOKUP(A77,MatrixRank_B,31,FALSE),IF(VLOOKUP(A77,MatrixRank_B,33,FALSE)<>"",VLOOKUP(A77,MatrixRank_B,33,FALSE),IF(VLOOKUP(A77,MatrixRank_B,35,FALSE)<>"",VLOOKUP(A77,MatrixRank_B,35,FALSE),IF(VLOOKUP(A77,MatrixRank_B,37,FALSE)<>"",VLOOKUP(A77,MatrixRank_B,37,FALSE),IF(VLOOKUP(A77,MatrixRank_B,39,FALSE)<>"",VLOOKUP(A77,MatrixRank_B,39,FALSE),"")))))
BZ77:BZ84BZ77=IF(VLOOKUP(A77,MatrixRank_B,32,FALSE)<>"",VLOOKUP(A77,MatrixRank_B,32,FALSE),IF(VLOOKUP(A77,MatrixRank_B,34,FALSE)<>"",VLOOKUP(A77,MatrixRank_B,34,FALSE),IF(VLOOKUP(A77,MatrixRank_B,36,FALSE)<>"",VLOOKUP(A77,MatrixRank_B,36,FALSE),IF(VLOOKUP(A77,MatrixRank_B,38,FALSE)<>"",VLOOKUP(A77,MatrixRank_B,38,FALSE),IF(VLOOKUP(A77,MatrixRank_B,40,FALSE)<>"",VLOOKUP(A77,MatrixRank_B,40,FALSE),"")))))
CE77:CE84CE77=IFERROR(IF($I77="","",IF($BH$23=0,"",IF(BY77>$BH$23,"",BY77))),"")
CF77:CF84CF77=IFERROR(IF($I77="","",IF($CE$75="No","",IF(CE77="","",IF(COUNTIF($I$77:$I$122,$I77)>1,RANK(BT77,$BT$77:$BT$122,1)/100,0)))),"")
CG77:CG84CG77=IFERROR(IF($I77="","",IF($CE$75="No","",IF(CE77="","",CE77+CF77))),"")
CH77:CH84CH77=IFERROR(IF($I77="","",IF($CE$75="No","",IF($BH$23=0,"",IF(RANK($CG77,$CG$77:$CG$122,1)>$BH$23,"",RANK($CG77,$CG$77:$CG$122,1))))),"")
CI77:CI84CI77=IFERROR(IF($I77="","",IF($CH$75="No","",IF(CH77="","",IF(COUNTIF($I$77:$I$122,$I77)>1,RANK(BS77,$BS$77:$BS$122,1)/100,0)))),"")
CJ77:CJ84CJ77=IFERROR(IF($I77="","",IF($CH$75="No","",IF(CH77="","",CH77+CI77))),"")
CK77:CK84CK77=IFERROR(IF($I77="","",IF($CH$75="No","",IF($BH$23=0,"",IF(RANK($CJ77,$CJ$77:$CJ$122,1)>$BH$23,"",RANK($CJ77,$CJ$77:$CJ$122,1))))),"")
CU77:CU84CU77=IF($CE$75="No",CE77,IF($CH$75="No",CH77,IF($CK$75="No",CK77,IF($CN$75="No",CN77,IF($CQ$75="No",CQ77,IF($CT$75="No",CT77,""))))))
Press CTRL+SHIFT+ENTER to enter array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
CK77:CK122Cell Valueunique valuestextNO
CH77:CH122Cell Valueunique valuestextNO
CE77:CE122Cell Valueunique valuestextNO
 
Upvote 0
Part 2:
SignUp and Winners Sheet Master 08-13-2020 Rev 15S - Round 4 - Fix Matrix.xlsm
AIJ
75Combined Scores
76B FlightGrossNet
77BAUZENBERGER, RICHARD188143.70
78BEWALDA, BOB177133.10
79BOESELAGER, JEROME  
80EDWARDS, DON173134.00
81MICHAUD, KEN177135.60
82MOYER, JACK178133.06
83RITT, DON165124.76
84SCHLAEFER, STEVE180137.74
League Tournament
Cell Formulas
RangeFormula
A76A76=IF('Event & Dates'!$C$5<>"League Tournament","","B Flight")
A77:A84A77=IFERROR(IF('Event & Dates'!$C$5<>"League Tournament","",INDEX(AllLeague,SMALL(IF('Flight and Handicap'!$G$22:$G$250="B",ROW(AllLeague)-ROW('Flight and Handicap'!A$22)+1),ROW(1:1)))),"")
I77:I84I77=IFERROR(IF(OR(E77="",F77=""),"",E77+F77),"")
J77:J84J77=IF(OR(G77="",H77=""),"",G77+H77)
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Stephen...The new formula that you suggested which returns 0's is only in cells AK 78 and AK 81...the balance of the matrix formulas are are your original formula. The original formula will return 4 in both places (thereby a tie).
 
Upvote 0
Thanks, but I'm still missing a bit of the puzzle, e.g. there are references to columns BH and BT which are essential to produce the results you display, but I have no idea what's in these cells.

Normally, XL2BB is the better option for posting to the forum, as not everyone can/will download other people's files. But given the size of your workbook, it's going to be easier/faster for me to diagnose direct.

I use box.com to post files, e.g. as in my Post#17. There are other similar no-nonsense providers that are also free, and don't require others to login or instal software to download files.

If you do post your file, you may want to post only the one relevant sheet, and anonymise your players.
 
Upvote 0
Stephen...just opened an account in Box.com - username Richard Jackson - file name: Tournament Worksheet - Fix Ties.xlsx. I have never used this before so I hope that everything is setup correctly...not sure how to share/give you access to the file.
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
Advice for Excel integration
Replies
1
Views
182
Jeffrey Mahoney
Jeffrey Mahoney
D
  • Question
Using VBA to determine Maximum Values in an Excel Spreadsheet
Replies
4
Views
122
Artik
Artik
K
  • Question
User Name formula - Non-VBA
Replies
2
Views
169
Cubist
Cubist
S
  • Solved
Need VBA suggestions to import and update excel data
Replies
4
Views
299
Akuini
Akuini
B
  • Question
macOS excel VBA or alternate solution
Replies
0
Views
160
bcoz70
B

Forum statistics

Threads
1,217,213
Messages
6,135,245
Members
449,922
Latest member
qingyue

Share this page

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