chappo555 wrote:
My question is.

I have the following data (measured in 1/1000th of an inch)(I have
rounded down in this example though!!!)

Name A B C D E AGG Real RANK
AAAAA 0.11 0.12 0.09 0.12 0.27 0.142 2
BBBBB 0.12 0.114 0.08 0.135 0.261 0.142 1
CCCCC 0.35 0.17 0.17 0.16 0.11 0.192 3

copied down to a minimum of 100 rows (names) The AGG is the sum(a:e/5)

When u use RANK(agg,agg1:agg100,1) It ties Names AAAA and BBBB as they
both have AGG of 0.142. Problem is the rule book states ties are split
by the smallest of the results for each competitor in columns A to E.
In this example BBBB is 2nd as he has smallest result in Column C being
0.08.

I have tried the AGG+1/min(a:e) and still end up with incorrect
results. The problem is the numbers are so small and you have to split
ties all the way to the 100th person involved. I cant use sort either
as the data has to be printed in alphabeticall name order all the way
to 100 names with their ranks recorded against the names.

Any help would be greatfully appreciated.

cheers and thanks
chappo555


Hi chappo555

I'm interested to see what formula will resolve your problem but in the
meantime you could try this:

In column H put your tie breaker i.e. in H2 put =MIN(B2:F2) and copy it
down as far as necessary.

Then use this macro:

Sub AverageMinimumSort()
Dim LastRow As Integer
Dim myCell As String
Application.ScreenUpdating = False
myCell = ActiveCell.Address
LastRow = Range("A65536").End(xlUp).Row
Range("A2", Cells(LastRow, "J")).Sort Key1:=Range("G2"), _
Order1:=xlAscending, Key2:=Range("H2"), Order2:=xlAscending
With Range("I2", Cells(LastRow, 9))
.Formula = "=Row()-1"
.Copy
.PasteSpecial xlPasteValues
End With
Range("A2", Cells(LastRow, "J")).Sort Key1:=Range("A2"),
Order1:=xlAscending
Range(myCell).Select
Application.ScreenUpdating = True
End Sub

Hope that's enough to get you going for now

Regards

Steve