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!

Hi!

Try this:

=IF(F9=0,SUMPRODUCT(--($F$9:$F$83=0),--(D9<$D$9:$D$83))+1,"")

Biff

"tuph" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

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.

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

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.

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

"tuph" <[email protected]> skrev i en
meddelelse news:[email protected]...
>
> 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.

Correct. Ideally I would like to rank the other duplicates, also, but this isn't as important.

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

"tuph" <[email protected]> skrev i en
meddelelse news:[email protected]...
>
> 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.

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

You're welcome, Trish.

Leo Heuser



"tuph" <[email protected]> skrev i en
meddelelse news:[email protected]...
>
> 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
>