Using IF function when you have more than 5 possible solutions

[ms1]

I need a formula to calculate a % mark up. the mark up differs depending on the manufacturers selling price, and i need a formula that will take this into consideration. please see the attachment, hopefully that will make clear what i am trying to say.

Thanks

[NBVC]

in the table in column I, only input the lower bounds of each range

e.g

0
6.07
6.23
12.12

etc..

then use formula:

=LOOKUP(A2,$I$3:$J$12)

copied down.

[Richard Buttrey]

Hi,

See if the attached helps

Regards

[ms1]

this is good, thanks, but i also need the calculation to add the mark up so if its a % i would normally insert A2*(1+B2) but if its in Euros then i would only need A2+B2

how can i do this with the look up included? hope this makes sense!!

[ms1]

i need it to automatically detect whether it is a currency or %

[ms1]

richard, i think ur formula is correct but i cant get it to work in my spreadsheet - i have changed it so the cells are correct in the formula but i am getting #REF! any ideas on what im doing wrong?

Thanks

[Richard Buttrey]

Hi,

Can you upload your workbook so that we can take a look.

Regards

[ms1]

Here is a sheet from my work book. i have added the %'s manually but need them to go in automatically as prices change!!

Thank you

[ms1]

i need a formula for the "Pharmacy Mark Up of PPP" too. hope im not being to cheeky!!

[Richard Buttrey]

Hi,

I've added the formula in the first three relevant cells of column Q.

Not quite sure what you're expecting for the PPP column since you seen to have a formula in there already. However if you're trying to lookup another value just follow the same logic as the other column Q formula.

HTH

[ms1]

at the end of the formula u have put a

,2

please can you tell me what this is for so i know when i am adapting it for other places where a similar formula is needed.

thanks

[Richard Buttrey]

Hi,

A VLOOKUP() function contains three elements plus an optional fourth element. In this case we're using just the three elements. The third element, the value 2 in this example, picks the value from the 2nd column in the range defined by the second element. The second element refers to the range AE3:AG12, hence the second column is column AG.

HTH

[Richard Buttrey]

Hi,

A VLOOKUP() function contains three elements plus an optional fourth element. In this case we're using just the three elements. The third element, the value 2 in this example, picks the value from the 2nd column in the range defined by the second element. The second element refers to the range AE3:AG12, hence the second column is column AG.

HTH