| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel formula
Can you give 2-3 examples of the correct answer for the amount given.
-- Don Guillett SalesAid Software "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! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| formula structure building ? check under the excel forum.... | Excel Discussion (Misc queries) | |||
| Excel Formula Issue | Excel Discussion (Misc queries) | |||
| Match then lookup | Excel Worksheet Functions | |||
| converting formula from lotus.123 to excel | Excel Worksheet Functions | |||
| How do I view the actual numeric value of a formula in Excel 2002. | Excel Worksheet Functions | |||
Linear Mode
