Help with IF Statement

S

Sherry

I think I need to use an "IF" function for the following but don't know how
to write if statements. Can anybody help?

If Cell A is between 3,000 and 5,000 then multiply by 20%

Thanks!!
 
M

Marcelo

Hi Sherry

if(and(a1>3000,a1<5000),a1*1.2,a1)

HTH
Regards From Brazil
Marcelo

"Sherry" escreveu:
 
T

tim m

What do you want to appear if the number is not between those values? (In
Marcelos solution the result of A1 shows up and in Bearacades solution a
blank is put in the cell.)
 
S

Sherry

I don't know. I'm doing this for somebody in our office that's not here.
Actually, they need the formula to be as such:

If A1 is between: $3,000 - 5,000 then 20%; if $5,000 - 10,000 then 17%; if
$10,000 - 20,000 then 15%; if $20,000 - 30,000 then 12%, if $30,000 - 50,000
then 10%; if 50,000 - 80,000 then 8%.

Thanks!!!!
 
S

Sherry

Thanks Bearacade!!

How can I write the statement to contain all scenarios into one forumula?

If Cell A1 is between $3,000 - 5,000 then multiply by 20%; if $5,000 -
10,000 then multiply by 17%; if$10,000 - 20,000 then 15%; if $20,000 - 30,000
then 12%; if $30,000 - 50,000 then 10%; if 50,000 - 80,000 then 8%
 
B

Bearacade

There has been a few great examples in the forum that does this MUC
more effectively, but I have always been doing it the long way, so her
goes:

=IF(AND(A1>=50001, A1<=80000), A1*0.08, IF(AND(A1>=30001, A1<=50000)
A1*0.1, IF(AND(A1>=20001, A1<=30000), A1*0.12, IF(AND(A1>=10001
A1<=20000), A1*0.15, IF(AND(A1>=5001, A1<=10000), A1*0.17
IF(AND(A1>=3000, A1<=5000), A1*0.2, "Out of Range")))))
 
B

BEEJAY

You might find the following a little shorter:

=IF(A10<3000,A10*0,IF(A10<=5000,A10*0.2,IF(A10<=10000,A10*0.17,IF(A10<=20000,A10*0.15,IF(A10<=30000,A10*0.12,IF(A10<=50000,A10*0.1,IF(A10<=80000,A10*0.08,A10*0)))))))

The process will read from left to right, until it finds a "match".
The above statement, if an amount over 80,000, will return a "0" (Zero)
I hope this helps.
 
Top