How to calculate amount by looking up matching currency and rate of exchange
Category: [Excel] Demo Available
How to calculate amount by looking up matching currency and rate of exchange
Requirement:
Worksheet1 has data with amounts paid in 3 different currencies, and the user needs 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 the user 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?
Solution:
In column A on Sheet1, make sure that the dates are pure dates and do not have any time associated with them.
=IF([@Curr]="USD",[@Total],INDEX(Table2[[RSD]:[EUR]],MATCH(INT([@Date]),Table2[Date],0),MATCH([@Curr],Table2[[#Headers],[RSD]:[EUR]],0)))
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.
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],"")))
Obtained from the OzGrid Help Forum.
Solution provided by NBVC.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.