Converting currency

[RWLAUS]

Hi

I have a workbook with hundreds of contract details, many in different currencies. I have the numerical value in one column and the currency in another. What I'd like to do is convert all of them to one standard currency as well for reporting. So I believe I need a logic formula that will multiply the value by a pre-determined conversion rate in another cell, depending on the currency designation (AUD, USD etc) in the currency column. I've played around with some things with no success thus far. Any help would be much appreciated!

RWL

[Fotis1991]

Hi RWLAUS and welcome to the Forum.

Be sure that the best way to describe your problem, is to attach a sample workbook.

[RWLAUS]

Ah, excellent point, thanks Fotis! See attached.

Basically, I'm looking for the best formula to use in column C to convert the original amount (column A) based on the original currency (column B) and appropriate conversion rate (column G)

[Fotis1991]

Hi again.

I don't know if it's best function...,but this function, works for me.

In C2, put the formula:

Please Login or Register  to view this content.

Drag down as you need.


Hope to helps you.

[martindwilson]

in C2 filled down
=VLOOKUP("sgd:"&B2,$F$2:$G$8,2,FALSE)*A2

[royUK]

In the Rates only have the actual abbreviation e.g.USD & the rate next to it. Then obtain the rate using VLOOKUP

I have doen this and called the list Rates

in C2 & copied down

=ROUND(VLOOKUP(B2,Rates,2,0)*A2,2)

Currency Calculation

[Fotis1991]

Great & cleaver solution martindwilson!

We can learn a lot from you ..

Thank you!

[Fotis1991]

royUK.

Fantastic idea! I do not believe what I learn today ...

Please, continued to teach us ...!

[dilipandey]

Hi RWLAUS,

Use following formula in column C and drag it down:-

=$A2*OFFSET($G$1,MATCH("SGD:"&$B2,$F:$F,0)-1,0)

Hope this solves your problem.

Regards,
DILIPandey

< click on below 'star' if this helps >

[RWLAUS]

Thanks so much for the help, everyone! These all worked quite well

[dilipandey]

You are welcome RWLAUS,

Cheers

Regards,
DILIPandey

<click on below 'star' if this helps >