Trying to do a shifting multiplication and summation
Posted by Will on April 22, 2001 5:35 PM
Well, i'm sure this is pretty easy to do but I'm pretty stupid when it comes to excel. I tried looking in the help files and was just further confused. Pretty much what i'm trying to do is :
c[k]=a[k]*b[1]+a[k-1]*b[2]+a[k-2]*b[3]+..a[1]*b[k] where k is the row number in c where i'm storing the sum
obviously excel doesn't use brackets so like c[k] would be Ck or column C row k.
So like for example it should give
c1=a1*b1
c2=a2*b1+a1*b2
c3=a3*b1+a2*b2+a1*b3
any help would be appreciated, thanks.
and so on.
Posted by Steve W on April 22, 2001 5:49 PM
hi will
Are you trying to get c1 to equal a1+b1 if so enter in c1
=a1*b1
enter in c2
=a2*b2+a1*b1
enter in c3
=a3*b1+a2*b2+a1*b3
I'm not sure if this is whatyou needed I didn't understand what you were looking for in your questine.
If this wasn't what you were looking for post what your trying to do and maybe I can help.
steve
Posted by will on April 22, 2001 5:51 PM
that is exactly what i'm trying to do
Posted by Will on April 22, 2001 5:54 PM
sorry if i made it confusing, I had it in written down in c++ in a forloop and was using arrays so that is what the brackets came from. I would just type it in per cell but it needs to be able to handle 1000 cells and that would take forever, also thanks for the quick response.
Posted by Steve W on April 22, 2001 5:59 PM
Not sure if you need more help, but you may be able to do a for loop in VBa or maybe another type of fofmula.
steve
Posted by will on April 22, 2001 6:10 PM
well that is the problem, i don't know vb. I'm sure it isn't that difficult though. I mean i know what i need to do, a forloop and such, i just don't know how to implement it for use in excel via a macro or whatever. Thats pretty much what i'm asking
Posted by Mark W. on April 22, 2001 6:40 PM
Here's the Excel formula...
...that accomplishes your task:
{=SUM($A$1:A1*TRANSPOSE($B$1:B1)*(ROWS($A$1:A1)+1-ROW($A$1:A1)=TRANSPOSE(ROW($A$1:A1))))}
Enter this formula into cell C1 and copy down.
Please note that this is an array formula which
must be entered using Control+Shift+Enter. The
braces, {}, are supplied by Excel -- not entered
by you.
Posted by Steve W on April 22, 2001 6:46 PM
Will I've been doing VBa for about 5 months but for-loops is something I haven't learned. I know one thing though, you have come to the right place there are alot of vary talented people on this site who can help you.
If you don't get the answere to your problem try reposting it asking for some help with a loop.
steve
Posted by Mark W. on April 22, 2001 6:49 PM
See...
the formula I posted above.
