Excel 2007 - Pivot Table and Calculated fields?

[Wanda]

For All: Thanks in advance for all assistance!

I have a spreadsheet listing information of all the loans on our books, one
loan per line. Some of the data includes: Type of Loan, Account number,
Loan officer approving the loan, loan balance, next due date, delinquency
amount, branch, source, and other identifying information. My loan
department would like me to create a report showing the delinquency
percentage by loan officer by loan type, including totals by loan officer and
by category, and grand total for the entire file. I'm able to create a Pivot
table showing the outstanding balance by loan officer by type, and a separate
pivot table showing delinquent balance by loan officer by type. but I can't
seem to figure out how to calculate the delinquency percentage from those 2
pivot tables.

Can this be done without a whole lot of manual calculations? My data file
changes monthly, so I don't want to spend an inordinate amount of time
"recreating the wheel" monthly.

Again, thanks in advance for any and all assistance!!!!

(Delinquency percentage is total delinquent balances divided by total loan
balances).

 

[Shane Devenshire]

Hi,

Instead of two pivot tables in one of the pivot tables add the other
calculation to the Values area. In other words at two calculations in the
Values area one for loan amount and one for delinquency amount.

Now put your cursor in the Values area and choose PivotTable Tools, Options,
Formulas, Calculated Field
Give the field a name
In the Formula box enter something like
=Delinquency/Balance
This formula depends on the names of your fields, which you can double click
in the lower portion of the window to get into the formula. Click Add, OK.
Format as %.