Need a formula to multiply arrays

[sriman]

I have values in columns A1, B1, C1.... 120 columns. Then I have values in A2, B2, C2, .... for 120 columns

I need to compute the following in row 3...

A3=A2*A1, B3=B2*A1+A2*B1, C3=C2*A1+A2*C1+B2*B1......

I want to do this w/o having to edit each cell in row 3

Any help will be appreciated.

 

[shg]

What's in D3?

 

[sriman]

D3 = D2*A1+A2*D1+B2*C1+C2*B1

and

E3 = E2*A1+A2*E1+B2*D1+C2*C1+D2*B1

The entire row three is built this way and I need to do this for 120 columns

 

[sriman]

I posted as a reply to my original thread.

 

[shg]

That's a messier formula than I want to figure out, but a UDF is simple:

Function m(r As Range) As Double
    Dim i           As Long
    Dim nCol        As Long
 
    nCol = r.Columns.Count
 
    For i = 1 To nCol \ 2
        m = r(1, i).Value2 * r(2, nCol - i + 1).Value2 + _
            r(2, i).Value2 * r(1, nCol - i + 1).Value2 + m
    Next i
    If nCol Mod 2 = 1 Then m = m + r(1, i).Value2 * r(2, nCol - i + 1).Value2
End Function

There's no error checking.

In A3 and copy across,

=m($A$1:A2)

 

[sriman]

this is great! It seems to work when the values are in A1, A2, A3, B1, B2, B3... etc I was wondering if this could be tweaked so the rows need not be sequential. Meaning the values in A1:A(x) and B1:B(x) can be separated from each other?

 

[shg]

Function o(rA As Range, rB As Range) As Double
    Dim i           As Long
    Dim nCol        As Long
 
    nCol = rA.Cells.Count
 
    For i = 1 To nCol \ 2
        o = rA(i).Value2 * rB(nCol - i + 1).Value2 + _
            rB(i).Value2 * rA(nCol - i + 1).Value2 + o
    Next i
    If nCol Mod 2 = 1 Then o = o + rA(i).Value2 * rB(nCol - i + 1).Value2
End Function

Still no error checking.

 

[sriman]

worked! Thank you so much. Love this forum

 

[shg]

You're welcome, glad it worked for you.