Solved Excel Function To Divide By Price

July 25, 2011 at 11:36:55
Specs: Windows Vista
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.

See More: Excel Function To Divide By Price

Report •

July 25, 2011 at 12:45:31
✔ Best Answer
=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"))))

Report •

July 25, 2011 at 14:41:27
Thanks! It works great.

Report •

July 25, 2011 at 15:00:25
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:


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:


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

Report •

Related Solutions

July 25, 2011 at 15:21:27

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.

Office Forum Moderator

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

Report •

July 26, 2011 at 05:48:16
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.

Report •

Ask Question