SUMPRODUCT as Array formula

[Rofeu]

Hello,

I've created this array formula that puzzles me.

{=SUMPRODUCT(INDEX(Support!R1C4:R6C5,MATCH(INDEX(Support!R1C1:R14C2,MATCH
(LEFT(VSource!R2C1:R17219C1,2),Support!R1C1:R14C1,FALSE),2),Support!R1C4:
R6C4,FALSE),2)*VSource!R2C13:R17219C13)}

If I enter this over 2 or more cells, it gives me the correct result in both cells. However, if I put it in a single cell it comes up with a completely different number.

Basically, what it is supposed to do is:

from a list of countries look up the Base Currency then use this base currency to look up the exchange rate for that currency and multiply the amount behind the country with that and add the results for the different countries.

Can someone explain the logic behind the difference between single cell and multiple cell array formulae?


Cheers,

Roel

 

[SkipVought]

Hi,

First off, you have two MATCH function in your expression that use a Match_type of 2.

Match_type is the number -1, 0, or 1. Match_type specifies how Microsoft Excel matches lookup_value with values in lookup_array.

Skip,

_[red][/red]

 

[SkipVought]

Maybe I spoke too soon.

If you omitted, then the default is 1

Skip,

_[red][/red]

 

[Rofeu]

Hi,

actually, Skip, they both use Match_Type = 0 (I used FALSE, must have been confused with vlookup, but the result it the same), the 2 is part of the INDEX referring that it should take the value from the second column.

Any thoughts on how to get the result in a single cell array formula and why it behaves as it does?

Cheers,

Roel

 

[SkipVought]

I won't analyze the formula because I don't use or like R1C1 notation. It would take too much effort to convert it.

You also need to describe the source data and lookup values.

Skip,

_[red][/red]

 

[Rofeu]

Hi,

Formula in A1:

{=SUMPRODUCT(INDEX(Support!$D$1:$E$6,MATCH(INDEX(Support!$A$1:$B$14,MATCH(LEFT(VSource!$A$2:$A$17219,2),Support!$A$1:$A$14,FALSE),2),Support!$D$1:$D$6,FALSE),2)*VSource!$M$2:$M$17219)}

Sample VSource

Col A	Col M
AE100	119.78
AE100	840.38
AE100	-274,000.00
AE101	112.02
AE101	171.52
AE101	219.82
BE100	444.57
BE100	193.65
BE100	-183.44

Support!$A$1:$A$14

AE	USD
AT	EUR
BE	EUR
CH	CHF
DE	EUR
DK	DKK
ES	EUR
FR	EUR
GB	GBP
IE	EUR
IT	EUR
NL	EUR
PT	EUR
SE	SEK

Support!$D$1:$E$6

EUR	1
USD	0.82
CHF	0.6
DKK	0.12
SEK	0.1
GBP	1.38

Hope you can help me out with this.

Cheers,

Roel

 

[SkipVought]

Using your test data, I get -223106.994 not matter where I put the array formula or it its one or its more than one.

I can't seem to duplicate your problem.

Skip,

_[red][/red]

 

[Rofeu]

That's odd. The thing is that -223106.994 is incorrect. It multiplies EVERYTHING with 0.82, which is the rate for USD and the first one it finds. (same result entering it as a array and regular formula)

When I select 2 cells and enter the formula as an array it returns -223025.1336 in both cells, which is the correct outcome.

It seems that the formula doesn't not consider the array returned by the INDEX function as an array at all.

Cheers,

Roel

 

[SkipVought]

Consider using the OFFSET function rather than INDEX.

Skip,

_[red][/red]

 

[Rofeu]

Hi,

I've tried the OFFSET as follows, but I get #VALUE!. Not sure what I'm doing wrong here.

{=SUMPRODUCT((OFFSET(Support!E1,MATCH(OFFSET(Support!B1,MATCH(LEFT(VSource!$A$2:$A$10,2),Support!A1:A14,0)-1,0),Support!D1:D6,0),0)-1)*VSource!$M$2:$M$17219)}

Cheers,

Roel

 

[Rofeu]

Sorry, that was the wrong formula, this is the one I tried:

{=SUMPRODUCT(OFFSET(Support!E1,MATCH(OFFSET(Support!B1,MATCH(LEFT(VSource!A2:A10,2),Support!A1:A14,0)-1,0),Support!D1:D6,0)-1,0)*VSource!$M$2:$M$10)}

result is the same though.

Cheers,

Roel

 

[SkipVought]

Your MATCH, need a LOOKUP VALUE. OFFSET returns a RANGE
[tt]
....,MATCH(???,OFFSET(Support!B1.......
[/tt]


Skip,

_[red][/red]

 

[Rofeu]

Hello,

MATCH(LEFT(VSource!A2:A10,2),Support!A1:A14,0)

-this returns an array of integers representing the rows on which the default currency is listed for the country codes.

In my example this returns:
{1,1,1,1,1,1,3,3,3}

The OFFSET should return an array of ranges with the values:
{USD,USD,USD,USD,USD,USD,EUR,EUR,EUR}

This array should then be matched against the list of rates resulting in the following array:
{2,2,2,2,2,2,1,1,1}

The other OFFSET should then return an array of ranges with the rates in them:
{0.82,0.82,0.82,0.82,0.82,0.82,1,1,1}

which is then multiplied by the values in M1:M10:
{119.78,840.38,-274000,112.02,171.52,219.82,444.57,193.65,-183.44}

resulting in the following array:
{98.2196,689.1116,-224680,91.8564,140.6464,180.2524,444.57,193.65,-183.44}

which is then summed to -223025.1336

I put the -1 after the MATCH functions to compensate for starting the offset reference at row 1.

I hope I've explained it clearer now.

Cheers,

Roel

 

[SkipVought]

Now all your range references are RELATIVE. Before they were ABSOLUTE.

Skip,

_[red][/red]

 

[Rofeu]

That doesn't matter. Result's the same.