Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Nested RANK functions
I have a sales ladder which ranks my people by the % difference between budget and sales to date, but not everyone has made a sale yet and the % difference is zero for 6 people. These 6 people all have a rank of 75. Where this happens, I want to then rank only those 6 people, based on their budget figure. Is this possible? My current formula reads: =IF(F83=0,75,RANK($G83,$G$9:$G$83,0)) where F is the sales value and G is the % difference. Budget figures are in column D. Thanks in anticipation! :) -- tuph ------------------------------------------------------------------------ tuph's Profile: http://www.excelforum.com/member.php...o&userid=31390 View this thread: http://www.excelforum.com/showthread...hreadid=569284 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Nested RANK functions
Hi!
Try this: =IF(F9=0,SUMPRODUCT(--($F$9:$F$83=0),--(D9<$D$9:$D$83))+1,"") Biff "tuph" wrote in message ... I have a sales ladder which ranks my people by the % difference between budget and sales to date, but not everyone has made a sale yet and the % difference is zero for 6 people. These 6 people all have a rank of 75. Where this happens, I want to then rank only those 6 people, based on their budget figure. Is this possible? My current formula reads: =IF(F83=0,75,RANK($G83,$G$9:$G$83,0)) where F is the sales value and G is the % difference. Budget figures are in column D. Thanks in anticipation! :) -- tuph ------------------------------------------------------------------------ tuph's Profile: http://www.excelforum.com/member.php...o&userid=31390 View this thread: http://www.excelforum.com/showthread...hreadid=569284 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Nested RANK functions
Thanks, Biff. I can see how your formula works, but it results in rankings between 1 and 6. I probably wasn't clear enough in my original explanation - I need these people to be at the bottom of the list, ranked in order of their budgets if they have not yet made any sales. In other words, they would have a rank of 70-75 (75 being the total number of rows in the RANK array), instead of all having the same rank of 70, which my RANK formula calculates. -- tuph ------------------------------------------------------------------------ tuph's Profile: http://www.excelforum.com/member.php...o&userid=31390 View this thread: http://www.excelforum.com/showthread...hreadid=569284 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Nested RANK functions
If I understand, you can just change the +1 to +70.
=IF(F9=0,SUMPRODUCT(--($F$9:$F$83=0),--(D9<$D$9:$D$83))+70,"") Biff "tuph" wrote in message ... Thanks, Biff. I can see how your formula works, but it results in rankings between 1 and 6. I probably wasn't clear enough in my original explanation - I need these people to be at the bottom of the list, ranked in order of their budgets if they have not yet made any sales. In other words, they would have a rank of 70-75 (75 being the total number of rows in the RANK array), instead of all having the same rank of 70, which my RANK formula calculates. -- tuph ------------------------------------------------------------------------ tuph's Profile: http://www.excelforum.com/member.php...o&userid=31390 View this thread: http://www.excelforum.com/showthread...hreadid=569284 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Nested RANK functions
Hmmm - could do, but then it wouldn't be dynamic and change the ranking of the remaining people when one of them makes a sale. -- tuph ------------------------------------------------------------------------ tuph's Profile: http://www.excelforum.com/member.php...o&userid=31390 View this thread: http://www.excelforum.com/showthread...hreadid=569284 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Nested RANK functions
Ok, so you want those ranks to correlate to the other ranks?
So if the lowest non-zero rank is 50 you then want the "zeros" to start from that point and be unique? What about duplicate ranks for the others? That's OK but it's not OK for people with no sale? Biff "tuph" wrote in message ... Hmmm - could do, but then it wouldn't be dynamic and change the ranking of the remaining people when one of them makes a sale. -- tuph ------------------------------------------------------------------------ tuph's Profile: http://www.excelforum.com/member.php...o&userid=31390 View this thread: http://www.excelforum.com/showthread...hreadid=569284 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Nested RANK functions
"tuph" skrev i en
meddelelse ... Thanks, Biff. I can see how your formula works, but it results in rankings between 1 and 6. I probably wasn't clear enough in my original explanation - I need these people to be at the bottom of the list, ranked in order of their budgets if they have not yet made any sales. In other words, they would have a rank of 70-75 (75 being the total number of rows in the RANK array), instead of all having the same rank of 70, which my RANK formula calculates. -- tuph ------------------------------------------------------------------------ Hi tuph It's not clear to me, if the zeros are ranked with highest budget at the top or at the bottom. Here are 2 suggestions: Highest budget at top: =IF(G2<0,RANK(G2,$G$2:$G$11),RANK(G2,$G$2:$G$11)+ MATCH(D2,LARGE(($G$2:$G$11=0)* $D$2:$D$11,ROW(INDIRECT("1:"&COUNTIF($G$2:$G$11,0) ))),0)-1) Highest budget at bottom: =IF(G2<0,RANK(G2,$G$2:$G$11),RANK(G2,$G$2:$G$11)+ MATCH(D2,SMALL(($G$2:$G$11=0)* $D$2:$D$11,ROW(INDIRECT("1:"&COUNTIF($G$2:$G$11,0) ))+COUNTIF($G$2:$G$11,"<"&0)),0)-1) Both formulae are array formulae and must be entered with <Shift<Ctrl<Enter, also if edited later. Still it might be possible that 2 or more of the zeros have the same budget and therefore ranked with the same number. -- Best regards Leo Heuser Followup to newsgroup only please. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Nested RANK functions
Correct. Ideally I would like to rank the other duplicates, also, but this isn't as important. -- tuph ------------------------------------------------------------------------ tuph's Profile: http://www.excelforum.com/member.php...o&userid=31390 View this thread: http://www.excelforum.com/showthread...hreadid=569284 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Nested RANK functions
Not sure if this reply is to me or Leo?
If you want a unique consecutive rank that "breaks ties": =RANK(G9,G$9:G$83)+COUNTIF(G$9:G9,G9)-1 The first tie will have a higher rank: 20 = 1 19 = 3 20 = 2 15 = 4 0 = 5 0 = 6 Biff "tuph" wrote in message ... Correct. Ideally I would like to rank the other duplicates, also, but this isn't as important. -- tuph ------------------------------------------------------------------------ tuph's Profile: http://www.excelforum.com/member.php...o&userid=31390 View this thread: http://www.excelforum.com/showthread...hreadid=569284 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Nested RANK functions
"tuph" skrev i en
meddelelse ... Correct. Ideally I would like to rank the other duplicates, also, but this isn't as important. -- tuph I guess, that the above wasn't an answer to my "question"? Anyway, try one of these two array formulae instead of the ones I supplied: Highest budget at top: =RANK(G2,$G$2:$G$11)+IF(G2=0,MATCH(D2,LARGE(($G$2: $G$11=0)* $D$2:$D$11,ROW(INDIRECT("1:"&COUNTIF($G$2:$G$11,0) ))),0)-1,COUNTIF($G$2:G2,G2)-1) Highest budget at bottom: =RANK(G2,$G$2:$G$11)+IF(G2=0,MATCH(D2,SMALL(($G$2: $G$11=0)* $D$2:$D$11,ROW(INDIRECT("1:"&COUNTIF($G$2:$G$11,0) ))+COUNTIF($G$2:$G$11,"<"&0)),0)-1,COUNTIF($G$2:G2,G2)-1) Both to be entered with <Shift<Ctrl<Enter, also if edited later. -- Best regards Leo Heuser Followup to newsgroup only please. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Nested RANK functions
Thanks, guys, for taking the trouble to give me such detailed answers. I haven't had a chance to test them out, but hope to do so in the next few days. Cheers!:) Trish -- tuph ------------------------------------------------------------------------ tuph's Profile: http://www.excelforum.com/member.php...o&userid=31390 View this thread: http://www.excelforum.com/showthread...hreadid=569284 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Nested RANK functions
You're welcome, Trish.
Leo Heuser "tuph" skrev i en meddelelse ... Thanks, guys, for taking the trouble to give me such detailed answers. I haven't had a chance to test them out, but hope to do so in the next few days. Cheers!:) Trish |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there any way around the maximum of seven nested functions? | Excel Worksheet Functions | |||
Combining SUMPRODUCT and RANK functions | Excel Worksheet Functions | |||
Nested IF's with Rank Problem | Excel Worksheet Functions | |||
how do I use multiple nested functions? | Excel Worksheet Functions | |||
ENTER EXCEL FORMULA WITH MORE THAN 7 NESTED FUNCTIONS | Excel Worksheet Functions |