Multiplying rows and cloumns

**skochar** · 11-17-2006, 10:50 AM

Hi how do i multiply rows & colums & have the results in the row

i.e.

6 1 2 3 4 5
7
8
9
10

I want to multiply 1&6, 7&2 etc and want the results in the cell below 1 2 3 4 5

**oldchippy** · 11-17-2006, 10:58 AM

With 1-5 in cells B1:F1, and 6-10 in cell A2:A6, put this in B2

=B$1*$A2, then drag down and across your range

**skochar** · 11-17-2006, 11:08 AM

Originally Posted by oldchippy

With 1-5 in cells B1:F1, and 6-10 in cell A2:A6, put this in B2

=B$1*$A2, then drag down and across your range

Hi,

Thanks for the prompt reply but it locks one cell, i want to multiply the second cell in the row with the second cell in the column, similarly 3rd with 3rd etc. without manually doing it one by one

**oldchippy** · 11-17-2006, 11:19 AM

Originally Posted by skochar

Hi,

Thanks for the prompt reply but it locks one cell, i want to multiply the second cell in the row with the second cell in the column, similarly 3rd with 3rd etc. without manually doing it one by one

Just to confirm what you want, the numbers 612345 are in one cell A1?
The others in A2 down.
Then multiply 6*1, 7*2, 8*3, 9*4, 10*5.
Do you want the result of these in one cell?

**skochar** · 11-17-2006, 11:27 AM

1 2 3 4 5
6 6 14 24 36 50
7
8
9
10

I want the result 6-14-24-36-50 (which is below 1-2-3-4-5) but automated because I need to work with huge tables

**oldchippy** · 11-17-2006, 11:50 AM

Try this in B2

=B$1*A2 drag over to the right, then select cells B2:F2, and drag formula down. Is that what you are after?

**Mark@Work** · 11-17-2006, 12:15 PM

I seem to be posting a lot of offset solutions today!

B2 =OFFSET($A$1,COLUMN()-1,0)*A1

copy across.

Mark.

**Mark@Work** · 11-17-2006, 12:20 PM

Slightly more esoteric using the excel array facilities.

select B2:F2

type in =A1:E1*TRANSPOSE(A2:A6)

press ctrl shift enter

A nice trick, but perhaps a bit more awkward to adapt to the general case of your "large array".

Mark.