This is the equation I need help trying to set up. I have a list of products that all come in one of three sizes ( B, D, or L). In cell A2 I will put in the price, cell A3 I would like to put in the size of container (B=25.36, L=33.81, D=59.17), cell A4 will be A2/A3. So if I plug in size B in cell A3 it would divide by 25.36, if I plug in size L it would divide by 33.81, and if I plug in size D it would divide by 59.17. Thanks for your help.

=if(AND(A3<>"B",A3<>"D",A3<>"L"),"Incorrect Entry",A2/if(A3="B",25.36,if(A3="D",33.81,if(A3="L",59.17,"Error"))))

Thanks! It works great.

2 suggestions that might make the solution a bit more elegant and convenient. 1 - Use a Data Validation Drop Down in A3 to choose B, D or L. That way you won't have to deal with incorrect entries and you can shorten the formula to:

=A2/IF(A3="B",25.36,IF(A3="D",33.81,59.17))

2 - Use a VLOOKUP table for your B, D and L values so that you can update the table if your prices change instead of having to edit the formula. This will be especially convenient if your prices are going to be used in multiple formulas that would all have to be updated if there were a change.

The new formula might look something like this, assuming your VLOOKUP table was in G1:H3:

=A2/VLOOKUP(A3,$G$1:$H$3,2,0)

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

jwatt: When posting a question in a Help forum such as this, please try to use a subject line that gives the readers some idea of what your post is about.

If everyone used a generic subject line such as Excel Equation Help, we wouldn't be able to tell one question from another. It would also make searching the archives next to impossible.

I have edited the subject line of this thread to give you an idea of what I mean.

DerbyDad03

Office Forum Moderator

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

The vlookup method, which DerbyDad03 put forth, is a much better method in my opinion as it allows you to easily update prices without having to dig into your formulas and change where they are hard coded.

Ask Your Question

Weekly Poll