# Exchange rate conversion help needed

1. ## Exchange rate conversion help needed

The attached (desensitized) worksheet is a copy of a section of one I have been building for the last few weeks which allows our sales director to ensure that our resellers are pricing our products correctly and keeping us strong against our competitors, also listed on the same reseller's website.

The price in column H is the end user price shown on the reseller's website - deducted from that is a 12% margin (column I) to allow for the end user markup of the reseller after buying from our distributor.
The figure in column I is then converted to Euros (column K)

At the moment there is a single formula, from an exchange rate of 1.373 Euros to the Pound, in column K - this would be fine if the exchange rate stayed the same, but of course it doesn't, and at the moment, I cannot sort data on this sheet because as soon as I do, the formula in column J no longer works and I end up with a whole column of 0.00s.

So what I would like to do is either:

(a) have a formula in column J that accepts any exchange rate, and then shows the correct sum in column K
(b) enter the price shown on the reseller website in to column H, and from that get the final sum in one more column with the 12% margin deducted and the conversion from GBP to EUR (not sure if this would be a VLOOKUP function or macro?)

I would greatly appreciate any help

2. ## Re: Exchange rate conversion help needed

Hi, the exchange rate conversion formula is in column K at the moment. My aim is to be able to enter any exchange rate in to column J, K or whatever, and still be able to sort data without ending up with a column full of zeros. Or to find a way to enter one figure in one column (the price shown on the reseller website) which then has the 12% deducted and is then converted with the up-to-date conversion rate in one process, and for the final figure to show in one more column.

3. ## Re: Exchange rate conversion help needed

you can put the exchange rate on a different sheet and refer to it using \$ for absolute addressing
for example
sheet2 cell A1
=I2*Sheet2!\$A\$1

then you can sort
or you can combine all the formulas into 1 column
and use sheet2!\$A\$2 for the discount

see column L

then you can do away with column I,J,K

see example

4. ## Re: Exchange rate conversion help needed

Wayne, thank you for your help. Can you tell me how to refresh the data on Sheet1? as at the moment, when I change the exchange rate in cell A1 on Sheet2 the sums are not changing in column L on Sheet1.

5. ## Re: Exchange rate conversion help needed

For info: I have checked that the option 'Workbook Calculation' is set to 'Automatic' in Excel 2010 options.

6. ## Re: Exchange rate conversion help needed

Never mind, it does update automatically.
Thanks once again.

7. ## Re: Exchange rate conversion help needed

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1