Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
Performing a convolution in a worksheet
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. |
#2
|
|||
|
|||
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 peter dmz wrote: 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. |
#3
|
|||
|
|||
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 Jerry W. Lewis wrote: 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 peter dmz wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to search for and display data in another worksheet | Excel Worksheet Functions | |||
Search/Match between 2 x separate Worksheets and populate result in third worksheet | Excel Discussion (Misc queries) | |||
Copy from worksheet to another x times | Excel Discussion (Misc queries) | |||
Weekly Transaction Processing | Excel Worksheet Functions | |||
Indirect reference from one worksheet to another | Excel Worksheet Functions |