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

Do you think Google Fiber has a strong future?

Discuss in The Lounge

Poll History