|
Convolution
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 |
Convolution
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 |
Convolution
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 |
Convolution
I need the "convolution"
As a side note, Excel has a Fourier program in the Analysis toolkpak that can be used for Convolution. However, the example is a little confusing to me. I would suggest giving a smaller example, say two vectors of size 4, and the expected solution. Dana DeLouis Neal Carron wrote: 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 |
Convolution
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" 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 |
Convolution
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 |
| All times are GMT +1. The time now is 12:37 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com