IF THEn Formula

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 ?
 
C

CLR

I sure hope that's not a Sales Commssion scale..........

Vaya con Dios,
Chuck, CABGx3
 
M

Matt Richardson

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
 
C

CLR

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
 
S

Sandy Mann

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
 
G

Gord Dibben

One more to ponder.

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


Gord Dibben MS Excel MVP
 
C

CLR

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
 
M

Mc/

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Compute percentage 1
Compute percentages 2
Help with normal distribution, generating ratios for bell-shape cu 5
Excel Macro for Scatter graph 0
Referencing with multiple criteria 2
Logarithmic Trendline 2
vlookup error 3
tricky problem with 14800 rows (collating figures) 1

Top