sumproduct with arrays of different size

[excellearner16]

I am trying to use sumproduct with arrays of different size. I want to use last number of smaller array to multiply with remaining values of the large array. For example,

Array 1 = {1,3,5,7,9}
Array 2 = {1,2,3}

I am looking for a formula for expanding the Array 2 to make it {1,2,3,3,3} -- where the last two 3s in this array is the last number of Array 2. Then, I want to make sure that it expand to make this of size of Array 1 so that I can use this for sumproduct.

Any help will be greatly appreciated

 

[XOR LX]

Hi,

IFERROR(IF(Array1,Array2),INDEX(Array2,COLUMNS(Array2)))

coerced as an array formula**, will produce the required new array.

Note that, just as in the example you give, it is assumed that Array2 (and therefore also Array1) is a row-vector.

Since array-entry is required, it is as well to use SUM with CSE rather than SUMPRODUCT, the latter not being sufficiently strong to coerce the array from IFERROR in the above. So, using your example:

=SUM(IFERROR(IF(Array1,Array2),INDEX(Array2,COLUMNS(Array2)))*Array1)

with CSE, will resolve to:

=SUM({1,2,3,3,3}*{1,3,5,7,9})

etc., as desired, though:

=SUMPRODUCT(IFERROR(IF(Array1,Array2),INDEX(Array2,COLUMNS(Array2)))*Array1)

without CSE, will not work as intended, instead resolving to:

=SUMPRODUCT(1*{1,3,5,7,9})

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

 

[Macropod]

Cross-posted at: sumproduct with arrays of different size
Please read Mr Excel's policy on Cross-Posting in rule 13: Forum Rules

 

[excellearner16]

Thanks a lot

 

[excellearner16]

sorry, will take care of this in future

 

[FlameRetired]

These both do that.

=MATCH(({1,3,5,7,9}-1)/2+1,{1,2,3},1)

=MATCH({1,3,5,7,9},({1,2,3}-1)*2+1,1)