I have a supplier pricelist. In one column is the list price ( Cell A1) , in the next column is a number which references a further discount (i.e 1 = 5% discount, 2 = 10% discount) Cell A2

I want to have the next blank column (Cell A3) return the value of the list price less the discount.

There are only 4 discount numbers - 1,2,3,4 but they are random throughout the document and so I need a formula which will reference all the discount numbers, relate them to a percent discount, and then return the value in the next empty cell

re: " I want to have the next blank column (Cell A3)"A1, A2 and and A3 are all in the same column. They are in different

rows, so I'll assume that you mean "the next blankrow".You didn't provide any discount percetages for 3 or 4, so it's kind of hard for us to give you an exact answer.

Assuming 3 = 15% and 4 = 20%, try this forumla:

=A1*(1-CHOOSE(A2, 0.05, 0.1, 0.15, 0.2))

The CHOOSE function will choose one of the values from the list of values (0.05, 0.1, 0.15, 0.2) based on the value in A2 (1 - 4).

If A2 doesn't really contain 1 - 4, then a Nested IF will probably be required.

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

This is perfect.

Thank you very much for your help on this.

And thank you also, for moving the question to the correct area

Ask Your Question

Weekly Poll