Summing two columns

[Guest]

Hi

I have values in two columns, A and B in a single worksheet. I want to multiply values in column A with the inverse order of values in column B and add them and put the results in column C. For example at C1, I want to have A1*B1, at C2, A2*B1+A1*B1, at C3, A3*B1+A2*B2+A1*B3 and at C4, A4*B1+A3*B2+A2*B3+A1*B4, etc. How can I accomplish this job with SUMPRODUCT or VBA and fill that column automatically. Any help is welcome.

Thanks

 

[Bernard V Liengme]

HI,
Here is an inelegant subroutine
Option Base 1
Function doit(N, rangeA As Range, rangeB As Range)
Dim tempA(10), tempB(10)
j = 1
For Each cell In rangeA
tempA(j) = cell
j = j + 1
Next
j = 1
For Each cell In rangeB
tempB(j) = cell
j = j + 1
Next
mysum = 0
For j = 1 To N
mysum = mysum + tempA(j) * tempB(N - j + 1)
Next
doit = mysum
End Function

In C 1 enter =doit(ROW(),$A$1:INDIRECT("A"&ROW()),$B$1:INDIRECT("B"&ROW()))
and copy down the column as far as makes sense.
Bernard

Hi,

I have values in two columns, A and B in a single worksheet. I want to

multiply values in column A with the inverse order of values in column B and
add them and put the results in column C. For example at C1, I want to have
A1*B1, at C2, A2*B1+A1*B1, at C3, A3*B1+A2*B2+A1*B3 and at C4,
A4*B1+A3*B2+A2*B3+A1*B4, etc. How can I accomplish this job with SUMPRODUCT
or VBA and fill that column automatically. Any help is welcome.