Ranking multiple columns by 1000th inch
 

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


--
chappo555
------------------------------------------------------------------------
chappo555's Profile: http://www.excelforum.com/member.php...o&userid=36060
View this thread: http://www.excelforum.com/showthread...hreadid=558468

Ranking multiple columns by 1000th inch
 

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

Ranking multiple columns by 1000th inch
 

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 ,
chappo555
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

Ranking multiple columns by 1000th inch
 

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.

Ranking multiple columns by 1000th inch
 

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

Ranking multiple columns by 1000th inch
 

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

Ranking multiple columns by 1000th inch
 

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

Ranking multiple columns by 1000th inch
 

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
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=558468

Ranking multiple columns by 1000th inch
 

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" wrote
in message ...

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

Ranking multiple columns by 1000th inch
 

Dav Wrote:
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


--
chappo555
------------------------------------------------------------------------
chappo555's Profile: http://www.excelforum.com/member.php...o&userid=36060
View this thread: http://www.excelforum.com/showthread...hreadid=558468

Ranking multiple columns by 1000th inch
 

G3+
AVERAGE(B3:F3)+SMALL(B3:F3,1)/10000+SMALL(B3:F3,2)/10000000+SMALL(B3:F3,3)/10000000000+SMALL(B3:F3,4)/10000000000000

You can ot have a 5th values being of use as one of the average or the
previous 4 would have had an effect

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=558468

Ranking multiple columns by 1000th inch
 

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,MAT CH(MIN(B2:F2),SMALL(IF(
G$2:G$15=G2,SUBTOTAL(5,OFFSET(B$2:F$15,ROW(B$2:F$1 5)-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 ,
chappo555
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 |
+-------------------------------------------------------------------+