Converting Multiple Currencies t one

[Brandy2015]

Hi,

Apologies if this has been asked before but I've a table with multiple currencies on the same sheet. I want to convert this to one currency. So for example

A B C D
1 £100,000 $350,000 USD 1.5
2 $350,000 €250,000 Euro 1.65
3 €200,000 £400,000
4 €250,000 £100,000
5 £400,000 $350,000

On column E I would like to have the figures in column A converted to GBP using the exchange rates in column D. On column F I would like the figures in column B converted to GBP.

thanks for any help with this.

 

[DRSteele]

This will do what you ask, but the formulas must be entered manually because there is no easy way to make Excel detect in which currency the values are formatted. [N.B. The values in Value1 and Value2 are values formatted as various currencies; the $ symbol is not typed into the cell.]

When looking at a cross-rates currency table, read it from the rows to determine how much of the column-currency the row-currency will buy. For example, One USD will buy 0.667 GBP; on the flip-side, One GPB will buy 1.500 USD.

	A	B	C	D	E	F
1			cross rates	USD	GBP	EUR
2			USD	1.0000	0.6667	0.6061
3			GBP	1.5000	1.0000	0.9091
4			EUR	1.6500	1.1000	1.0000
5
6	value1	value2			GBP	GBP
7	£100,000	$350,000			£100,000	£233,333
8	$350,000	€ 250,000			£233,333	£275,000
9	€ 200,000	£400,000			£220,000	£400,000
10	€ 250,000	£100,000			£275,000	£100,000
11	£400,000	$350,000			£400,000	£233,333

<tbody>
</tbody>

Sheet4​

Worksheet Formulas Cell Formula E2 =1/D3 F2 =1/D4 F3 =1/E4 E4 =D4/D3 E7 =A7*E3 F7 =B7*E2 E8 =A8*E2 F8 =B8*E4 E9 =A9*E4 F9 =B9*E3 E10 =A10*E4 F10 =B10*E3 E11 =A11*E3 F11 =B11*E2 <tbody> </tbody>

<tbody>
</tbody>

 

[Brandy2015]

Thank you Drsteele. I forgot to mention in my original post that there are about 200 rows and 12 columns with the mixed currencies that I need to convert to GBP so manually multiplying the cells would be very time consuming. Is there any formula to help with this?

Thanks

 

[DRSteele]

The problem here is that I cannot figure out a way to make Excel determine how each cell is formatted with its currency. If we could do that, we could easily make a lookup algorithm to grab the appropriate rate from the Cross-Currency table. Can your data be altered so that there is a column beside each value that indicates the currency?

Stay tuned while I ruminate.....