Results 1 to 12 of 12
  1. #1
    New Lounger
    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
    Attached Files Attached Files

  2. #2
    5 Star Lounger
    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.

  3. #3
    New Lounger
    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

  4. #4
    Uranium Lounger
    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?
    Attached Files Attached Files
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    3 Star Lounger
    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

  6. #6
    New Lounger
    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/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

  7. #7
    Uranium Lounger
    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

  8. #8
    New Lounger
    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

  9. #9
    Uranium Lounger
    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.
    Attached Files Attached Files
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    Platinum Lounger
    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.

  11. #11
    New Lounger
    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

  12. #12
    Platinum Lounger
    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 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.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •