Lori,
I found your function works, thanks.
But I'll state the problem more clearly, as Dana requested.
Try a function (vector) with 4 points.
Start with the four columns i, t, f, and g.
Ignore Excel row numbers for now (row number will equal i+2).
i is an index, i=0,1,2,3.
t is time. t0 = 0 is the first time point. t1=t0+DT=DT. t2=t0+2*DT=2*DT.
t3=t0+3*DT=3*DT.
In general ti = t0+i*DT, i=0,1,2,3.
These times are specified by the user.
f is the function (vector) to be convolved with g.
The fi are specified. fi = f(t=ti): f0=f(t0). f1=f(t1). f2=f(t2), etc.
The gi are also given. gi = g(t=ti): g0=g(t0). g1=g(t1). g2=g(t2), etc.
The column "Con" is the convolution to be computed.
Coni is the value of the convolution at t=ti, i=0,1,2,3.
This table just defines notation. The number following t, f, and g (a
subscript) is the index i, not the row number.
Row number will equal i+2 if the column names row is row 1.
i t f g Con
0 t0 f0 g0 Con0
1 t1 f1 g1 Con1
2 t2 f2 g2 Con2
3 t3 f3 g3 Con3
The definition of the convolution in calculus is
Con(t) = Integral dt' from 0 to "inf" of f(t')*g(t-t'). For 0<t<"inf".
"inf" is some large time.
In discrete form,
Con(ti) = DT * { Sum on j from j=0 to j=3 of f(tj) * g(ti-tj) }
In the real problem, the maximum j (=3 here) will be a large integer.
Con is evaluated at the four times corresponding to i=0,1,2,3.
So, for i=0,1,2,3:
Con(ti) = DT * { f(t0)*g(ti-t0) + f(t1)*g(ti-t1) + f(t2)*g(ti-t2) +
f(t3)*g(ti-t3) }
Expressed in terms of the subscripts i and j,
Con0 = DT * { f0*g0 + f1*g(-1)+ f2*g(-2) + f3*g(-3) }
Con1 = DT * { f0*g1 + f1*g0 + f2*g(-1) + f3*g(-2) }
Con2 = DT * { f0*g2 + f1*g1 + f2*g0 + f3*g(-1) }
Con3 = DT * { f0*g3 + f1*g2 + f2*g1 + f3*g0 }
Since g(ti) is symmetric, g(ti) = g(-ti), and the required result is:
Con0 = DT * { f0*g0 + f1*g1 + f2*g2 + f3*g3 }
Con1 = DT * { f0*g1 + f1*g0 + f2*g1 + f3*g2 }
Con2 = DT * { f0*g2 + f1*g1 + f2*g0 + f3*g1 }
Con3 = DT * { f0*g3 + f1*g2 + f2*g1 + f3*g0 }
A worked example:
row
#
1 i t f g Con
2 0 t0=0 f0=0.15 g0=10.0 Con0=2.73
3 1 t1=1 f1=0.25 g1= 3.1 Con1=4.407
4 2 t2=2 f2=0.32 g2= 1.0 Con2=5.52
5 3 t3=3 f3=0.45 g3= 0.3 Con3=5.787
t is in, say, seconds, so in this example DT = 1 second.
The computation for, say, Con2 is
Con2 = (0.15)*(1.0) + (0.25)*(3.1) + (0.32)*(10.0) + (0.45)*(3.1) = 5.52
I need to express Coni, i=0,1,2,3 in Excel functions.
I believe your last formula was correct, but if you want to do more, please
use column names i, t, f, g, Con, instead of A,B,C,... . I will translate to
columns A,B,C,....
Again, row number = i+2, so all the above indexes can be replaced by 2 + the
above values to express them in terms of row number.
- Neal
Lori Miller said:
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 said:
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 said:
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
6 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
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
