Using Nested RANK functions

[tuph]

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!

 

[Biff]

Hi!

Try this:

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

Biff

 

[tuph]

Thanks, Biff.

I can see how your formula works, but it results in rankings between
and 6.

I probably wasn't clear enough in my original explanation - I nee
these people to be at the bottom of the list, ranked in order of thei
budgets if they have not yet made any sales. In other words, they woul
have a rank of 70-75 (75 being the total number of rows in the RAN
array), instead of all having the same rank of 70, which my RAN
formula calculates

 

[Biff]

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]

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.

 

[Biff]

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

 

[Leo Heuser]

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.

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.

 

[tuph]

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

 

[Biff]

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

 

[Leo Heuser]

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

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.

 

[tuph]

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 nex
few days.

Cheers!

Tris

 

[Leo Heuser]

You're welcome, Trish.

Leo Heuser