# Automatic currency conversion in Google Sheets

I have a personal accounting sheet that is mainly accounting in Euros, but for every manual Euro (EUR) entry I do, I want to have that entry converted to USD in the previous column next to it automatically, fixed at that moment's EUR -> USD rate (and not fluctuate with the currency exchange rate over time), and rounded up to the nearest dollar.

Currently, I only enter the converted amount manually, based on the output from a calculation cell with the =GOOGLEFINANCE("CURRENCY:EURUSD") function.

For each accounting entry, today's date is manually entered in column A, the EUR figure manually entered in column F, and I would like the automatically converted EUR -> USD figure (rounded up to the nearest dollar) to appear next to that EUR entry in column E, and remain unchanging. As a reference the =GOOGLEFINANCE("CURRENCY:EURUSD") function lives in cell J1, which is part of where I currently do the aforementioned manual currency conversions in the small calc table I1:L2.

Many thanks for any help!

• I just may not have phrased it correctly - once the the conversion to USD is calculated, I don't want the USD converted figure to fluctuate when the currency rate fluctuates over time. Will edit question to make more clear, thank you. – Ryan Smith Feb 10 '19 at 14:24
• @MARKMYANSWER no, i just want it automated, and not fluctuating once calcluated, so if anyone can think of way to do that in any way that would be great :) – Ryan Smith Feb 10 '19 at 14:32

• paste in cell E3 and drag down

=IF(LEN(F3); ROUND(F3 * IFERROR(VLOOKUP(A3;
"select Col1 where Col1 is not null"; 0))\
"select Col2 where Col2 is not null"; 0)}; 2; 0);

• Thanks for the formula, however I believe this does not work for two reasons: 1) cell J1's Google Finance function is a volatile formula, and the array formula output will accordingly constantly change as the EUR:USD conversion rate changes; and 2) It outputs a text string, which I can't use....it would need to output a value. I've modified my original question to include a link to a sample google sheet to work this out on. Thank you! – Ryan Smith Feb 10 '19 at 17:33
• @RyanSmith answer updated – user0 Feb 10 '19 at 19:10
• everything worked brilliantly and you were a great help to work through the specifics of the problem, thanks again for your time and support! – Ryan Smith Feb 10 '19 at 19:42
• This solution seems to work fine without the ARRAYFORMULA component, am I correct or am I not seeing a potential error? – Ryan Smith Feb 10 '19 at 20:36
• not sure what exactly you are asking... ARRAYFORMULA is on the first row, right after IFERROR and its needed to construct virtual table - that stuff what is in between {} brackets. and yes, it may work without it, but not sure (I didnt tested it out) if in all possible scenarios so I left it there as "safety future" – user0 Feb 10 '19 at 20:56

Formula variation without ARRAYFORMULA:

=IF(LEN(F3); ROUND(F3 * IFERROR(VLOOKUP(A3;