CODEWITHSUNDEEP
arraysexcel
Simone Raba
Simone Raba

Reputation: 13

Excel error when multiplying two arrays

I am trying to multiply two arrays and I get a #Value error.

Just a brief description of what is in the table:

Column E is used as a boolean array based on the existing of a particlar string at the end

Column D is used to lookup column A where the same set of IDs reside

Finally column B contains the amount to be added based on the boolean vector derived from column E

Step by step (using F9) all the pieces evaluates correctly, but the whole formula doesn't.

A   B     C     D   E      
ID  Amounts     ID  Code
9   100         1   PT91
8   2000        2   PT92
7   3000        3   PT93
66  4000        4   PT94
5   5000        5   PT95
4   5000        66  PT96
3   5000        7   PT97
2   5000        8   PT93
1   5000        9   PT99

The formula giving the error is:

{=MMULT(TRANSPOSE(IF(RIGHT(E3:E11,2)="93",1,0)),OFFSET(B2,MATCH(D3:D11,A3:A11,0),0))}

The expected result is 7000

Thanks a lot

Simone

Upvotes: 0

Views: 326

Answers (1)

XOR LX
XOR LX

Reputation: 7762

You need to first "dereference" the returns from OFFSET (which can equally return cell references as well as cell values, depending on the construction involved), for which N is an appropriate function here, i.e.:

=MMULT(TRANSPOSE(IF(RIGHT(E3:E11,2)="93",1,0)),N(OFFSET(B2,MATCH(D3:D11,A3:A11,0),0)))

You can also achieve identical results using the shorter, non-volatile and non-array:

=SUMPRODUCT(0+(RIGHT(E3:E11,2)="93"),SUMIF(A3:A11,D3:D11,B3:B11))

Regards

Upvotes: 3

Related Questions