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!!

Hi Sherry

if(and(a13000,a1<5000),a1*1.2,a1)

HTH
Regards From Brazil
Marcelo

"Sherry" escreveu:

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!!

=if(and(a1=3000, A1<=5000), A1*0.2, "")

Hth


--
Bearacade


------------------------------------------------------------------------
Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
View this thread: http://www.excelforum.com/showthread...hreadid=554608

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%


"Bearacade" wrote:


=if(and(a1=3000, A1<=5000), A1*0.2, "")

Hth


--
Bearacade


------------------------------------------------------------------------
Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
View this thread: http://www.excelforum.com/showthread...hreadid=554608

There has been a few great examples in the forum that does this MUCH
more effectively, but I have always been doing it the long way, so here
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"))))))


--
Bearacade


------------------------------------------------------------------------
Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
View this thread: http://www.excelforum.com/showthread...hreadid=554608

It worked beautifully. Thank you so much!!

"Bearacade" wrote:


There has been a few great examples in the forum that does this MUCH
more effectively, but I have always been doing it the long way, so here
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"))))))


--
Bearacade


------------------------------------------------------------------------
Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
View this thread: http://www.excelforum.com/showthread...hreadid=554608

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

"Sherry" wrote:

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!!

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!!!!

"tim m" wrote:

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

"Sherry" wrote:

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!!

You might find the following a little shorter:

=IF(A10<3000,A10*0,IF(A10<=5000,A10*0.2,IF(A10<=10 000,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.

"Sherry" wrote:

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!!!!

"tim m" wrote:

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

"Sherry" wrote:

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!!