What is the problem in this formula

[Gerhard]

Hallo ladies and gentle men,

I have two columns, A and B. I want to have a third
column, C, by multiplying column A and B in an inverse
order. That is to mean C1=A1*B1, C2=A2*B1+A1*B2,
C3=A3*B1+A2*B2+A1*B3, C4=A4*B1+A3*B2+A2*B3+A1*B4, etc. One
friend in this forum provided me the following subroutine.
I just copied it, paste it in VBE of the sheet I am
working on, and saved the workbook. When I want to do the
calculation in C1 by pasting the "doit" formula given
below, what I get is an error (#Name). What is the
problem? Please see the subroutine and the formula and
help out. Is there a non-macro version of solving this
problem?

Thanks

The subroutine is
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

and the formula is

=doit(ROW(),$A$1:INDIRECT("A"&ROW()),$B$1:INDIRECT("B"&ROW
()))

 

[J.E. McGimpsey]

The first problem appears to be that you pasted the function into a
sheet module rather than a regular code module. See

http://www.mcgimpsey.com/excel/modules.html

I would also rewrite the function a bit:

Public Function InverseMultiplyAndAdd( _
rRange As Range) As Variant
Dim vArr1 As Variant
Dim vArr2 As Variant
Dim i As Long
Dim nrows As Long
Dim temp As Double
With rRange
If .Columns.Count <> 2 Or .Areas.Count > 1 Then
InverseMultiplyAndAdd = CVErr(xlErrRef)
ElseIf Application.Count(.Cells) <> .Count Then
InverseMultiplyAndAdd = CVErr(xlErrNum)
Else
vArr1 = .Columns(1).Cells.Value
vArr2 = .Columns(2).Cells.Value
nrows = .Rows.Count
If nrows = 1 Then
InverseMultiplyAndAdd = vArr1 * vArr2
Else
For i = 1 To nrows
temp = temp + vArr1(i, 1) * _
vArr2(nrows - i + 1, 1)
Next i
InverseMultiplyAndAdd = temp
End If
End If
End With
End Function

call it like this:

C1: = InverseMultiplyAndAdd(A$1:B1)

and copy down.

 

[Leo Heuser]

Hallo Gerhard

For a non-macro solution try this array formula
entered in C1. Not a beauty, but it will do the job

=MMULT(TRANSPOSE(OFFSET($B$1,,,MIN(ROW()-ROW($C$1))+1)),
MMULT(N((MIN(ROW(OFFSET($A$1,,,MIN(ROW()-ROW($C$1))+1)))+
MAX(ROW(OFFSET($A$1,,,MIN(ROW()-ROW($C$1))+1)))-
ROW(OFFSET($A$1,,,MIN(ROW()-ROW($C$1))+1)))=
TRANSPOSE(ROW(OFFSET($A$1,,,MIN(ROW()-ROW($C$1))+1)))),
OFFSET($A$1,,,MIN(ROW()-ROW($C$1))+1)))

The formula must be array-entered, i.e. with <Shift><Ctrl><Enter>
instead of <Enter>, also if edited later. If done properly, Excel will
display the formula in the formula bar enclosed in curly brackets { }
Don't enter these brackets yourself.
Copy C1 down with the fill handle (the little square in the lower right
corner of the cell)

 

[Leo Heuser]

Or, with one function call less
(still array-entered) :

=SUM(OFFSET($B$1,,,MIN(ROW()-ROW($C$1))+1)*
MMULT(N((MIN(ROW(OFFSET($A$1,,,MIN(ROW()-ROW($C$1))+1)))+
MAX(ROW(OFFSET($A$1,,,MIN(ROW()-ROW($C$1))+1)))-
ROW(OFFSET($A$1,,,MIN(ROW()-ROW($C$1))+1)))=
TRANSPOSE(ROW(OFFSET($A$1,,,MIN(ROW()-ROW($C$1))+1)))),
OFFSET($A$1,,,MIN(ROW()-ROW($C$1))+1)))

LeoH

 

[Gerhard]

Thank you everybody,

McGimpsey's code and Leo Heuser non-macro solutions worked
well and good. Thanks again.

 

[Guest]

Thank you LeoH, Both formulas worked well and good.

Gerhard

 

[Leo Heuser]

You're welcome, Gerhard.
I appreciate your feedback.