|
Excel formula
Hello there,
I was wondering if anyone could help me with a formula that will calculate the following: Commission Levels: Target $2500 95% to 100% of Target $1250 For each additional 1% above 100% $50 e.g. if achieved %110 of target, then payout would be $2500 plus 10% (10x$50) for a grand total of $3000 for payout...There is no cap to this payout. I have been racking my brains and have come up with the following formula which works well unless the percentage ends in a zero: =IF(AND(VALUE(E8)=95%),IF(VALUE(E8)<101%,1250,RIG HT(ROUND(VALUE(E8),2),2)*50+2500),0) -- Thanks for your help! |
Excel formula
Try =1250*(E8=95%)+1250*(E8=100%)+50*100*MAX(0,E8-100%)
-- David Biddulph "TamIam" wrote in message ... Hello there, I was wondering if anyone could help me with a formula that will calculate the following: Commission Levels: Target $2500 95% to 100% of Target $1250 For each additional 1% above 100% $50 e.g. if achieved %110 of target, then payout would be $2500 plus 10% (10x$50) for a grand total of $3000 for payout...There is no cap to this payout. I have been racking my brains and have come up with the following formula which works well unless the percentage ends in a zero: =IF(AND(VALUE(E8)=95%),IF(VALUE(E8)<101%,1250,RIG HT(ROUND(VALUE(E8),2),2)*50+2500),0) -- Thanks for your help! |
Excel formula
Maybe:
=(E8=.95)*(1250+Max(0,(e9-1)*100)*50) "TamIam" wrote: Hello there, I was wondering if anyone could help me with a formula that will calculate the following: Commission Levels: Target $2500 95% to 100% of Target $1250 For each additional 1% above 100% $50 e.g. if achieved %110 of target, then payout would be $2500 plus 10% (10x$50) for a grand total of $3000 for payout...There is no cap to this payout. I have been racking my brains and have come up with the following formula which works well unless the percentage ends in a zero: =IF(AND(VALUE(E8)=95%),IF(VALUE(E8)<101%,1250,RIG HT(ROUND(VALUE(E8),2),2)*50+2500),0) -- Thanks for your help! |
Excel formula
By the way, your description and formula are inconsistent. You state:
95% to 100% of Target $1250 but your formula uses $2,500 "TamIam" wrote: Hello there, I was wondering if anyone could help me with a formula that will calculate the following: Commission Levels: Target $2500 95% to 100% of Target $1250 For each additional 1% above 100% $50 e.g. if achieved %110 of target, then payout would be $2500 plus 10% (10x$50) for a grand total of $3000 for payout...There is no cap to this payout. I have been racking my brains and have come up with the following formula which works well unless the percentage ends in a zero: =IF(AND(VALUE(E8)=95%),IF(VALUE(E8)<101%,1250,RIG HT(ROUND(VALUE(E8),2),2)*50+2500),0) -- Thanks for your help! |
Excel formula
Make that
=(E8=.95)*(1250+Max(0,(e8-1)*100)*50) and if you really mean $2,500 instead of $1,250, then =(E8=.95)*(2500+Max(0,(e8-1)*100)*50) "Duke Carey" wrote: Maybe: =(E8=.95)*(1250+Max(0,(e9-1)*100)*50) "TamIam" wrote: Hello there, I was wondering if anyone could help me with a formula that will calculate the following: Commission Levels: Target $2500 95% to 100% of Target $1250 For each additional 1% above 100% $50 e.g. if achieved %110 of target, then payout would be $2500 plus 10% (10x$50) for a grand total of $3000 for payout...There is no cap to this payout. I have been racking my brains and have come up with the following formula which works well unless the percentage ends in a zero: =IF(AND(VALUE(E8)=95%),IF(VALUE(E8)<101%,1250,RIG HT(ROUND(VALUE(E8),2),2)*50+2500),0) -- Thanks for your help! |
Excel formula
This works very well, however, is it possible to add a 'rounding' option say
to a percentage of 1.27300045? I only want to pay the $50 on the 27% over target. Again..many thanks! -- Thanks for your help! "TamIam" wrote: Hello there, I was wondering if anyone could help me with a formula that will calculate the following: Commission Levels: Target $2500 95% to 100% of Target $1250 For each additional 1% above 100% $50 e.g. if achieved %110 of target, then payout would be $2500 plus 10% (10x$50) for a grand total of $3000 for payout...There is no cap to this payout. I have been racking my brains and have come up with the following formula which works well unless the percentage ends in a zero: =IF(AND(VALUE(E8)=95%),IF(VALUE(E8)<101%,1250,RIG HT(ROUND(VALUE(E8),2),2)*50+2500),0) -- Thanks for your help! |
Excel formula
=(E8=.95)*(1250+Max(0,(min(e8,1.27)-1)*100)*50)
"TamIam" wrote: This works very well, however, is it possible to add a 'rounding' option say to a percentage of 1.27300045? I only want to pay the $50 on the 27% over target. Again..many thanks! -- Thanks for your help! "TamIam" wrote: Hello there, I was wondering if anyone could help me with a formula that will calculate the following: Commission Levels: Target $2500 95% to 100% of Target $1250 For each additional 1% above 100% $50 e.g. if achieved %110 of target, then payout would be $2500 plus 10% (10x$50) for a grand total of $3000 for payout...There is no cap to this payout. I have been racking my brains and have come up with the following formula which works well unless the percentage ends in a zero: =IF(AND(VALUE(E8)=95%),IF(VALUE(E8)<101%,1250,RIG HT(ROUND(VALUE(E8),2),2)*50+2500),0) -- Thanks for your help! |
Excel formula
Hi Duke
I wonder if the OP meant a rounding of the % to decimal places, rather than the specific 1.27? If so maybe =(E8=0.95)*(1250+MAX(0,(ROUND(E8*100,0)/100-1)*100)*50) -- Regards Roger Govier "Duke Carey" wrote in message ... =(E8=.95)*(1250+Max(0,(min(e8,1.27)-1)*100)*50) "TamIam" wrote: This works very well, however, is it possible to add a 'rounding' option say to a percentage of 1.27300045? I only want to pay the $50 on the 27% over target. Again..many thanks! -- Thanks for your help! "TamIam" wrote: Hello there, I was wondering if anyone could help me with a formula that will calculate the following: Commission Levels: Target $2500 95% to 100% of Target $1250 For each additional 1% above 100% $50 e.g. if achieved %110 of target, then payout would be $2500 plus 10% (10x$50) for a grand total of $3000 for payout...There is no cap to this payout. I have been racking my brains and have come up with the following formula which works well unless the percentage ends in a zero: =IF(AND(VALUE(E8)=95%),IF(VALUE(E8)<101%,1250,RIG HT(ROUND(VALUE(E8),2),2)*50+2500),0) -- Thanks for your help! |
Excel formula
rounding of the % to decimal places
That should of course have read rounding of the % to 2 decimal places -- Regards Roger Govier "Roger Govier" wrote in message ... Hi Duke I wonder if the OP meant a rounding of the % to decimal places, rather than the specific 1.27? If so maybe =(E8=0.95)*(1250+MAX(0,(ROUND(E8*100,0)/100-1)*100)*50) -- Regards Roger Govier "Duke Carey" wrote in message ... =(E8=.95)*(1250+Max(0,(min(e8,1.27)-1)*100)*50) "TamIam" wrote: This works very well, however, is it possible to add a 'rounding' option say to a percentage of 1.27300045? I only want to pay the $50 on the 27% over target. Again..many thanks! -- Thanks for your help! "TamIam" wrote: Hello there, I was wondering if anyone could help me with a formula that will calculate the following: Commission Levels: Target $2500 95% to 100% of Target $1250 For each additional 1% above 100% $50 e.g. if achieved %110 of target, then payout would be $2500 plus 10% (10x$50) for a grand total of $3000 for payout...There is no cap to this payout. I have been racking my brains and have come up with the following formula which works well unless the percentage ends in a zero: =IF(AND(VALUE(E8)=95%),IF(VALUE(E8)<101%,1250,RIG HT(ROUND(VALUE(E8),2),2)*50+2500),0) -- Thanks for your help! |
| All times are GMT +1. The time now is 09:55 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com