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

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

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

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

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

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

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.

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

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

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.

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

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