|
IF with Rank
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 |
IF with Rank
On Oct 30, 3:21 pm, "Gary" wrote:
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 |
IF with Rank
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. "Pierre" wrote in message ups.com... On Oct 30, 3:21 pm, "Gary" wrote: 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 |
IF with Rank
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? On Oct 30, 5:11 pm, "Gary" wrote: 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. "Pierre" wrote in message ups.com... On Oct 30, 3:21 pm, "Gary" wrote: 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- Hide quoted text - - Show quoted text - |
IF with Rank
Ilia,
Remarkable. Thanks a ton. "ilia" wrote in message oups.com... 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? On Oct 30, 5:11 pm, "Gary" wrote: 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. "Pierre" wrote in message ups.com... On Oct 30, 3:21 pm, "Gary" wrote: 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- Hide quoted text - - Show quoted text - |
IF with Rank
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. On Oct 30, 7:31 pm, "Gary" wrote: Ilia, Remarkable. Thanks a ton. "ilia" wrote in message oups.com... 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? On Oct 30, 5:11 pm, "Gary" wrote: 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. "Pierre" wrote in message roups.com... On Oct 30, 3:21 pm, "Gary" wrote: 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- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| All times are GMT +1. The time now is 07:40 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com