IF statement error

[SkampDiddy]

Hey:

I keep getting an error on the following statement:

=IF((C21<500, "0%", IF(AND(C21>501, C21<700, "10%", IF(AND(C21>701, C21<900, "15%", IF(AND(C21>901, C21<1200, "20%", IF(AND(C21>1201, "20%"))))))))))

Basically I'm trying to take a value and determine the discount based on the total. If the total is less than $500 then there is a 0% discount, If between 501 and 700 then $10%, if 701 and 900 then, 15%, if between 901 and 1200 then 20%, if greater than 1201, then 20%.

I looked up some help online, but I cannot figure out the mistake I am making. Any help would be great, thanks!

SkampDiddy

[6StringJazzer]

You need to have parentheses around the arguments to the AND functions.

=IF(C21<500, "0%", IF(AND(C21>501, C21<700), "10%", IF(AND(C21>701, C21<900), "15%", IF(AND(C21>901, C21<1200), "20%", IF(C21>1201, "20%")))))

However, your answers are being generated as strings. Depending on what you do with them you might want numbers. For example, instead of "10%" use 0.1.

Also, I'm going to think about this for another minute, I think this can be done more elegantly with LOOKUP.

EDIT: You are not including the cases where C21=500,501,700,701,900,901,1200,1201. You need to rethink your comparisons.

[SkampDiddy]

Ok I put the formula in and it works perfectly, but now I have a new problem. I should have applied the discount to the before tax amount, not the after tax amount. So I tried to change C21 to C19, but that gives me a "circular formula" (I think that is what it says). I need to be able to read the subtotal and then determine the discount. Then take that price and times it by tax (13%) to give me my total. Is this possible?

[SkampDiddy]

Ok I put the formula in and it works perfectly, but now I have a new problem. I should have applied the discount to the before tax amount, not the after tax amount. So I tried to change C21 to C19, but that gives me a "circular formula" (I think that is what it says). I need to be able to read the subtotal and then determine the discount. Then take that price and times it by tax (13%) to give me my total. Is this possible?

Nevermind I just had to change around the order. Thanks for your help, it works great!

[6StringJazzer]

...I should have applied the discount to the before tax amount, not the after tax amount. So I tried to change C21 to C19, but that gives me a "circular formula" (I think that is what it says). I need to be able to read the subtotal and then determine the discount. Then take that price and times it by tax (13%) to give me my total. Is this possible?

What's in C19? What cell is the base price in? How are you calculating tax? At this point attaching the workbook would be helpful.

[zbor]

Here, try this: =LOOKUP(C21,{0,500,700,900},{0,10,15,20}/100)

[mdbct]

The Lookup formula would be:

=LOOKUP(C1,{0,501,701,901},{0,0.1,0.15,0.2})

[contaminated]

You forgot to close parentesis after AND function

=IF(C5<500,"0%",IF(AND(C5>501,C5<700),"10%",IF(AND(C5>701,C5<900),"15%",IF(AND(C5>901,C5<1200),"20%",IF(C5>1201,"20%")))))

Actua;;y u can use simple VLOOKUP See file

[mdbct]

Since there is only one cell reference, I'm guessing you tried entering the formula in cell C19 if you are receiving the circular reference error.

Just about anything is possible. Can you provide a sample sheet with raw data and the expected results?

[zbor]

Also, according to your IF function:

=IF(C21<500, "0%", IF(AND(C21>501, C21<700), etc...

What if is C21=500.8 or exactly 500?

Is it less than 500? No. So IF function go further.
Is ti bigger than 501 and less than 700? No... So IF function go further (up to the end.

And you get result 20% (I doubt it's correct result).

And all of that you can avoid by proper using of IF function.

Look at this way:

=IF(C21<500, 0, 10)

Now.. if number is 500.8 it will return 10.
Is it less than 500? No. Then ELSE -> 10

So your correct formula would be:

=IF((C21<500, "0%", IF(C21<700, "10%", IF( C21<900, "15%", "20%"))))

No need to test is it bigger than 500 because you already check it: Is it less than 500? No. Then what is it? (If your answer is greater then you got the point )