Weighted average

[Elisabeth]

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

[d_rasley]

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.

[Elisabeth]

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

[JohnBF]

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?

[epic60sman]

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

[Elisabeth]

John, thank you for doing that - my original numbers were share price at time of buying/top-up 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

[JohnBF]

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!

[Elisabeth]

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

[JohnBF]

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.

[gwhitfield]

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.

[Elisabeth]

Geoff, thanks for this - I will certainly have a look when I get home.

(Wouldn't a "Shares" lounge be cool!)

Elisabeth

[gwhitfield]

I did have a "general investments" discussion forum for a little while, with some emphasis on shares and options because property investment forums are well-covered 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.