Formula Dilemma

[Roz D]

I have a column titled Unit Price, I need to be able to find a way of firstly
choosing whether it is n £, $ or Euros and calcluating the exchange rate
accordingly, I will also need to be able to edit the exchange rate
periodically. Secondly the same Pcolumn needs to have an option between 2
sets of pricing: List price and Discounted Price. I have a limited amount of
experience, so any suggestions would be welcome.

 

[stew]

Hi Roz


You would not be able to do that within one column in any orderly fashion.

You could do it over 3 or 4 Columns. Do you wish to do that?

Also , are you saying, that your wholesale unit prices could be in Euro,
Pounds or Dollars and you would like the facility to show all stock in any
one Currency.

If you can answer these questions then we could move forward

Best

Stewart

 

[Per Jessen]

Hi R

Do you want to choose for all calculations on the sheet or will it change
for each row. Is the unit price looked up from an other table?

In either case you need a cell or column where the user can choose currency
(from a validation list), then apply an "If" formula to decide which
exchange rate to use from the table mentioned below.

As you need to edit the exchange rate, set up a table with the currency and
exchange rate in two columns, then reference the cell with the desired
exchange rate.

To select between two sets of prices, you have to add an extra column in
which you enter L for List Price or D for Discounted Price. Then apply
formula in your Unit Price sell to decide which price to use:
=If(PriceTypeCell="D", "Use Discounted Price", "Use List Price")

Hopes it helps.

Regards,
Per

 

[Roz D]

Thanks I have with your help and the help of a colleague managed to do what I
set out to achieve. Thanks once again.

 

[Per Jessen]

Thanks for your reply, I'm glad to help.

Regards,
Per