Pivot table: How to calculate weighted averages?

R

ricardo

Each row of my data represents a bond with amount, interest rate and
yearly payment of interest. First column shows at which bank I have this
bond.

Bank1 - Bond1 - Interest Rate: 5.00% - Amount: 1000 - Payment: 50
Bank1 - Bond2 - Interest Rate: 2.00% - Amount: 100 - Payment: 2
Bank2 - Bond3 - Interest Rate: 4.00% - Amount: 2000 - Payment: 80
Bank2 - Bond4 - Interest Rate: 8.00% - Amount: 1000 - Payment: 80

I want to create a pivot table which shows for each bank the weigthed
average of interest rate and sum of payments.

Bank1: 4.72% - 52
Bank2: 5.33% - 160
Total: 5.17% - 212

4.72 for Bank1 is calculated from (50+2) / (1000+100)

Showing sum of payments (52 and 160) is no problem.
But how to show weighted average? I tried "formulas"-"calculated fields" but
with no luck.

Any ideas? The problem should not sound that complicated....Thank you
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

pivot table help 2
How to Calculate a Weighted Average in a Pivot Table 0
How to do UK mortgage math? 1
How do I calculate a weighted average in a pivot table? 0
Need suggestions on layout to report/pivot 5
Pivot Table Calculate Field 1
How do I calculate a weighted average and exclude the empthy cells 2
Weighted average in a pivot table 1

Top