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

Johnny C

Well-known Member
Joined
Nov 7, 2006
Messages
1,003
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
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Johnny C

Well-known Member
Joined
Nov 7, 2006
Messages
1,003
Just in case anybody wants some VBA to do it...

Code:
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

MrExcel MVP
Joined
Jun 9, 2006
Messages
1,802
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

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,043
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Jun 9, 2006
Messages
1,802

ADVERTISEMENT

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

Johnny C

Well-known Member
Joined
Nov 7, 2006
Messages
1,003
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,079
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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:
Code:
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. :)
 
Last edited:

Johnny C

Well-known Member
Joined
Nov 7, 2006
Messages
1,003
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,079
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

Well-known Member
Joined
Nov 7, 2006
Messages
1,003
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 :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,672
Messages
5,597,488
Members
414,146
Latest member
marginmakerb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top