| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ranking based on conditions
In column A I have unique IDs (10001 up to 19999).
In column B I have countries, In column C I have fruit names, In column D I have values (between 1 and 10000) If I have a unique ID - lets say ID 14592 It's corresponding country is Australia, and corresponding fruit is Pear, and corresponding value is 1000 I want to know its rank in its class. - its rank for all Pears - its rank for all Australians - its rank for all Australian Pears. Can someone help with the formula for this? Say the ID (14592) is entered in F1 Thanks in advance! Daniel |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Ranking based on conditions
Assuming that the fruit name is also in F2
=IF(C1<$F$2,"",MATCH(D1,LARGE(IF($C$1:$C$25="Pear s",$D$1:$D$25),ROW(INDIRECT("1:"&COUNTIF($C$1:$C$2 5,$F$2)))),0)) =IF(A1<$F$1,"",MATCH(D1,LARGE(IF(($A$1:$A$25=$F$1 ),$D$1:$D$25),ROW(INDIRECT("1:"&COUNTIF($A$1:$A$25 ,$F$1)))),0)) =IF(OR(C1<$F$2,A1<$F$1),"",MATCH(D1,LARGE(IF(($C $1:$C$25=$F$2)*($A$1:$A$25=$F$1),$D$1:$D$25),ROW(I NDIRECT("1:"&SUM(($C$1:$C$25=$F$2)*($A$1:$A$25=$F$ 1))))),0)) all array formulae (and not a SUMPRODUCT in sight) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Daniel Bonallack" wrote in message ... In column A I have unique IDs (10001 up to 19999). In column B I have countries, In column C I have fruit names, In column D I have values (between 1 and 10000) If I have a unique ID - lets say ID 14592 It's corresponding country is Australia, and corresponding fruit is Pear, and corresponding value is 1000 I want to know its rank in its class. - its rank for all Pears - its rank for all Australians - its rank for all Australian Pears. Can someone help with the formula for this? Say the ID (14592) is entered in F1 Thanks in advance! Daniel |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Ranking based on conditions
Hi Bob
This returned me with a blank cell - would it be possible to send you my country/fruit example? regards Daniel "Bob Phillips" wrote: Assuming that the fruit name is also in F2 =IF(C1<$F$2,"",MATCH(D1,LARGE(IF($C$1:$C$25="Pear s",$D$1:$D$25),ROW(INDIRECT("1:"&COUNTIF($C$1:$C$2 5,$F$2)))),0)) =IF(A1<$F$1,"",MATCH(D1,LARGE(IF(($A$1:$A$25=$F$1 ),$D$1:$D$25),ROW(INDIRECT("1:"&COUNTIF($A$1:$A$25 ,$F$1)))),0)) =IF(OR(C1<$F$2,A1<$F$1),"",MATCH(D1,LARGE(IF(($C $1:$C$25=$F$2)*($A$1:$A$25=$F$1),$D$1:$D$25),ROW(I NDIRECT("1:"&SUM(($C$1:$C$25=$F$2)*($A$1:$A$25=$F$ 1))))),0)) all array formulae (and not a SUMPRODUCT in sight) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Daniel Bonallack" wrote in message ... In column A I have unique IDs (10001 up to 19999). In column B I have countries, In column C I have fruit names, In column D I have values (between 1 and 10000) If I have a unique ID - lets say ID 14592 It's corresponding country is Australia, and corresponding fruit is Pear, and corresponding value is 1000 I want to know its rank in its class. - its rank for all Pears - its rank for all Australians - its rank for all Australian Pears. Can someone help with the formula for this? Say the ID (14592) is entered in F1 Thanks in advance! Daniel |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Ranking based on conditions
Of course, juts pop it in the post.
Bob "Daniel Bonallack" wrote in message ... Hi Bob This returned me with a blank cell - would it be possible to send you my country/fruit example? regards Daniel "Bob Phillips" wrote: Assuming that the fruit name is also in F2 =IF(C1<$F$2,"",MATCH(D1,LARGE(IF($C$1:$C$25="Pear s",$D$1:$D$25),ROW(INDIRECT("1:"&COUNTIF($C$1:$C$2 5,$F$2)))),0)) =IF(A1<$F$1,"",MATCH(D1,LARGE(IF(($A$1:$A$25=$F$1 ),$D$1:$D$25),ROW(INDIRECT("1:"&COUNTIF($A$1:$A$25 ,$F$1)))),0)) =IF(OR(C1<$F$2,A1<$F$1),"",MATCH(D1,LARGE(IF(($C $1:$C$25=$F$2)*($A$1:$A$25=$F$1),$D$1:$D$25),ROW(I NDIRECT("1:"&SUM(($C$1:$C$25=$F$2)*($A$1:$A$25=$F$ 1))))),0)) all array formulae (and not a SUMPRODUCT in sight) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Daniel Bonallack" wrote in message ... In column A I have unique IDs (10001 up to 19999). In column B I have countries, In column C I have fruit names, In column D I have values (between 1 and 10000) If I have a unique ID - lets say ID 14592 It's corresponding country is Australia, and corresponding fruit is Pear, and corresponding value is 1000 I want to know its rank in its class. - its rank for all Pears - its rank for all Australians - its rank for all Australian Pears. Can someone help with the formula for this? Say the ID (14592) is entered in F1 Thanks in advance! Daniel |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Ranking based on conditions
Daniel,
Please use the gmail account mentioned in my postings, I am having horrific spam problems in my main accounts at the moment. Bob |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Total or Count based on multiple conditions | Excel Discussion (Misc queries) | |||
| Cell values based upon multiple conditions | New Users to Excel | |||
| Add cells from a range based on 2 conditions from 2 other ranges | Excel Worksheet Functions | |||
| How do I calculate sum based on 3 conditions? | Excel Worksheet Functions | |||
| Formula to return cell contents based on multiple conditions | Excel Worksheet Functions | |||
Linear Mode
