Complex calculated fields in pivot table

I'm trying to use an Excel spreadsheet to keep track of tasks undertaken by different people in a network of voluntary groups. Sample attached below. Raw data is in TDL;

We need a pivot table that will give us a simple summary of key metrics for the network broken down by months across the columns and by named individuals going down the rows.

One thing we want in the data area is what percentage of the number of tasks that the person offered to do during a particular month were actually completed (field name is Status - content is either Done Late or On time - Done)

The problem I've had with this is that I can choose Field Settings: Options: Show Data As: % of Column, but the divisor is wrong: it's showing Pascal's On Time Dones for January as a percentage of all jobs by all people in January, instead of as % of all Pascal's jobs in January both done and undone.

I can't fix this either by checking the subtotal of hidden page items in Table Options or by hiding totals by rightclicking fields in the pivot table.

I can easily create the formula I want in a different cell next to the pivot table: it's

=SUM((GETPIVOTDATA("Hours required",$A$3,"Period","January","Status","Done late","Who","Donatian")+GETPIVOTDATA("Hours required",$A$3,"Period","January","Status","On time - done!","Who","Donatian"))/GETPIVOTDATA("Hours required",$A$3,"Period","January","Who","Donatian"))

where $A$3 is the page field showing Count of Hours. But I don't know how to incorporate this into the pivot table iteslf, or how to cut-paste this formula so I can get meaningful figures for each person for each month.

Once I've understood this, I'll want to show it in a simple chart that will show lines for each person over time showing the percentage of their tasks that were completed on time.

Thank you in advance if you can help!

[/I]

TimKT,

You can create a table with the periods horizontally and the names vertically. I created it from cell J17 (periods in K17:O17, names in J18:J32). Then, at the first intersection, you can put the formula (note the proper use of absolute/variable references):

Please Login or Register  to view this content.

Copy the formula to the entire table, the references will update accordingly. Then, to avoid the #REF!, you can select the cells inside the table, apply a color of font white, then put a conditionnal formatting on the same range if the cell is >= 0 to set the font color to black.