IF with Rank

[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

 

[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

 

[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.

 

[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?

 

[Gary]

Ilia,

Remarkable.

Thanks a ton.

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?

 

[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.