Pivot Table issue with weighted average subtotals and grand total

I am using Excel 2007.

I have a table of data which sets up the framework for including the weighted average in a pivot table. I have a column called wocc where the data is NULL, or blank in Excel, if occ * principal = 0, otherwise the value in the field is occ * principal.

In the pivot table I have a calculated field called socc with a formula of =IF(curr_principal=0,0,wocc/curr_principal)/100

The problem is, the resulting pivot table returns 0% in those instances where wocc was blank in the source data. As a result, the subtotal and grand total weighted average life is incorrect because those values are including the 0%. I need the pivot table to not change the blank source data to 0% so that the subtotal and grand total will show the correct amounts.

Richard,

What type of field do you add so that the pivot table shows the correct weighted average?

Also, you do know that when I say "calculated field" I mean the pivot table formual feature, yes?

I took your advice and added a column to the data, then used the appropriate calculated field formula in the pivot table to get the weighted average to work correctly.

For those who may be interested I did the following.

I have a column I called wocc. That field is from my SQL database and it is the if occ is null then null else occ * principal.

I created and added an Excel formula to the dataset that is =if(wocc = "","",principal). I named that column wprinocc.

Lastly, for the pivot table I created a calculated field called socc, which is wocc/wprincocc. Previously this did not work because the formula was wocc/principal. The issue happened when wocc was blank and there was a principal balance. In this instance the pivot table changed the value to 0, which made the weighted average at the subtotal and grand total level incorrect.

I hope this helps others.