Average of Sumproducts

[jholme9]

Hi all. I've just stumbled on this forum and am hoping for some help.

My goal is to obtain the average of sumproducts where I have a constant array as the first input of the sumproduct and several thousand rows as the second input of the sumproduct formula. As a simple example, let's assume that I have A1:C1 as my constant. I need the average of each of the sumproducts of A1:C1*A2:C2, A1:C1*A3:C3, ... ,An:Cn. As an added bit of complexity, I'm hoping to do these calculations in one cell, because this process will need to happen about 20,000 times.

I'm assuming I need an array formula for this, but I haven't been able to incorporate both the sumproduct AND the average component... just one or the other.

Any help is appreciated. Thanks!
John

 

[Marcelo Branco]

Not quite sure if i understood, but, assuming your data in A1:C10, maybe this array-formula

=AVERAGE($A$1*A2:A10+$B$1*B2:B10+$C$1*C2:C10)

Ctrl+Shift+Enter

M.

 

[mikerickson]

I'm thinking something like (untested)

=SUM(MMULT(A1:C1,TRANSPOSE(A2:Cn)))/(n-1)

 

[jholme9]

You guys are awesome. Those actually both worked! I guess I was trying to come at it in too complex a manner.

Thanks so much for your help!

John

 

[barry houdini]

This one doesn't need CSE

=SUMPRODUCT(A1:C1*A2:C10)/ROWS(A2:C10)

 

[Marcelo Branco]

John,

You are welcome and tks for the feedback

M.

 

[Marcelo Branco]

This one doesn't need CSE

=SUMPRODUCT(A1:C1*A2:C10)/ROWS(A2:C10)

Nice!