IF THEn Formula

  • Thread starter Thread starter clemente821
  • Start date Start date
C

clemente821

I need the formula to multiply by 3 diferent rates base on the following
assumptions if a<10,000,a*0.08,if a>10001,a*0.06, if a>20001,a*0.04. can
anyone help ?
 
I need the formula to multiply by 3 diferent rates base on the following
assumptions if a<10,000,a*0.08,if a>10001,a*0.06, if a>20001,a*0.04. can
anyone help ?
Click to expand...

=IF(A1<10000, A1*0.08,IF(A1>20001, A1*0.04,IF(A1>10001,A1*0.06)))

And that should do the trick.

Hope this helps,
Matt
http://teachr.blogspot.com
 
You may wish to consider something like this..........


=IF(A1>20000,10000*0.08+10000*0.06+0.08*(A1-20000),IF(A1>10000,10000*0.08+0.06*(A1-10000),0.08*A1))

This way, the more the person sells, the more money they make.

Vaya con Dios,
Chuck, CABGx3
 
I think that Chuck may have been alluding to the fact that with your figures
as salesman gets 800 commission for 10,000 but gets LESS than that for all
values up to 13,333! Similarly he gets 1,200 commission for 20,000 but
needs to to more than 30,000 before he will get that much again.

From the information you gave it is not clear what you want for exactly
10,000. I am going to assume that you meant <=10,000 adjust the formula if
not:

=A1*(4-(A1>10000)-(A1>20000))*0.02


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
One more to ponder.

=LOOKUP(A1,{0,10002,20002},{0.08,0.06,0.04})*A1


Gord Dibben MS Excel MVP
 
Hmmmm...........typeo here's correction........=IF(A1>20000,10000*0.08+10000*0.06+0.04*(A1-20000),IF(A1>10000,10000*0.08+0.
06*(A1-10000),0.08*A1))

Vaya con Dios,
Chuck, CABGx3
 
if a<10,000,a*0.08,if a>10001,a*0.06, if a>20001,a*0.04
You almost have the formula correct as you presented the issue.

You must keep in mind that the IF/THEN/ELSE function will stop considering
conditions, when the first TRUE path has been determined.

I also use the >= and <= conditional operative.

=if(A1>=20,000,A1*0.04,(A1<=10000,A1*0.08, A1*0.06))
-OR-
=if(A1<=10,000,A1*0.08,(A1>=20000,A1*0.04, A1*0.06))
However, I like the format of showing the percentage rather than the decimal
equivalent as follows;

=if(A1<=10,000,A1*8%,(A1>=20000,A1*4%, A1*6%))
 
Back
Top