Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Oct 2004
    Location
    Minnesota, USA
    Posts
    151
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Caculating Weighted Average Excel 2010

    I should know how to do this, but...

    I have a calculation in a spreadsheet that is balance / security = loan to security as a %.

    My boss wants a weighted average of those loan to security %s for each state. There can be hundreds of accounts per state.

    How do I do the calculation? I haven't had to do a weighted average of the results of a calculation before.

    Thanks for any help you can provide.

    Nancy

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    The weighted percent would be the sum of the balances divided by the sum of the security. You could do this as a calculated field in a pivot table if it helps?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    2 Star Lounger
    Join Date
    Oct 2004
    Location
    Minnesota, USA
    Posts
    151
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Isn't that just the average? Don't I need to know, for example, how much balance there is for each resulting percentage?

    For whatever reason, weighted averages mess me up.

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Nope - have a look at the attached. The straight average just takes the average of the percentages. The weighted one divides one total by the other so that you get the total ratio of all balances to all securities. (hence the effect of the 4% on the largest loan has more of an effect on the result).
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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