Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Dilemma
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. -- R |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Dilemma
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 "Roz D" wrote: 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. -- R |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Dilemma
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" skrev i meddelelsen ... 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. -- R |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Dilemma
Thanks I have with your help and the help of a colleague managed to do what I
set out to achieve. Thanks once again. :) -- R "Per Jessen" wrote: 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" skrev i meddelelsen ... 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. -- R |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Dilemma
Thanks for your reply, I'm glad to help.
Regards, Per On 12 Dec., 14:09, Roz D wrote: Thanks I have with your help and the help of a colleague managed to do what I set out to achieve. Thanks once again. :) -- R |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What if Dilemma | Excel Discussion (Misc queries) | |||
Apostrophe Dilemma | Excel Discussion (Misc queries) | |||
BIG Dilemma.....HELP!! | Excel Worksheet Functions | |||
If Then Dilemma | Excel Worksheet Functions | |||
The Prisoner's Dilemma | Excel Discussion (Misc queries) |