1-2-3 Weighted Average

Aaron · Apr 17th, 2002, 03:31 PM

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. · Apr 17th, 2002, 03:34 PM

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

Jay Petrulis · Apr 17th, 2002, 03:38 PM

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 · Apr 17th, 2002, 03:43 PM

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 · Apr 17th, 2002, 03:48 PM

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

Apr 17th, 2002, 03:43 PM	#4
Aaron Board Regular Join Date: Feb 2002 Location: Texas Posts: 233	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? __________________ Excel 2002 XP SP1

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 17th, 2002, 03:31 PM	#1
Aaron Board Regular Join Date: Feb 2002 Location: Texas Posts: 233	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

Apr 17th, 2002, 03:34 PM	#2
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	=SUMPRODUCT(E2:E6,F2:F6)/SUM(F2:F6)

Apr 17th, 2002, 03:38 PM	#3
Jay Petrulis MrExcel MVP Join Date: Mar 2002 Location: Chicago, IL USA Posts: 2,042	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

Apr 17th, 2002, 03:48 PM	#5
Jay Petrulis MrExcel MVP Join Date: Mar 2002 Location: Chicago, IL USA Posts: 2,042	That is correct. Excel does not have a weighted average function.