Multiply multiple columns

julienmcmahon · Nov 12, 2018

HI - I need to multiply three columns. Each value by each value in all columns:
A1*B1*C1
A1*B2*C1
A1*B3*C1...
A2*B1*C1
A2*B2*C1
A2*B3*C1...all the way through to
A6*B6*C6

	A	B	C
1	1	0.5	0.1
2	5	1	0.5
3	15	2	1
4	25	3	3
5	50	6	6
6	100	10	10

alansidman · Nov 13, 2018

In D1 type =A1 * B1 * C1 and then copy down.

julienmcmahon · Nov 16, 2018

Thanks Alan
But not what required.
Jules

sandy666 · Nov 16, 2018

is that what you want?

Column1	Column2	Column3	Multiplication
1	0.5	0.1	0.05
5	1	0.5	0.25
15	2	1	0.5
25	3	3	1.5
50	6	6	3
100	10	10	5
			0.1
			0.5
			1
			3
			6
			10
			0.2
			1
			2
			6
			12
			20
			0.3
			1.5
			3
			9
			18
			30
			0.6
			3
			6
			18
			36
			60
			1
			5
			10
			30
			60
			100
			0.25
			1.25
			2.5
			7.5
			15
			25
			0.5
			2.5
			5
			15
			30
			50
			1
			5
			10
			30
			60
			100
			1.5
			7.5
			15
			45
			90
			150
			3
			15
			30
			90
			180
			300
			5
			25
			50
			150
			300
			500
			0.75
			3.75
			7.5
			22.5
			45
			75
			1.5
			7.5
			15
			45
			90
			150
			3
			15
			30
			90
			180
			300
			4.5
			22.5
			45
			135
			270
			450
			9
			45
			90
			270
			540
			900
			15
			75
			150
			450
			900
			1500
			1.25
			6.25
			12.5
			37.5
			75
			125
			2.5
			12.5
			25
			75
			150
			250
			5
			25
			50
			150
			300
			500
			7.5
			37.5
			75
			225
			450
			750
			15
			75
			150
			450
			900
			1500
			25
			125
			250
			750
			1500
			2500
			2.5
			12.5
			25
			75
			150
			250
			5
			25
			50
			150
			300
			500
			10
			50
			100
			300
			600
			1000
			15
			75
			150
			450
			900
			1500
			30
			150
			300
			900
			1800
			3000
			50
			250
			500
			1500
			3000
			5000
			5
			25
			50
			150
			300
			500
			10
			50
			100
			300
			600
			1000
			20
			100
			200
			600
			1200
			2000
			30
			150
			300
			900
			1800
			3000
			60
			300
			600
			1800
			3600
			6000
			100
			500
			1000
			3000
			6000
			10000

julienmcmahon · Nov 17, 2018

Yes sandy666. But it's the formula I'm looking for
Cheers
Jules

sandy666 · Nov 17, 2018

I don't know how to do it with formula but I know how to do it with PowerQuery

use cartesian for three columns

Code:

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table3),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column2"}, {"Column2"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom", each Table4),
    #"Expanded Custom1" = Table.ExpandTableColumn(#"Added Custom1", "Custom", {"Column3"}, {"Column3"}),
    #"Inserted Multiplication" = Table.AddColumn(#"Expanded Custom1", "Multiplication", each List.Product({[Column1], [Column2], [Column3]}), type number),
    #"Removed Other Columns" = Table.SelectColumns(#"Inserted Multiplication",{"Multiplication"})
in
    #"Removed Other Columns"[/SIZE]

example excel file

Fluff · Nov 17, 2018

I've no idea how to do this via formulae, but here is a macro if you're interested.

Code:

Sub Multiplycolumns()
   Dim ary As Variant
   Dim i As Long, j As Long, k As Long, l As Long
   ary = Range("A1", Range("A" & Rows.Count).End(xlUp).Offset(, 2)).Value
   For i = 1 To UBound(ary)
      For j = 1 To UBound(ary)
         For k = 1 To UBound(ary)
            l = l + 1
            Range("D" & l).Value = ary(i, 1) * ary(j, 2) * ary(k, 3)
         Next k
      Next j
   Next i
End Sub

XOR LX · Nov 17, 2018

Hi,

Does the order of returns matter?

This array formula** will return them from smallest to largest:

=SMALL(SMALL(A$1:A$6*TRANSPOSE(B$1:B$6),COLUMN(INDEX(1:1,1):INDEX(1:1,ROWS(A$1:B$6)^2)))*C$1:C$6,ROWS(A$1:A1))

and copied down.

Regards

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Multiply multiple columns

julienmcmahon

New Member

Excel Facts

alansidman

Well-known Member

julienmcmahon

New Member

sandy666

Banned - Rules violations

julienmcmahon

New Member

sandy666

Banned - Rules violations

Fluff

MrExcel MVP, Moderator

XOR LX

Well-known Member

Similar threads

Forum statistics

Share this page

Multiply multiple columns

New Member

Excel Facts

Well-known Member

New Member

Banned - Rules violations

New Member

Banned - Rules violations

MrExcel MVP, Moderator

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock