Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Calculate a Weighted Average in a Pivot Table
The below will show you how to calculate a weighted average for a
group of data in 3 simple steps. In this case, a weighted average interest rate based on a prime or subprime borrower. Given data: Credit Type Borr UPB IntRate Prime Kelly $20,000 6.875 Prime Kline $68,000 7.5 Prime Stephens $450,000 6.5 Prime Jordan $784,000 6.125 Subprime Smith $125,000 8 Subprime Westerfield $200,000 7.5 Subprime Ross $312,000 7 Subprime Gerry $615,000 7.25 STEP 1: Add column to your data that multiplies each interest rate by the UPB (unpaid principal balance). This will simplify the calculated field you must build in the pivot and calculate your weighted average correctly. Credit Type Borr UPB IntRate UPB*IntR Prime Kelly $20,000 6.875 $137,500 Prime Kline $68,000 7.5 $510,000 Prime Stephens $450,000 6.5 $2,925,000 Prime Jordan $784,000 6.125 $4,802,000 Subprime Smith $125,000 8 $1,000,000 Subprime Westerfield $200,000 7.5 $1,500,000 Subprime Ross $312,000 7 $2,184,000 Subprime Gerry $615,000 7.25 $4,458,750 STEP 2: Create a pivot using the wizard to capture the above 5 columns. Your initial pivot should look like this: Data Credit Type Sum of UPB Prime $1,322,000 Subprime $1,252,000 Grand Total $2,574,000 STEP 3: Right-click data 'Formulas' 'Calculated Field' and enter 'WtdAvg IntRate' as the name of your field. Your formula should read: =SUM('UPB*IntR')/SUM(UPB) Results: Data Credit Type Sum of UPB Sum of WtdAvg IntRate Prime $1,322,000 6.335 Subprime $1,252,000 7.303 Grand Total $2,574,000 6.805 Note: Make sure your results match the format type of the data. For example, if your interest rate shows % in the data, format the resulting WtdAvg IntRate as %. -Grace |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating a weighted average in a pivot table | Excel Discussion (Misc queries) | |||
How do I calculate a weighted average in a pivot table? | Excel Discussion (Misc queries) | |||
Weighted Average in Pivot table | Excel Worksheet Functions | |||
weighted average on a pivot table | Excel Discussion (Misc queries) | |||
weighted average in pivot table | Excel Discussion (Misc queries) |