Solved If and statement in Excel

February 7, 2014 at 10:23:33
Specs: Windows 7
I am trying to get Excel to evaluate if g3 is the "Red" then evaluate b6 to determine the price. It works until I get to 501. For some reason Excel wants the * but I do not know why. Any suggestions?
=IF((AND(G3="Red",B6<=250)),25,
((IF((AND(G3="Red",B6<=500)),35,
((IF((AND(G3="Red",B6<=1000)),40*
((IF((AND(G3="Red",B6<=2500)),50*
((IF((AND(G3="Red",B6>=2501)),75,"")))))))))))))

See More: If and statement in Excel

Report •


#1
February 7, 2014 at 13:08:19
✔ Best Answer
First, I'll discuss your formula, then I'll suggested a method using VLOOKUP that might be more efficient.

I copied your formula into a spreadsheet. I put Red in G3 and started entering values in B6.

Starting at 501, I get a #VALUE error which is what I would have expected. The * is the multiplication operator and Excel can't figure out what to multiply by 40.

As soon as I changed the *'s to commas, the formula works fine.

=IF((AND(G3="Red",B6<=250)),25,
((IF((AND(G3="Red",B6<=500)),35,
((IF((AND(G3="Red",B6<=1000)),40,
((IF((AND(G3="Red",B6<=2500)),50,
((IF((AND(G3="Red",B6>=2501)),75,"")))))))))))))

However, you have way too many parenthesis. Even though the formula works, it can be written in a shorter manner:

=IF(AND(G3="Red",B6<=250),25,
IF(AND(G3="Red",B6<=500),35,
IF(AND(G3="Red",B6<=1000),40,
IF(AND(G3="Red",B6<=2500),50,
IF(AND(G3="Red",B6>=2501),75,"")))))

That said, you might want to consider a VLOOKUP formula instead of a Nested IF. By using VLOOKUP and a lookup table for your quantity/price relationships, you can enter your values in the table and never have to touch the formula when the criteria change.

For example, if you set up a table like this...

       A        B
1       0      25
2     251      35
3     501      40
4    1001      50
5    2501      75

... then you can use this formula:

=IF(G3="Red",VLOOKUP(B6,$A$1:$B$5,2,1),"")

The range_lookup argument of "1" means that VLOOKUP will find the value that is equal to or less than the value in B6 and return the corresponding price.

e.g. When B6 = 752, 40 will be returned because 752 isn't found in the table so VLOOKUP uses the next value that is less than 752 which is 501.

As I mentioned earlier, when the price or quantity criteria changes, all you have to do is change the table and not mess with a cumbersome Nested IF.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
February 7, 2014 at 13:43:34
Yes, I realized the stupid comma after I posted. I am however going to work on the vlookup. I have one already but the all other results in g3 are set costs-not varying based on quantity. I think that I should be able to add a vlookup within a vlookup but couldn't figure out exactly how. Thank you so much for your help. It was driving me crazy!

Report •

#3
February 8, 2014 at 05:11:02
How about this:

Using the same example lookup_table as I used above, create another lookup_table for the other items in G3 with the item and the price. Then try something like this (untested)...

       C       D
1    Blue      10
2    Green     20
3    Tan       30

=IF(G3<>”Red”,VLOOKUP(G3,$C$1:$D$3,2,0),VLOOKUP(B6,$A$1:$B$5,2,1))

This will lookup the non-Red items in the new table and return its fixed price. For Red items it will use the varying price lookup_table.

Note that the range_lookup argument is 0 (FALSE) for the first VLOOKUP because you are looking for an exact match.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

Related Solutions

#4
February 10, 2014 at 06:03:15
That did it. You are wonderful. Thank you so much for your help!

Report •


Ask Question