IF Function

[joaniemic]

I would like one of four discount percentages to appear on a spreadsheet,
depending on what the dollar amount is in A1...
If A1 is equal to or greater than $600 and less than or equal to $1,500 the
discount is 5%
If A1 is equal to or greater than $1,501 and less than or equal to $3,000
the discount is 10%
If A1 is equal to or greater than $3,001 and less than or equal to $6,000
the discount is 15%
If A1 is equal to or greater than $6,001 and less than or equal to $15,000
the discount is 20%
Thank you.

 

[Bob Umlas, Excel MVP]

=IF(AND(A1>=600,A1<=1500),5%,IF(AND(A1>=1501,A1<=3000),10%,IF(AND(A1>=3001,A1<=6000),15%,IF(AND(A1>=6001,A1<=15000),20%))))
is the answer to your question, but this MAY produce a result of FALSE
because A1 may be <600 or A1 may be >15000 and you didn't account for these
cases.
HTH

 

[joaniemic]

Bob,
A1 cannot be less than $600 because we would not prepare this if it were.
However, for the last one I should have said if A1 is equal to or greater
than $6,001 the discount percentage is 20% and just left it at that because
anything greater than $6,000 is at 20%. Thanks.

 

[David Biddulph]

Nor does the question allow for values between 1500 and 1501, nor between
3000 and 3001, nor between 6000 and 6001.

If the OP actually intended not to say
If A1 is equal to or greater than $1,501 and less than or equal to $3,000
the discount is 10%
but
If A1 is greater than $1,500 and less than or equal to $3,000 the discount
is 10%
and so on, then the formula can be simplified to
=IF(AND(A1>=600,A1<=1500),5%,IF(A1<=3000,10%,IF(A1<=6000,15%,IF(A1<=15000,20%))))