If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Excel 2007 - Pivot Table and Calculated fields?
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). |
#2
|
|||
|
|||
Excel 2007 - Pivot Table and Calculated fields?
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 %. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Wanda" wrote: 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). |
Thread Tools | |
Display Modes | |
|
|