Calculate amount by looking up matching currency and rate of exchange

    Hello,


    I have a workbook where Worksheet1 has data with amounts paid in 3 different currencies, and I need a column to calculate the equivalent of the expense in a single currency. The workbook also contains Worksheet2 with rates of exchange which change on a daily basis. How can I find the corresponding date/currency/rate of exchange in Worksheet2 and calculate the USD equivalent if the amount paid was not in USD, and just copy the USD amount to the cell if it was paid in USD? The solution must be a formula, not a macro or VBA as they're not allowed where I work.


    Attached is a spreadsheet with sample data.


    Thank you very much.
    ZdjBel

    1. In column A on Sheet1, make sure that the dates are pure dates and do not have any time associated with them.
    2. In column D on sheet1, use this formula =IF([@Curr]="RSD",INDEX(Table2[RSD],MATCH([@Date],Table2[Date],0)),IF([@Curr]="EUR",INDEX(Table2[EUR],MATCH([@Date],Table2[Date],0)),1))*[@Total]

    Hello Alan,


    Thanks very much for the formula, unfortunately it gives errors for non-USD currencies. This is what I got, not sure how/where to edit the formula to get the desired result...



    [ATTACH=JSON]{"data-align":"none","data-size":"full","title":"Capture.JPG","data-attachmentid":1210344}[/ATTACH]


    Many thanks!


    Z

    Try:


    =IF([@Curr]="USD",[@Total],INDEX(Table2[[RSD]:[EUR]],MATCH(INT([@Date]),Table2[Date],0),MATCH([@Curr],Table2[[#Headers],[RSD]:[EUR]],0)))

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Hi NBVC,


    Your formula works to bring in the corresponding rate of exchange (ROE) just fine and that completes the first step of what I want to accomplish. The next step is to calculate the USD equivalent of the amount: divide Total by ROE if the currency is RSD, multiply Total by the ROE is the currency is EUR or just copy Total if the currency us USD. I'm certain that this would make the formula way too complex and slow to execute over my 20,000+ records so having a separate column, i.e. formula to do this portion of the calculations is absolutely fine.


    Thank you very much in advance.


    Z

    Change the header at D1 to "Rate", then in new column use:


    =IF([@Curr]="USD",[@Total],IF([@Curr]="RSD",[@Total]/[@Rate],IF([@Curr]="EUR",[@Total]*[@Rate],"")))

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    That did the trick. Thank you for the solution and the lesson, now I know how to nest multiple IF statements in a formula.


    All the best! :thanx:


    Z


    This thread is SOLVED, but I can't find how to mark it as such.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login