Ranking multiple columns by 1000th inch

[chappo555]

Gudday to all excell gurus, I am new to this forum so please be gentle. AND yes I have searched before posting however cannot find accurate solution.

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

[Scoops]

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

[Domenic]

Assuming that A2:G4 contains the data, try the following formula which
needs to be confirmed with CONTROL+SHIFT+ENTER...

H2, copied down:

=RANK(G2,G$2:G$4,1)+IF(COUNTIF(G$2:G$4,G2)>1,MATCH(MIN(B2:F2),SMALL(IF(G$
2:G$4=G2,SUBTOTAL(5,OFFSET(B$2:F$4,ROW(B$2:F$4)-ROW(B$2),0,1))),ROW(INDIR
ECT("1:"&COUNTIF(G$2:G$4,G2)))),0)-1)

Hope this helps!

In article <[email protected]>,
chappo555 <[email protected]>
wrote:

> Gudday to all excell gurus, I am new to this forum so please be gentle.
> AND yes I have searched before posting however cannot find accurate
> solution.
>
> 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

[Herbert Seidenberg]

Assume you have a 12x5 array of numbers named ArrayA (not shown),
and there are multiple 2 way ties and one 3 way tie.
Arrange the adjacent area like this:
aggs mins ranks dups corr rank_real
0.438 0.052 1 1 0 1
0.567 0.137 9 3 0 9
0.548 0.144 5 2 0 5
0.567 0.248 9 3 1 10
0.491 0.002 2 2 0 2
0.602 0.226 12 1 0 12
0.548 0.358 5 2 1 6
0.564 0.063 7 2 0 7
0.564 0.220 7 2 1 8
0.545 0.066 4 1 0 4
0.567 0.261 9 3 2 11
0.491 0.249 2 2 1 3
The formula for aggs, mins, ranks and dups, in R1C1 style, is:
=ROUND(AVERAGE(ArrayA R),3)
=MIN(ArrayA R)
=RANK(aggs,aggs,1)
=COUNTIF(ranks,ranks)
Into the first cell of corr, enter this array formula with
Cntrl+Shift+Enter:
=dups R-MATCH(mins R,LARGE((dups=dups R)*(ranks=ranks
R)*mins,{1;2;3}),0)
then copy it down with the fill handle.
The formula for rank_real is
=ranks+corr
For ties greater than 3, increase the {1;2;3} array.
Uncheck R1C1 to translate into A1 style.

[chappo555]

Thanks for the help. However none of these appear to work. I have added a basic idea of what my data looks like and what results I get with the XL RANK function.

