Correct way to =IF(B6<7000,B6*0.45)=IF(B6<97000,B6*0.5) ...etc

C

cbrock

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:
 
E

Elkar

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))))
 
R

Roger Govier

Hi

=IF(A1>16000,A1,(A1>0)*45%+(A1>7000)*5%+(A1>9000)*5%+(A1>13000)*5%)

Regards

Roger Govier
 
C

cbrock

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
 
C

cbrock

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
 
E

Elkar

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)))))
 
R

Roger Govier

Hi

Your original posting said
IF >16000, 100% of the amount 16000 is multiplyed by 100%
Click to expand...

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

=(A1>0)*45%+(A1>7000)*5%+(A1>9000)*5%+(A1>13000)*5%+MAX(0,A1-16000)



Roger Govier
 
C

cbrock

You've been a big help thanks!

Corey

Elkar said:
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)))))
Click to expand...
 
R

Roger Govier

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
=((A1>0)*45%+(A1>7000)*5%+(A1>9000)*5%+(A1>13000)*5%)*MIN(A1,16000)+MAX(0,A1-16000)


Regards

Roger Govier
 
Top