|
Correct way to =IF(B6<7000,B6*0.45)=IF(B6<97000,B6*0.5) ...etc
I am trying to create a formula that will calculate commissions depending on
bechmarks. Example: IF <7000 then multiply by 45% IF 7000, but <9000 then multiply by 50% IF 9000, but <13000 then multiply by 55% The final part of the formula should be as follows: If 13000, but <16000 then multiply by 60%, IF 16000, 100% of the amount 16000 is multiplyed by 100% |
Correct way to =IF(B6<7000,B6*0.45)=IF(B6<97000,B6*0.5) ...etc
This ought to do the trick:
=IF(B6<7000,B6*0.45,IF(B6<9000,B6*0.5,IF(B6<13000, B6*0.55,IF(B6<16000,B6*0.6,B6)))) "cbrock" wrote: I am trying to create a formula that will calculate commissions depending on bechmarks. Example: IF <7000 then multiply by 45% IF 7000, but <9000 then multiply by 50% IF 9000, but <13000 then multiply by 55% The final part of the formula should be as follows: If 13000, but <16000 then multiply by 60%, IF 16000, 100% of the amount 16000 is multiplyed by 100% |
Correct way to =IF(B6<7000,B6*0.45)=IF(B6<97000,B6*0.5) ...etc
Hi
=IF(A116000,A1,(A10)*45%+(A17000)*5%+(A19000)* 5%+(A113000)*5%) Regards Roger Govier cbrock wrote: I am trying to create a formula that will calculate commissions depending on bechmarks. Example: IF <7000 then multiply by 45% IF 7000, but <9000 then multiply by 50% IF 9000, but <13000 then multiply by 55% The final part of the formula should be as follows: If 13000, but <16000 then multiply by 60%, IF 16000, 100% of the amount 16000 is multiplyed by 100% |
Correct way to =IF(B6<7000,B6*0.45)=IF(B6<97000,B6*0.5) ...et
Great thanks so much. One last thing, if B6 is 16000 it should multiply
16000 *.60 then add any amount over <16000. Example: IF B6 = 17500 Then the formula should do this (16000 *.60 + 17500-16000 = 11,100.00 In other words any commission generated 16000 the agent will keep plus recieve 60% on the amount between 13000 & 16000 Corey "Elkar" wrote: This ought to do the trick: =IF(B6<7000,B6*0.45,IF(B6<9000,B6*0.5,IF(B6<13000, B6*0.55,IF(B6<16000,B6*0.6,B6)))) "cbrock" wrote: I am trying to create a formula that will calculate commissions depending on bechmarks. Example: IF <7000 then multiply by 45% IF 7000, but <9000 then multiply by 50% IF 9000, but <13000 then multiply by 55% The final part of the formula should be as follows: If 13000, but <16000 then multiply by 60%, IF 16000, 100% of the amount 16000 is multiplyed by 100% |
Correct way to =IF(B6<7000,B6*0.45)=IF(B6<97000,B6*0.5) ...et
how does that continue with this:
=IF(B6<7000,B6*0.45,IF(B6<9000,B6*0.5,IF(B6<13000, B6*0.55,IF(B6<16000,B6*0.6,B6)))) right now any number 16000 is simply multiplied by 100% Corey "Roger Govier" wrote: Hi =IF(A116000,A1,(A10)*45%+(A17000)*5%+(A19000)* 5%+(A113000)*5%) Regards Roger Govier cbrock wrote: I am trying to create a formula that will calculate commissions depending on bechmarks. Example: IF <7000 then multiply by 45% IF 7000, but <9000 then multiply by 50% IF 9000, but <13000 then multiply by 55% The final part of the formula should be as follows: If 13000, but <16000 then multiply by 60%, IF 16000, 100% of the amount 16000 is multiplyed by 100% |
Correct way to =IF(B6<7000,B6*0.45)=IF(B6<97000,B6*0.5) ...et
Ok, I didn't quite understand that last part in your original post. Try this:
=IF(B6<7000,B6*0.45,IF(B6<9000,B6*0.5,IF(B6<13000, B6*0.55,IF(B6<16000,B6*0.6,9600+(B6-16000))))) "cbrock" wrote: Great thanks so much. One last thing, if B6 is 16000 it should multiply 16000 *.60 then add any amount over <16000. Example: IF B6 = 17500 Then the formula should do this (16000 *.60 + 17500-16000 = 11,100.00 In other words any commission generated 16000 the agent will keep plus recieve 60% on the amount between 13000 & 16000 Corey "Elkar" wrote: This ought to do the trick: =IF(B6<7000,B6*0.45,IF(B6<9000,B6*0.5,IF(B6<13000, B6*0.55,IF(B6<16000,B6*0.6,B6)))) "cbrock" wrote: I am trying to create a formula that will calculate commissions depending on bechmarks. Example: IF <7000 then multiply by 45% IF 7000, but <9000 then multiply by 50% IF 9000, but <13000 then multiply by 55% The final part of the formula should be as follows: If 13000, but <16000 then multiply by 60%, IF 16000, 100% of the amount 16000 is multiplyed by 100% |
Correct way to =IF(B6<7000,B6*0.45)=IF(B6<97000,B6*0.5) ...etc
Hi
Your original posting said IF 16000, 100% of the amount 16000 is multiplyed by 100% You have since said that if amount 16000, then 60% of amount up to 16000, plus all of excess over 16000. If so then change formula to =(A10)*45%+(A17000)*5%+(A19000)*5%+(A113000)*5 %+MAX(0,A1-16000) Roger Govier Roger Govier wrote: Hi =IF(A116000,A1,(A10)*45%+(A17000)*5%+(A19000)* 5%+(A113000)*5%) Regards Roger Govier cbrock wrote: I am trying to create a formula that will calculate commissions depending on bechmarks. Example: IF <7000 then multiply by 45% IF 7000, but <9000 then multiply by 50% IF 9000, but <13000 then multiply by 55% The final part of the formula should be as follows: If 13000, but <16000 then multiply by 60%, IF 16000, 100% of the amount 16000 is multiplyed by 100% |
Correct way to =IF(B6<7000,B6*0.45)=IF(B6<97000,B6*0.5) ...et
You've been a big help thanks!
Corey "Elkar" wrote: Ok, I didn't quite understand that last part in your original post. Try this: =IF(B6<7000,B6*0.45,IF(B6<9000,B6*0.5,IF(B6<13000, B6*0.55,IF(B6<16000,B6*0.6,9600+(B6-16000))))) "cbrock" wrote: Great thanks so much. One last thing, if B6 is 16000 it should multiply 16000 *.60 then add any amount over <16000. Example: IF B6 = 17500 Then the formula should do this (16000 *.60 + 17500-16000 = 11,100.00 In other words any commission generated 16000 the agent will keep plus recieve 60% on the amount between 13000 & 16000 Corey "Elkar" wrote: This ought to do the trick: =IF(B6<7000,B6*0.45,IF(B6<9000,B6*0.5,IF(B6<13000, B6*0.55,IF(B6<16000,B6*0.6,B6)))) "cbrock" wrote: I am trying to create a formula that will calculate commissions depending on bechmarks. Example: IF <7000 then multiply by 45% IF 7000, but <9000 then multiply by 50% IF 9000, but <13000 then multiply by 55% The final part of the formula should be as follows: If 13000, but <16000 then multiply by 60%, IF 16000, 100% of the amount 16000 is multiplyed by 100% |
Correct way to =IF(B6<7000,B6*0.45)=IF(B6<97000,B6*0.5) ...etc
Couldn't rest until I thought about what was wrong with my posting.
I missed out the vital part of multiplying by the original sum Try =((A10)*45%+(A17000)*5%+(A19000)*5%+(A113000)* 5%)*MIN(A1,16000)+MAX(0,A1-16000) Regards Roger Govier Roger Govier wrote: Sorry, half asleep ignore all of my postings Off to bed!!! Regards Roger Roger Govier Technology 4 U Tel 01873 880266 Mob 07970 786191 Roger Govier wrote: Hi Your original posting said IF 16000, 100% of the amount 16000 is multiplyed by 100% You have since said that if amount 16000, then 60% of amount up to 16000, plus all of excess over 16000. If so then change formula to =(A10)*45%+(A17000)*5%+(A19000)*5%+(A113000)*5 %+MAX(0,A1-16000) Roger Govier Roger Govier wrote: Hi =IF(A116000,A1,(A10)*45%+(A17000)*5%+(A19000)* 5%+(A113000)*5%) Regards Roger Govier cbrock wrote: I am trying to create a formula that will calculate commissions depending on bechmarks. Example: IF <7000 then multiply by 45% IF 7000, but <9000 then multiply by 50% IF 9000, but <13000 then multiply by 55% The final part of the formula should be as follows: If 13000, but <16000 then multiply by 60%, IF 16000, 100% of the amount 16000 is multiplyed by 100% |
| All times are GMT +1. The time now is 09:41 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com