The rank(countif(if etc function that domenic sent results in a #n/a result.

The macro came up with a run error and Im obviously too stupid to understand the r1c1 reply thingy.

sorry but I still need help. RANK only needs to be split when they result in a tie in AGG column (col G). they are split by the MIN(b2:e2) of each competitor. I was trying a RANK(IF(MIN style but without success.

any help greatly appreciated.


try rank forms.zip

[Herbert Seidenberg]

Scoops macro works great for me...
chappo555 wrote:
> The macro came up with a run error and Im obviously too stupid to...

[Scoops]

chappo555 wrote:
> The macro came up with a run error ...

Hi chappo555

The macro works fine for me using the sample data and layout in your
original post. I can't get at your file to try the macro so can you
tell me where it's stopping?

Regards

Steve

[Dav]

If you are measuring values to a 1000th of an inch, the average can only be to a 10000th of an inch (as you have 5 columns) Assuming they are always populated.
If you create a new column, which you can hide later of Agg+min(A:E)/100000

eg G1+min(b1:f1)/100000

then rank this column does that meet your requirements.

What happens if both the minimums are the same?

Regards

Dav

[Bob Phillips]

I was working on a solution along the lines of Domenic's solution when he
(Domenic) posted his response. It works fine with the data as you presented
it.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"chappo555" <[email protected]> wrote
in message news:[email protected]...
>
> Thanks for the help. However none of these appear to work. I have added
> a basic idea of what my data looks like and what results I get with the
> XL RANK function.
>
> The rank(countif(if etc function that domenic sent results in a #n/a
> result.
>
> The macro came up with a run error and Im obviously too stupid to
> understand the r1c1 reply thingy.
>
> sorry but I still need help. RANK only needs to be split when they
> result in a tie in AGG column (col G). they are split by the MIN(b2:e2)
> of each competitor. I was trying a RANK(IF(MIN style but without
> success.
>
> any help greatly appreciated.
>
>
> 4985
>
>
> +-------------------------------------------------------------------+
> |Filename: try rank forms.zip |
> |Download: http://www.excelforum.com/attachment.php?postid=4985 |
> +-------------------------------------------------------------------+
>
> --
> chappo555
> ------------------------------------------------------------------------
> chappo555's Profile:
http://www.excelforum.com/member.php...o&userid=36060
> View this thread: http://www.excelforum.com/showthread...hreadid=558468
>

[chappo555]

If you are measuring values to a 1000th of an inch, the average can only be to a 10000th of an inch (as you have 5 columns) Assuming they are always populated.
If you create a new column, which you can hide later of Agg+min(A:E)/100000

eg G1+min(b1:f1)/100000

then rank this column does that meet your requirements.

What happens if both the minimums are the same?

Regards

Dav

Dav:
thanks heaps mate. That seem to work fine. I gave it a trial where all results were the same across all 5 columns with the exception of one that I set to only 1/1000th of an inch lower and the rank came out right. If both get the same aggregate (result of the 5 columns summed and divided by 5) AND get the same minimum for one of the 5 columns it goes on to the next minimum for the 5 colums for that person and so we go.

Got an answer for that ??

ONCE AGAIN THANKS HEAPS MATE - REALLY APPRECIATE YOUR HELP.

CHEERS
CHAPPO 555

[Dav]

G1+SMALL(B1:F1,1)/10000+SMALL(B1:F1,2)/10000000+SMALL(B1:F1,3)/10000000000+SMALL(B1:F1,4)/10000000000000

You can not have a 5th values being of use as one of the average or the previous 4 would have had an effect and differentiated between the tie

Regards

Dav

[Domenic]

Did you confirm the formula with CONTROL+SHIFT+ENTER, not just ENTER?
Also, when I change the references according to your sample file to the
following...

=RANK(G2,G$2:G$15,1)+IF(COUNTIF(G$2:G$15,G2)>1,MATCH(MIN(B2:F2),SMALL(IF(
G$2:G$15=G2,SUBTOTAL(5,OFFSET(B$2:F$15,ROW(B$2:F$15)-ROW(B$2),0,1))),ROW(
INDIRECT("1:"&COUNTIF(G$2:G$15,G2)))),0)-1)

....and enter it in H2 and copy down, the formula returns the following
ranking...

2
1
3
6
4
8
7
9
11
5
14
10
12
13

Isn't this what you're looking for?

In article <[email protected]>,
chappo555 <[email protected]>
wrote:

> Thanks for the help. However none of these appear to work. I have added
> a basic idea of what my data looks like and what results I get with the
> XL RANK function.
>
> The rank(countif(if etc function that domenic sent results in a #n/a
> result.
>
> The macro came up with a run error and Im obviously too stupid to
> understand the r1c1 reply thingy.
>
> sorry but I still need help. RANK only needs to be split when they
> result in a tie in AGG column (col G). they are split by the MIN(b2:e2)
> of each competitor. I was trying a RANK(IF(MIN style but without
> success.
>
> any help greatly appreciated.
>
>
> 4985
>
>
> +-------------------------------------------------------------------+
> |Filename: try rank forms.zip |
> |Download: http://www.excelforum.com/attachment.php?postid=4985 |
> +-------------------------------------------------------------------+