1-2-3 Weighted Average

[Aaron]

In 1-2-3 If I type in the following formula @weightavg(e2..e6,f2..f6) I get a weighted average of the two columns. Is there a formula in Excel that does the same? Thanks in advance for your help. -asa

 

[Mark W.]

=SUMPRODUCT(E2:E6,F2:F6)/SUM(F2:F6)

 

[Jay Petrulis]

Hi,

No standard function, but you can create one easily

=SUMPRODUCT(Values, Weights)/Sum(Weights)
or in investment terms,
=SUMPRODUCT(Shares, Prices)/Sum(Shares)

note the above. It makes a big difference which range/array you put into the denominator, so be careful.

=SUMPRODUCT(A1:A5,B1:B5)/SUM(B1:B5)

is a specific example.

Bye,
Jay

 

[Aaron]

That gives me the answer I need. Thanks. But by the answer, there is no weighted average function, unless I wanted to write one correct?

 

[Jay Petrulis]

That is correct. Excel does *not* have a weighted average function.