CODEWITHSUNDEEP
excelexcel-formula
cherrun
cherrun

Reputation: 2142

Multiply row with above row and obtain an average

I have an excel table with lets say 500 columns and 2 rows. I want to multiply each cell in the second row with the cell above, in the first row. Then get the average of the sum.

Example:

3 4 1 2 5
1 3 3 5 1

Solution would be: (3*1 + 4*3 + 1*3 + 2*5 + 5*1) / number of columns

What would the command look like in Excel?

Upvotes: 2

Views: 2319

Answers (2)

chris neilsen
chris neilsen

Reputation: 53135

For data in rows 1 and 2 use

=SUMPRODUCT(1:1,2:2)/COUNT(1:1)

Upvotes: 3

McGarnagle
McGarnagle

Reputation: 102743

Here's what I'd do:

1) In the third row first column enter "=A1+A2". Copy/paste that across the entire row to propagate.

2) In another column somewhere enter "AVERAGE(C1:C500)".

That cell in #2 should give you the answer.

Upvotes: 1

Related Questions