Hi. I like the clever use of Lookup! Very nice!!

Dana DeLouis


Lori Miller wrote:
It's been a while since I've used this stuff. I think i see a little better
now.
With a similar set up to above but DT=1, try filling down from D2:

=SUMPRODUCT(B$2:B$101,LOOKUP(ABS(ROW(C$2:C$101)
-ROW(C2)),ROW(C$2:C$101)-ROW(C$2),C$2:C$101))

(I'm using LOOKUP instead as INDEX doesn't work well in array formulas.)
I guess you don't need values for (F*G)(i) outside your interval i=2,..,101?

"Neal Carron" wrote in message
...
Lori,
Thanks for reply.
I'll change notation so your A,B,C,D doesn't get confused with my A,B,C.
I need the convolution Fcon(t) of two functions F(t) and G(t).
G(t) is symmetric: G(-t) = G(t).
My columns have N=100 rows.
Time is in column T2:T101.
For simplicity let DT = 1 so it can be ignored.
Then I need
Fcon(i) = {Sum on j from 2 to 101} of F(j) * G(i-j)

I neglected to say the sum is always over all times.

For Fcon(i), your sum
=SUMPRODUCT(B$2:B2,LOOKUP(A2-A$2:A2,A$2:A2,C$2:C2)) [then 2 -- 3, 4,
... ]
is a sum of rows 2:i only. It should always sum over 2:101. The first
array
should always be B$2:B$101.

Even at that I don't see how LOOKUP gets the other required vector in the
SUMPRODUCT.

For each i the second vector must be G(i-2), G(i-3), ... G(i-101).
[ Due to symmetry the row (i-46), e.g., may be replaced by ABS(i-46), or
2+ABS(i-46) ]

Can the ROW function or the INDEX function be used somehow?



"Lori Miller" wrote:

Try setting up your values as below, starting at t=0, then fill down from
D2:

=SUMPRODUCT(B$2:B2,LOOKUP(A2-A$2:A2,A$2:A2,C$2:C2))*DT

eg DT=2 and data range A1:D6 contains:

t A B C
0 2 1 4
2 3 3 18
4 4 4 42
6 3 5 74
8 5 6 114


"Neal Carron" <Neal wrote in message
...
This is rather mathematical.
I need the "convolution" of two columns of numbers, which will be a
third
column of the same length.
It is the discrete counterpart of the common continuous convolution
C(t)
of
two functions A(t) and B(t):
C(t) = Integral dt' A(t')*B(t-t').
B(x) is symmetric, B(-x) = B(x).
So I have, say, the columns A1:A100 and B1:B100, representing functions
of
time from t1 ... to t100 at equally spaced times.
Column C is to be the convolution of these two.
Replace the integral by a sum. dt' becomes the constant DT = t2 - t1.
Then C(t) at time t=ti is cell Ci.
Ci = DT * sum_on_j of A(j) * B(i-j). , for each i=1,2,..., 100.
How do I express this sum in terms of Excel functions?
Thanks,
- Neal