Convert currency

[170619]

Hi

I have a problem I need help with. I have 600+ order lines in an excel sheet. Among other columns, there is 1 column for currency (DKK, EUR, USD), and 1 column for a money value in that currency. Suppose there are three order lines; one line with DKK 10.000, one with EUR 1.000, and one with USD 1.500. If I do a sum of these values, I get 10.000 + 1.000 + 1.500 = 12.500.

I need some extra column where I can convert EUR and USD to DKK. I tried to do an "if" but I don't know how to create multiple logical conditions and multiple true and false values.

Any ideas?

[Aardigspook]

You can use a nested IF. Let's say your 'currency' column, where you have 'DKK' 'EUR' or 'USD' is column B and your amount is in column C and that your data starts on row 2.
You can put this in D2:

Please Login or Register  to view this content.

You'll need to change the '10' and '11' to the actual exchange rates for EUR to DKK and USD to DKK.

If you have those in other fixed cells, you can use absolute cell references instead of the actual figures. For example, if you had EUR to DKK in F1 and USD to DKK in H1, the formula would be:

Please Login or Register  to view this content.

You could have another column for the exchange rate instead, if you need to be able to change that for each line. If you had the exchange rate in col D, then the formula (now in column E) would be a simpler one:

Please Login or Register  to view this content.

Note - if you have the exchange rate the other way round (DKK to EUR instead of EUR to DKK) then the calculations change a bit. /10 becomes *0.1 or C2/$F$1 becomes C2*$F$1 or the last formula becomes =C2*D2.


Hope that all makes sense.

[Glenn Kennedy]

... or use a VLOOKUP:

=A2*VLOOKUP(B2,$E:$F,2,FALSE)

[63falcondude]

Here's one option in C2 to make everything into USD:

=B2*IF(A2="DKK",0.15,IF(A2="EUR",1.12,IF(A2="USD",1,"Currency Not Recognized")))

Assuming currency is in column A and value is in column B.

See attachment.