Performing a convolution in a worksheet

[peter dmz]

I have data in the first two rows of my spreadsheet.
In the third row, I would like to compute a convolution:

X(3,j) = sum for i = 1 to j of X(2,i)*X(1,j+1-i)

where X(i,j) is the data in the ith row and jth column of the spreadsheet.

The following array formula, entered in cell C3 almost works:

{=SUM($A$2:C$2*INDEX($A$1:$Z$1,0,COLUMN(D$1)-COLUMN($A$1:C$1)))}

I say almost because it only works if I enter it in multiple cells, i.e.,
C3:C4 (in which case it gives the same number, which is the correct result
for C3, in both C3 and C4).

If I enter it cell C3 only, it seems to "forget" it is an array formula and
produces the wrong result.

Any thoughts on how to fix this problem, or on how to solve it another way,
are much appreciated.

 

[Jerry W. Lewis]

I can tell you what is happening, but am fuzzy as to why it was designed
this way.

INDEX does not always return arrays when you would expect. For instance
{=COUNT(INDEX($A$1:$Z$1,0,COLUMN(D$1)-COLUMN($A$1:C$1)))}
returns 1 instead of 3. That one value would be the first value of the
expected array, so that
{=SUM($A$2:C$2*INDEX($A$1:$Z$1,0,COLUMN(D$1)-COLUMN($A$1:C$1)))}
in a single cell returns
=SUM($A$2:C$2)*C1

But
{=INDEX($A$1:$Z$1,0,COLUMN(D$1)-COLUMN($A$1:C$1))}
entered across multiple cells will return an array of 3 values, hence
your formula does what you want it to if you enter it into multiple cells.

Jerry

 

[Jerry W. Lewis]

If the values in A1:Z1 can be calculated, perhaps you could move that
calculation into the convolution (I have successfully done this with
some convolutions).

Otherwise, I am fresh out of ideas on how to fix this up. My last idea
was to try to trick INDEX into returning an array
{=SUM(INDEX($A$1:$Z$1,1,{3,2,1}))}
is equivalent to =C1, my longshot was to use
{=SUM({0,0,0}+INDEX($A$1:$Z$1,1,{3,2,1}))}
but it is equivalent to =3*C1 instead of =SUM(A1:Z1).

Jerry