In this post I will show how important it is to avoid constants in a formula.
In my example I have a list of products with purchase price in three different currencies.
The question is, how should we go about calculating the price in US Dollars for each product?
The first way (=D5*1,3045) will get you in trouble for two reasons:
- When the currency rates change, you would need to edit each and every cell that has a currency rate reference in it.
- Since this list has prices in three different currencies, you can’t copy the formula down.
The second way (=D5*I5) solves the problem with changing currencies. When you update the currency table, the values will update. But you still can’t copy the formula down, since we have three different currencies.
So, what’s the solution? My favourite function, the VLOOKUP!
The VLOOKUP works like this: You tell it what to look for (the currency in the column to the left) and ask it to find that value in another table (the table to the right). Then you tell it to return the value in the second column of that table.
Here’s the formula:
US: =D5*VLOOKUP(E5,$H$5: $I$7,2,0)
European: =D5*VLOOKUP(E5; $H$5: $I$7;2;0)
D5 is the price in local currency (560.00)
E5 is the value you want to find (EUR)
$H$5: $I$7 is the currency table. Don’t forget to lock this reference with dollar signs (shortcut: F4)
The 2 is the number of the column of the value you want to return, and the 0 means that you want an exact match.
All done! When you update the currency rates, the amounts will update automatically, and when you add new rows to the table, you simply copy the formula further down.