2

I need to enter the values as I write cheques (ie $100 USD and $100CDN, not $111.55 USD and $100CDN) otherwise it will be a nightmare for me to match entries to invoices. I could create a column for USD and one for CDN, but that would result in too many columns. Is there a way to tell Excel what currency it is and what the exchange rate is (NOTE I will give it the exchange rate) then have it calculate it correctly? Is there a better way to do this (currently, I am using two separate spreadsheets).

enter image description here

1 Answer 1

2

You could do something like this:

Col A   Col B   Col C
Amt     Curr    Master
$100    CND     =IF(B2="USD",A2*1.2,A2)
$100    USD     =IF(B3="USD",A3*1.2,A3)

The formula =IF(B2="USD",A2*1.2,A2) will check if column B is in USD, and if it is, it will multiply it by 1.2 (or whatever the exchange rate should be), otherwise it will display the other currency. This will change everything into CND (or whatever).

If you have multiple other currencies, you can nest the IF like =IF(B2="USD",B2*1.2,IF(B2="HKD",B2*.75,B2)) to convert it all to the default currency.

If the exchange rate changes, you can set up the formula to reference a cell with the rate in it, but you would need to make sure that if the rate changes, you hard code the values otherwise prior values will default to the new exchange rate.

2
  • Another solution, going off of yours would be to have an exchange rate column, where canada would be default be 1.0
    – puk
    Sep 28, 2014 at 12:02
  • @puk correct, you could do any number of references for exchange rates with only adding one column to the data with the IF statements. This wouldn't add much to sort through when viewing data, but would give you plenty of flexibility. Sep 28, 2014 at 12:05

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .