Thread: Caculating Weighted Average Excel 2010

1. 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.

Nancy

2. 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?

3. 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. 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).

Posting Permissions

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