Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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... |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 | +-------------------------------------------------------------------+ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
making multiple columns when printing long datasheet | Excel Discussion (Misc queries) | |||
Justify text across multiple columns | Excel Discussion (Misc queries) | |||
Formula to delete blank cells across multiple columns? | Excel Worksheet Functions | |||
Convert 1 row of data into Multiple columns | Excel Discussion (Misc queries) | |||
Drop-down selection fills data across multiple columns | Excel Discussion (Misc queries) |