I have a worksheet, "2011-11", with a series of numbers in column C. The numbers are each in a given currency, identified by a code in column B. What I want to do is write a single formula (in cell C14) that will convert each number (from C4:C13) into pounds sterling (GBP) by dividing it by the relevant exchange rate, then take the sum of these numbers.
The exchange rates are stored on a separate worksheet, "GBP". The rate changes each month so there is a table with the currency codes listed vertically and the month listed horizontally.
I have a formula which, for a single cell C4, correctly looks up the exchange rate:
So I had hoped that by changing the reference range in the first
MATCH to point to
$B4:$B13, inserting the whole thing into a
SUM formula, and entering it as an array formula, I would get the right answer:
However, whilst it is nearly there, this is applying the exchange rate for row 4 to each row. Help much appreciated! Can it be done, or do I need to insert an extra column?
A couple of extra notes:
I tried using
SUM, but this made no difference
The value of A1 is "2011-11"