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,"")))))))))))))

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

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!

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

That did it. You are wonderful. Thank you so much for your help!

Ask Your Question

Weekly Poll