Sum the product of every 2 cells in a row (sum of A1*B1, C1*D1, E1*F1...to end) in Google Sheets

Question

I want to find the average share price in B4 based on the table of shares purchased and price seen to the right. Each meeting we will buy a few shares at different prices. I need a formula for B4;B8 that will basically do the function =sum(e4*f4,g4*h4,i4*j4...to end)/c4. Obviously I can do it manually with the given formula but I was hoping there would be an easier way to update the average share price when the # of new shares bought and the purchased price are entered after future meetings. Any idea if/how this can be done?

Answer 1

In B4 use,

=sumproduct((D4:I4)*(E4:J4)*mod(column(D:I), 2))/C4

Fill or copy down.

    

Answer 2

With data in the first row only, in A2 enter:

=IF(ISEVEN(COLUMN()),0,INDEX($1:$1,1,COLUMN())*INDEX($1:$1,1,COLUMN()+1))

and copy across until data ends in the top row.

Then use:

=SUM(2:2)