PLEASE!!! Help...need formula to break tie amounts....

Just a suggestion....

Why don't you just sort your data (after inputting all variables) with the first sort being on column V (ascending) and the secondary sort being on column C (ascending again). You could then fill a blank column with numbers 1 to the last row.

Would this work for you?

BarrieBarrie Davidson

Barrie...Re: Just a suggestion....

I cannot do what you suggested because I need to assign the point values for each event first (that is need to break ties in columns L O R first) If there is a tie in column V then it ends up being just a tie.

Good webpage...you know your stuff. Any more help would be appreciated. Thanks

Re: Barrie...I think I've got it!

Barrie -

I think I solved it. I built 4 macros, one each for sorting and assigning values for tie amounts. My first test worked. Thanks for your input

I have had to do a slight bit of redesign of your spreadsheet, but I think the following has all the bells and wistles you want.

Changes are to put these at the top.

Weight Class 236+
Bench Minimum 250
Squat Minimum 385
VJ Minimum 28
40 Maximum 5.1

The "250" from above should be in cell C2. The other numbers are also in column C.

Have the first person in the match listed on row 9. (Using a different row to start will throw off the macros.)

Cell G9 gets this:
=IF(ISBLANK(F9),"",IF(MAX(D9:F9)<C$2,"Disqualified",MAX(D9:F9)))

Cell K9 gets this:
=IF(ISBLANK(J9),"",IF(MAX(H9:J9)<C$3,"Disqualified",MAX(H9:J9)))

Leave columns M through S blank. The Macros will fill them in.

Here are the macros. I would suggest putting a button on your tool bar or in the spreadsheet in order to run the macro on demand.

Dim RwCnt, PlcCnt, Cnt1, Cnt2, Cnt3, Tie(10), LowRw, TieCnt As Integer
Sub Macro1()
Dim TieVlu As Single
RwCnt = 9
Do While Range("A" & RwCnt + 1) <> Empty
RwCnt = RwCnt + 1
Loop
Range("R9") = "=if(and(isnumber(M9),isnumber(O9),isnumber(Q9)),M9+O9+Q9," & """" & "NA" & """" & ")"
Range("R9").Copy
Range("R10:R" & RwCnt).Select
ActiveSheet.Paste
Application.CutCopyMode = False
'Disqualify as necessary in individual events
For Cnt1 = 9 To RwCnt
If IsNumeric(Range("N" & Cnt1)) And Range("N" & Cnt1) < Range("C4") Then
Range("N" & Cnt1) = "x" & Range("N" & Cnt1) & " DQ"
End If
If IsNumeric(Range("P" & Cnt1)) And Range("P" & Cnt1) > Range("C5") Then
Range("P" & Cnt1) = "x" & Range("P" & Cnt1) & " DQ"
End If
If IsNumeric(Range("G" & Cnt1)) And IsNumeric(Range("K" & Cnt1)) Then
Range("L" & Cnt1) = Range("G" & Cnt1) + Range("K" & Cnt1)
Else
Range("L" & Cnt1) = "x" & Val(Range("G" & Cnt1)) + Val(Range("K" & Cnt1)) & " DQ"
End If
Next
'Disqualify all events for a person if appropriate
For Cnt1 = 9 To RwCnt
If Not (IsNumeric(Range("L" & Cnt1)) And IsNumeric(Range("N" & Cnt1)) And IsNumeric(Range("P" & Cnt1))) Then
If IsNumeric(Range("L" & Cnt1)) Then
Range("L" & Cnt1) = Range("L" & Cnt1) & " DQ"
End If
If IsNumeric(Range("N" & Cnt1)) Then
Range("N" & Cnt1) = Range("N" & Cnt1) & " DQ"
End If
If IsNumeric(Range("P" & Cnt1)) Then
Range("P" & Cnt1) = Range("P" & Cnt1) & " DQ"
End If
End If
Next
'Put in Ranking equations
Call RankEm("L", "M", 0)
Call RankEm("N", "O", 0)
Call RankEm("P", "Q", 1)
Call RankEm("R", "S", 0)
'Tie Breaking
For Cnt1 = 1 To RwCnt - 8
Call UnTie("M", "LtWt")
Call UnTie("O", "HvWt")
Call UnTie("Q", "HvWt")
Next
For Cnt1 = 9 To RwCnt
If IsNumeric(Range("M" & Cnt1)) Then
Range("M" & Cnt1) = Range("M" & Cnt1) * 3
End If
Next
End Sub
Private Sub RankEm(Col1, Col2, Ord)
Range(Col2 & 9) = "=IF(ISNUMBER(" & Col1 & "9),RANK(" & Col1 & "9," & Col1 & "$9:" & Col1 & "$" & RwCnt & "," & Ord & ")," _
& """" & "N/A" & """" & ")"
Range(Col2 & 9).Copy
Range(Col2 & "10:" & Col2 & RwCnt).Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub
Private Sub UnTie(Col, Wate)
Cnt2 = 0
For Cnt3 = 9 To RwCnt
If Range(Col & Cnt3) = Cnt1 Then
Cnt2 = Cnt2 + 1
Tie(Cnt2) = Cnt3
End If
Next
If Cnt2 > 1 Then
LowRw = Tie(1)
TieCnt = 1
For Cnt3 = 2 To Cnt2
If Wate = LwWt Then
If Range("C" & Tie(Cnt3)) < Range("C" & LowRw) Then
LowRw = Tie(Cnt3)
TieCnt = 1
Else
If Range("C" & Tie(Cnt3)) = Range("C" & LowRw) Then
TieCnt = TieCnt + 1
End If
End If
Else
If Range("C" & Tie(Cnt3)) > Range("C" & LowRw) Then
LowRw = Tie(Cnt3)
TieCnt = 1
Else
If Range("C" & Tie(Cnt3)) = Range("C" & LowRw) Then
TieCnt = TieCnt + 1
End If
End If
End If
Next
TieVlu = Cnt1
For Cnt3 = 2 To TieCnt
TieVlu = TieVlu + Cnt1 + Cnt3 - 1
Next
TieVlu = TieVlu / TieCnt
For Cnt3 = 1 To Cnt2
If Wate = LwWt Then
If Range("C" & LowRw) < Range("C" & Tie(Cnt3)) Then
Range(Col & Tie(Cnt3)) = Cnt1 + TieCnt
Else
Range(Col & Tie(Cnt3)) = TieVlu
End If
Else
If Range("C" & LowRw) > Range("C" & Tie(Cnt3)) Then
Range(Col & Tie(Cnt3)) = Cnt1 + TieCnt
Else
Range(Col & Tie(Cnt3)) = TieVlu
End If
End If
Next
End If
End Sub

If the program doesn't run, there may be a problem with how this board deals with long command lines.

What the program does:
For the 1st 2 categories, it checks to see if the lift weight is at or above the minimum. If not, it enters a "disqualified" for the best amount. For the last 2 categories, it checks to see if the minimums have been met. If not, it leads the amount with an "x", and ends with a " DQ". (It does similar on the combined lift total.)

The program will then check on the the 3 category totals, and if someone is eliminated in one category, it will add the " DQ" in the other categories so that the person will not be ranked in any of the events.

The program then goes through all the rankings and does the tie breaking. It will allow for as many contestants as you want (until it finds a blank in column A). It also will allow for all contestants to be tied.

Its late. I've been at this too long. I would suggest you enter several numbers and test the results the program provides. I'm going to bed. Good Morning!