Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Rank
I am trying to use the following function to rank part of an array based on
one condition, but the function returns the rank for the entire array. Any suggestions? RANK(IF((Calls!$A$1:Calls!$A$1000=Calls!A100),Call s!B100),Calls!$B$1:$B$1000)) Thanks, IP |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Rank
iperlovsky wrote...
I am trying to use the following function to rank part of an array based on one condition, but the function returns the rank for the entire array. *Any suggestions? RANK(IF((Calls!$A$1:Calls!$A$1000=Calls!A100),Cal ls!B100),Calls!$B$1:$B$1000)) I'm guessing you want the rank for Calls!B100 within those cells in Calls!B1:B1000 corresponding to cells in Calls!A1:A1000 equal to Calls! A100. If Excel would allow it, that'd be =RANK(Calls!B100,IF(Calls!$A$1:Calls!$A$1000=Calls !A100,Calls!$B$1:$B $1000)) However, Excel only supports range references as 2nd argument to RANK, not arrays. You'd need to use something like =SUMPRODUCT((Calls!$A$1:Calls!$A$1000=Calls!A100)* (Calls!$B$1:$B $1000=Calls!B100)) if all cells in Calls!B1:B1000 contain numbers. If some could contain text, e.g., contain formulas that could evaluate to "", try =SUMPRODUCT((Calls!$A$1:Calls!$A$1000=Calls!A100)* ISNUMBER(Calls!$B $1:$B$1000)*(Calls!$B$1:$B$1000=Calls!B100)) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Rank
Thanks, that is a clean way of getting there. How would I incorporate a
"correction factor" for tied ranks? "Harlan Grove" wrote: iperlovsky wrote... I am trying to use the following function to rank part of an array based on one condition, but the function returns the rank for the entire array. Any suggestions? RANK(IF((Calls!$A$1:Calls!$A$1000=Calls!A100),Cal ls!B100),Calls!$B$1:$B$1000)) I'm guessing you want the rank for Calls!B100 within those cells in Calls!B1:B1000 corresponding to cells in Calls!A1:A1000 equal to Calls! A100. If Excel would allow it, that'd be =RANK(Calls!B100,IF(Calls!$A$1:Calls!$A$1000=Calls !A100,Calls!$B$1:$B $1000)) However, Excel only supports range references as 2nd argument to RANK, not arrays. You'd need to use something like =SUMPRODUCT((Calls!$A$1:Calls!$A$1000=Calls!A100)* (Calls!$B$1:$B $1000=Calls!B100)) if all cells in Calls!B1:B1000 contain numbers. If some could contain text, e.g., contain formulas that could evaluate to "", try =SUMPRODUCT((Calls!$A$1:Calls!$A$1000=Calls!A100)* ISNUMBER(Calls!$B $1:$B$1000)*(Calls!$B$1:$B$1000=Calls!B100)) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Rank
iperlovsky wrote...
Thanks, that is a clean way of getting there. How would I incorporate a "correction factor" for tied ranks? .... If you don't need to worry about being sued for arbitrary ordering of ties, use their original entry order. If all cells in Calls!B1:B1000 contain numbers, try =SUMPRODUCT((Calls!$A$1:Calls!$A$1000=Calls!A100) *(Calls!$B$1:$B$1000-ROW(Calls!$B$1:$B$1000)/1E9=Calls!B100-ROW(Calls! B100)/1E9)) Adjust the 1E9 (Excel converts this to 1000000000) figure as necessary. It should be large enough so that the row correction term is smaller in absolute value than MIN(Calls!$B$1:$B$1000) but not so large that it would be discarded due to Excel's 15 decimal digit precision. For example, if 1000000000 were one of the values in Calls! B1:B1000 and you try to add 1/1000000000 = 0.000000001 to it, the value would remain 1000000000 since 1000000000.000000001 would require 19 decimal digits. If there could be nonnumeric values in Calls!B1:B1000, you'll need to use an array formula like =SUM((Calls!$A$1:Calls!$A$1000=Calls!A100)*IF(ISNU MBER(Calls!$B$1:$B $1000), Calls!$B$1:$B$1000-ROW(Calls!$B$1:$B$1000)/1E9=Calls!B100-ROW(Calls! B100)/1E9)) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Rank
That works as expected. I had to use the array formula because the data set
contained non-numeric blank cells. Thanks, it really solved my rank issue. "Harlan Grove" wrote: iperlovsky wrote... Thanks, that is a clean way of getting there. How would I incorporate a "correction factor" for tied ranks? .... If you don't need to worry about being sued for arbitrary ordering of ties, use their original entry order. If all cells in Calls!B1:B1000 contain numbers, try =SUMPRODUCT((Calls!$A$1:Calls!$A$1000=Calls!A100) *(Calls!$B$1:$B$1000-ROW(Calls!$B$1:$B$1000)/1E9=Calls!B100-ROW(Calls! B100)/1E9)) Adjust the 1E9 (Excel converts this to 1000000000) figure as necessary. It should be large enough so that the row correction term is smaller in absolute value than MIN(Calls!$B$1:$B$1000) but not so large that it would be discarded due to Excel's 15 decimal digit precision. For example, if 1000000000 were one of the values in Calls! B1:B1000 and you try to add 1/1000000000 = 0.000000001 to it, the value would remain 1000000000 since 1000000000.000000001 would require 19 decimal digits. If there could be nonnumeric values in Calls!B1:B1000, you'll need to use an array formula like =SUM((Calls!$A$1:Calls!$A$1000=Calls!A100)*IF(ISNU MBER(Calls!$B$1:$B $1000), Calls!$B$1:$B$1000-ROW(Calls!$B$1:$B$1000)/1E9=Calls!B100-ROW(Calls! B100)/1E9)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
rank the numbers / range of data using 'RANK' and 'ABS' | Excel Worksheet Functions | |||
Conditional Rank (or rather, Conditional Range) | Excel Worksheet Functions | |||
Conditional formatting with Rank | Excel Discussion (Misc queries) | |||
Conditional Format Rank query | Excel Discussion (Misc queries) | |||
Rank items, select one start date, have remaining dates follow based on rank | Excel Discussion (Misc queries) |