IF with Rank

G

Gary

I need this for the incentive sheet.

here are the columns. I need the formula in the %age column.

Names Sales(20%weightage) %age




As we enter sales in the sales column, I want the %age column to calculate
the incentive based on these conditions.

Suppose we have data in 20 rows. Names and sales. Top 2 (10% of 20) will get
full 20% incentive, Next 6 (30% of 20) will get (.6*.2), Next 10 (50% of 20)
will get (.3*.2) and bottom 2 (10% of 20) will get 0.

20 is just an example. It could be any number. If its 18, the top 10% would
still be 2. If its 14, the top 10% should be 1.

Let me know if I need to explain more.

Thanks
 
P

Pierre

I need this for the incentive sheet.

here are the columns. I need the formula in the %age column.

Names Sales(20%weightage) %age

As we enter sales in the sales column, I want the %age column to calculate
the incentive based on these conditions.

Suppose we have data in 20 rows. Names and sales. Top 2 (10% of 20) will get
full 20% incentive, Next 6 (30% of 20) will get (.6*.2), Next 10 (50% of 20)
will get (.3*.2) and bottom 2 (10% of 20) will get 0.

20 is just an example. It could be any number. If its 18, the top 10% would
still be 2. If its 14, the top 10% should be 1.

Let me know if I need to explain more.

Thanks

Gary:
If the top 10% is 2 if there's 20, unless it's 14 people which would
make it 1 or 10%, what is driving the change? Please define your
thresholds.
Pierre
 
G

Gary

I want it to round off. If the total number is 15 or above, 10% should be 2.
If its less than 15, 10% should be 1.
 
I

ilia

Assuming your sales are in column C, this will return the percentage:

=0.2*IF((RANK(C2,$C$2:$C$20))<=(ROUND(0.1*COUNTA($C$2:$C$20),0)),
1,IF((RANK(C2,$C$2:$C$20))<=(ROUND(0.3*COUNTA($C$2:$C$20),0)+
(ROUND(0.1*COUNTA($C$2:$C$20),0))),0.6,IF((RANK(C2,$C$2:$C
$20))<=(ROUND(0.5*COUNTA($C$2:$C$20),0)+(ROUND(0.3*COUNTA($C$2:$C$20),
0)+(ROUND(0.1*COUNTA($C$2:$C$20),0)))),0.3)))

Does that work?
 
G

Gary

Ilia,

Remarkable.

Thanks a ton.

ilia said:
Assuming your sales are in column C, this will return the percentage:

=0.2*IF((RANK(C2,$C$2:$C$20))<=(ROUND(0.1*COUNTA($C$2:$C$20),0)),
1,IF((RANK(C2,$C$2:$C$20))<=(ROUND(0.3*COUNTA($C$2:$C$20),0)+
(ROUND(0.1*COUNTA($C$2:$C$20),0))),0.6,IF((RANK(C2,$C$2:$C
$20))<=(ROUND(0.5*COUNTA($C$2:$C$20),0)+(ROUND(0.3*COUNTA($C$2:$C$20),
0)+(ROUND(0.1*COUNTA($C$2:$C$20),0)))),0.3)))

Does that work?
 
I

ilia

Thanks! I'm glad you like it.

One thing you might consider is putting the percentage values into
separate cells, assigning them names, or doing a combination of the
two - just so it's easier to edit in the future, whatever your final
version is.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top