Tags: agroup, average, averageinterest, based, below, calculate, case, excel, microsoft, msdn, pivot, rate, software, steps, table, weighted

How to Calculate a Weighted Average in a Pivot Table

On Microsoft » Microsoft Excel

1,894 words with 0 Comments; publish: Sun, 01 Jun 2008 10:59:00 GMT; (30693.75, « »)

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 TypeBorr 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

SubprimeSmith $125,000 8

SubprimeWesterfield $200,000 7.5

SubprimeRoss $312,000 7

SubprimeGerry $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 TypeBorr 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

SubprimeSmith $125,000 8 $1,000,000

SubprimeWesterfield $200,000 7.5 $1,500,000

SubprimeRoss $312,000 7 $2,184,000

SubprimeGerry $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 TypeSum 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 TypeSum of UPBSum 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

All Comments

Leave a comment...

  • 0 Comments