Results 1 to 12 of 12
Thread: Weighted average

20010506, 21:50 #1
 Join Date
 Mar 2001
 Posts
 11
 Thanks
 0
 Thanked 0 Times in 0 Posts
Weighted average
I need a weighted average of the numbers in the attached spreadsheet  I think I should be using SumProduct, but am not sure how to use it correctly.
Can anyone help.
Elisabeth

20010507, 00:16 #2
 Join Date
 Feb 2001
 Location
 Youngstown, Ohio, USA
 Posts
 705
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Weighted average
Try:
=SumProduct(A3.A30,B3.B30)
This will give you the equivalent to =A3*B3+A4*B4+...A30*B30
If you use named ranges, you could simplify it to =SumProduct(Value,Growth)
For your weighted average, my guess would be to just divide the SumProduct value by the count of Value? Or perhaps the sum of Value? This would mostly depend on your needs and what your data actually represents.

20010507, 01:15 #3
 Join Date
 Mar 2001
 Posts
 11
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Weighted average
David, thank you. Value = Share prices, Growth = Growth on those prices  so would it be divide by Sum of Value (or is it Count....)
Elisabeth

20010507, 18:33 #4
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: Weighted average
Elisabeth, the answer depends on which change you are measuring; average numerator growth by denominator value would be =SUMPRODUCT($A$3:$A$30,B3:B30)/SUM(A3:A30).
I don't know what your measurement goal is, but would it make sense to also have the units of whatever are being measured, like my hypothetical modification of your attachment?John ... I float in liquid gardens
UTC 7ąDS

20010507, 19:32 #5
 Join Date
 Jan 2001
 Location
 Baltimore, MD, Maryland, USA
 Posts
 254
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Weighted average
With weighted averages, you always divide by the sum of the weighting factor to get the weighted mean. In this case, since you are weighting the growth by the "value" of the stock, then you would divide by the sum of the values.
Stephen

20010507, 21:39 #6
 Join Date
 Mar 2001
 Posts
 11
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Weighted average
John, thank you for doing that  my original numbers were share price at time of buying/topup and growth compared with price today. (I wish I had a worthwile number of these shares  especially at the original price!!!)
So, where you have put "units" I should enter the number of shares in each case and I would then have a true weighted average?
Your help is much appreciated.
Elisabeth

20010507, 22:19 #7
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: Weighted average
To your question: yes to units of shares, probably no to true weighted average. I track my measly portfolio in Excel more like this (there's a whole lot more, like acquisition date, Ticker code, etc.):
Units  Basis  Market  Gain/<Loss>
Where Basis is cost for US IRS purposes, and (Paper) Gain/<Loss> is (Market value minus Basis). Then the weighted average percent gain or loss will be
=sumproduct(units_column, gain/<loss>_column)/sumproduct(units_column, basis_column)
Alternatively you could calculate the gain/<loss> as a percent of basis (cost), in the form =1/(basis/value)1, then use
=sumproduct(units_column, percent_gain/<loss>_column)/sum(units_column)
I may not understand your purpose, but calculating sumproduct(value,gain)/sum(value) doesn't report anything useful to my way of looking at things.
... but it doesn't matter to me because I'm always <img src=/S/broke.gif border=0 alt=broke width=31 height=23>. At least you look like you're ahead of the market!John ... I float in liquid gardens
UTC 7ąDS

20010507, 22:39 #8
 Join Date
 Mar 2001
 Posts
 11
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Weighted average
John, thank you, I will need to spend some time with the info you have given me to understand it. (I do have a share register which covers all that the Aussie tax dept needs to know each year.)
We read of Fund Managers returning growth of xx.x% and the point of my exercise is to see how my portfolio is performing overall  sounds grand doesn't it  pity it's not lots of dollars, but maybe one day.....
So is the weighted average in the s/s you set up OK for this purpose?
Elisabeth

20010507, 23:05 #9
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: Weighted average
No, it should work more like the attached. (I did a couple of things different ways for fun  yellow is probably easiest.)
So how much BHP do you have? <img src=/S/wink.gif border=0 alt=wink width=15 height=15> See my profile.John ... I float in liquid gardens
UTC 7ąDS

20010507, 23:07 #10
 Join Date
 Dec 2000
 Location
 Queanbeyan, New South Wales, Australia
 Posts
 3,730
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Weighted average
Elisabeth,
This probably won't do anything new for you there is an Aussie share portfolio spreadsheet at [urlhttp://pmacgowan.freeyellow.com/Index.html[/url]. The free trial version is limited to 5 shares; the full version is $AU25.
The nice thing about it is that it downloads share prices directly from the ASX web site (20 minute delayed prices) into the spreadsheet.
I bought it just to see how he downloads at least at that stage, the source code was a part of the deal. I use the techniques for different purposes.Subway Belconnen home of the Signboard to make you smile. Get (almost) daily updates follow SubwayBelconnen on Twitter.

20010507, 23:47 #11
 Join Date
 Mar 2001
 Posts
 11
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Weighted average
Geoff, thanks for this  I will certainly have a look when I get home.
(Wouldn't a "Shares" lounge be cool!)
Elisabeth

20010507, 23:55 #12
 Join Date
 Dec 2000
 Location
 Queanbeyan, New South Wales, Australia
 Posts
 3,730
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Weighted average
I did have a "general investments" discussion forum for a little while, with some emphasis on shares and options because property investment forums are wellcovered in Australia. But it was a Front Page forum hosted on geocities and when it crashed, I could not work out (with my small level of expertise) how to get it going again.
If I had the time and ability, I'd use an Amdragon board and try to resurrect it. Maybe one day.Subway Belconnen home of the Signboard to make you smile. Get (almost) daily updates follow SubwayBelconnen on Twitter.