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).
1 Answer
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.
-
Another solution, going off of yours would be to have an exchange rate column, where canada would be default be 1.0– pukSep 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