is there a quick way to multiply a column by an equally sized row?

[Johnny C]

I have a set of columns with data in that relates to rows above. So, can I multiply a set of cells in a row by their transpose in a column?

This is what I'm trying to achieve (in cell C12) :-
=SUMPRODUCT(OFFSET($AD12,0,0,ROW()-2,1),C$2:C11)
10 cells across by 10 cells down.

I would prefer not to use array formulae if I can, I'll do a VBA function if there's no non-CSE way

Cheers

 

[Johnny C]

Just in case anybody wants some VBA to do it...

Function XMultiply(RowInput As Range, ColInput As Range) As Long

XMultiply = 0
RowTopRow = RowInput.Row
RowTopCol = RowInput.Column
ColTopRow = ColInput.Row
ColTopCol = ColInput.Column

If RowInput.Rows.Count = ColInput.Columns.Count Then
    For Counter = 1 To RowInput.Rows.Count
        On Error Resume Next
        Temp = (Cells(RowTopRow, RowTopCol).Offset(Counter - 1, 0).Value * Cells(ColTopRow, ColTopCol).Offset(0, Counter - 1).Value)
        If Err.Number <> 0 Then Temp = 0
        On Error GoTo 0
        XMultiply = XMultiply + Temp
    Next Counter
End If

 

[Cindy Ellis]

You should be able to use the MMULT function. It is an array formula (appropriately), and must be confirmed with Ctrl-Shift-Enter. To use it, first select an area the size of the output (if you have 5 rows in one input array and 5 columns in the other input array, you need to pre-select a 5x5 range).
With that range selected, type the formula MMULT(column_input_range,row_input_range). Instead of hitting "return" at the end, press CTRL-Shift-Enter, all together. Excel will add curly braces around the formula, and will populate the selected range with the column x row results.
Hope that helps.

 

[StephenCrump]

You should be able to use the MMULT function.

... and given it's MMULT of a row vector and a column vector, i.e. one cell output, it will meet OP's requirement for a non-array entered formula.

 

[Cindy Ellis]

Oops, I obviously didn't fully read the op's post. It specifies a 10x10 output I think.

 

[Johnny C]

Thanks.

It's not a true matrix - it's 1D vectors of various lengths, the cell refs in my example are the results of offset functions which would have confused matters unless I'd enforced a fixed format which is tricky, that's why I can't use a CSE function

I tried MMULT and couldn't get it to work. I'll look at it again

 

[RoryA]

Can you post a sample of inputs and what you want as outputs (and where)?

Also, can you elaborate on why you can't CSE - I didn't get that.

FWIW, I think your function could simply be:

Function XMultiply(RowInput As Range, ColInput As Range) As Double
    XMultiply = Application.SumProduct(RowInput, Application.Transpose(ColInput))
End Function

Also, FWIW, you can use a ridiculous construction like this:
=SUMPRODUCT(B2:J2,TRANSPOSE(INDEX(A3:A11,N(IF(1,INDEX(ROW(A3:A11)-MIN(ROW(A3:A11))+1,,))))))
per this page.

 

[Johnny C]

Cheers - that's what I wanted. Interesting article.

I didn't realise there was a TRANSPOSE function (either in VBA or Excel) - cheers

One of the reasons I can't use a CSE isn't that I have a "morbid fear of array formaulae" - but my contract at the moment is with a company where like most Finance departments they are just outright banned. This place is worst than most - even INDEX and MATCH are banned. Fortunately my stuff involves VBA so the rules don't usually apply, but for that non-VBA spreadsheet they do.

 

[RoryA]

but my contract at the moment is with a company where like most Finance departments they are just outright banned. This place is worst than most - even INDEX and MATCH are banned.

That is possibly the most ridiculous thing I have ever heard (and I have never come across any department where that was the case). Glad it's not my workplace!!

 

[Johnny C]

Lol Rory - me neither. There is one person who can record a macro and edit it. There are two people who can use index and match but aren't and screaming llowed to. A couple can use pivots. The Head of Finance is ripping his hair out, and has asked me to set up a 6 month course to *try* and drag them kicking into this century. Puts me in a great position, I've built some vba to reconcile large input files that they can't do without and can't support - roll on contract renewal negotiations