+ Reply to Thread
Results 1 to 7 of 7

Exchange rate conversion help needed

  1. #1
    Registered User
    Join Date
    05-06-2013
    Location
    Germany
    MS-Off Ver
    Excel 2010
    Posts
    29

    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
    Last edited by Jim_Davidson; 03-09-2015 at 12:33 PM.

  2. #2
    Registered User
    Join Date
    05-06-2013
    Location
    Germany
    MS-Off Ver
    Excel 2010
    Posts
    29

    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. #3
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    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
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  4. #4
    Registered User
    Join Date
    05-06-2013
    Location
    Germany
    MS-Off Ver
    Excel 2010
    Posts
    29

    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. #5
    Registered User
    Join Date
    05-06-2013
    Location
    Germany
    MS-Off Ver
    Excel 2010
    Posts
    29

    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. #6
    Registered User
    Join Date
    05-06-2013
    Location
    Germany
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: Exchange rate conversion help needed

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

  7. #7
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    Re: Exchange rate conversion help needed

    your welcome

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 2
    Last Post: 03-07-2015, 08:17 PM
  2. Replies: 1
    Last Post: 03-06-2015, 09:54 PM
  3. Replies: 0
    Last Post: 03-06-2015, 03:07 PM
  4. Replies: 0
    Last Post: 03-06-2015, 12:43 PM
  5. Is 'ranking by' a VBA solution or a manual solution?
    By mtw2018 in forum Excel General
    Replies: 2
    Last Post: 04-10-2012, 01:47 PM

Bookmarks

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