Array Forumla Help

[DRJD]

Hi Guys,

Hope you can help.

I've attached a spreadsheet which should hopefully illustrate what I need help with.

Basically, I have a spreadsheet with values in different currencies. I also have exchange rates for those currencies. (Cells highlighted in blue on spreadsheet).

What I would like to be able to do is to use a formula to convert the values from their original currency into USD and GBP and then sum the respective values to get the values in the cells highlighted in green.

Am I able to do this with an array formula or would I be better off using a macro to do the calculation?

(Also, although the example spreadsheet only has 5 rows, I do not know in advance how many rows the spreadsheet will have, so I need a formula that will work for the whole column).

Any help appreciated.

[Bob Phillips]

You can't do whole columns, but just use a high number or dynamic ranges

=SUMPRODUCT(A2:A2000,VLOOKUP("GBP",$D:$E,2,FALSE)/$E2:$E200)

etc.