Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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:

=INDEX(GBP!$A$4:$BE$42,MATCH($B4,GBP!$A$4:$A$42,0),MATCH(DATE(LEFT(A1,4),MID(A1,6,2),31),GBP!$A$3:$CE$3,1))

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:

{=SUM(C4:C13/INDEX(GBP!$A$4:$BE$42,MATCH($B4:$B13,GBP!$A$4:$A$42,0),MATCH(DATE(LEFT(A1,4),MID(A1,6,2),31),GBP!$A$3:$CE$3,1)))}

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:

  1. I tried using SUMPRODUCT instead of SUM, but this made no difference

  2. The value of A1 is "2011-11"

share|improve this question
    
I have not looked very carefully at your problem but I notice that your formula is full of $s which means do not adjust the target. Could this be your problem? –  Tony Dallimore Dec 5 '11 at 16:54
    
@TonyDallimore: It could but I don't think so. The $s are there on purpose and mostly refer to the lookup array (which is static). –  Michael Pavey Dec 5 '11 at 17:02
add comment

1 Answer

My first guess was partially correct but the wrong way round.

Problem 1

The look up value

DATE(LEFT(A1,4),MID(A1,6,2),31)

is based on the value 2011-11 and is therefore equivalent to

DATE(2011,11,31)

This will give inconsistent values. For October it gives 31Oct11. For November is gives 1Dec11. To get consistent values, such as the last day of any month use:

DATE(LEFT(A1,4),MID(A1,6,2)+1,0)

Problem 2

If you copy your initial formula down the column you

=INDEX(GBP!$A$4:$BE$42,MATCH($B4,GBP!$A$4:$A$42,0),
 MATCH(DATE(LEFT(A1,4),MID(A1,6,2),31),GBP!$A$3:$CE$3,1))

you get "#VALUE!" for all the copies because A1 becomes B1, C1, etc.

You need

=INDEX(GBP!$A$4:$BE$42,MATCH($B4,GBP!$A$4:$A$42,0),
 MATCH(DATE(LEFT($A$1,4),MID($A$1,6,2),31),GBP!$A$3:$CE$3,1))
                 ^ ^          ^ ^        

Problem 3

In your array formula

=SUM(C4:C13/INDEX(GBP!$A$4:$BE$42,MATCH($B4:$B13,GBP!$A$4:$A$42,0),
 MATCH(DATE(LEFT(A1,4),MID(A1,6,2),31),GBP!$A$3:$CE$3,1)))}

you have five ranges of which you want Excel to use two as arrays. I cannot find anything to suggest Excel if clever enough to guess that this what you want.

The best I can offer is to set D4 to:

=INDEX(GBP!$A$4:$BE$42,MATCH($B4,GBP!$A$4:$A$42,0),
 MATCH(DATE(LEFT($A$1,4),MID($A$1,6,2)+1,0),GBP!$A$3:$CE$3,1))

Copy this formula down the column and calculate the total with:

{=SUM(C4:C13/D4:D13)}

However, I am an adequate to good Excel VBA programmer; I am not a Excel power user. Others may be able to tell you how to achieve what you seek.

share|improve this answer
    
+1 Nice analysis. I disagree with the last statement, though. –  Rachel Hettinger Dec 6 '11 at 5:52
    
@Rachel Hettinger. Thank you for the +1. I have seen power users achieve amazing things with a few key strokes so I cannot claim to be one of there number. I only learnt VBA because it was the only programming language my then employer permitted. I have not been a professional programmer for 35 years. My major skill today is perseverance. –  Tony Dallimore Dec 6 '11 at 9:52
    
@TonyDallimore: Thanks so much, this is really very helpful. And apologies, I think I ought to have removed some of the irrelevant complexity from the problem before posting, to save you from having to deal with my sloppy coding (problem 1 in particular -- for which, thanks for a great solution). Re problem 2, I never got as far as copying it down (have amended my original question to clarify that I was seeking to put the whole thing in a single cell) -- but you're quite right to point out that flaw :) Re 3, too bad but perhaps I was just being overambitious! –  Michael Pavey Dec 6 '11 at 10:12
    
@Michael Pavey. Re Problem 2. My understanding of array formula is that Excel is only doing in the background what you could do on the sheet. This understanding implies: if you cannot copy the simple formula down the column, Excel cannot copy the array formula down the column. –  Tony Dallimore Dec 6 '11 at 10:52
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.