A pivot table field calculated using other field values as fields?

Hi,

I need to calculate the attendance rate of students as a % of the total number of hours offered in a course. Specifically:

At = 100 - Ha/Ho*100

where
At = Attendance rate (as a %)
Ha = Hours absent
Ho = Hours offered

Currently, 'Attendance' is coded as 0 (absent), 1 (present) or tba (to be assessed). In a pivot table, I have 'Student ID' in rows, 'Attendance' in columns, and 'Count of Attendance' as values. So the pivot table looks like this:

Student ID	0	1	tba
141002	56	100	198
141012	47	109	198
141013	38	118	198

When I go to calculated field, the only fields available are the fields from the pivot table source data eg Student ID; Attendance. Is it possible to use the values of 'Attendance' as fields, in order to calculate the new field of 'Attendance Rate', or something to that effect?

Thanks.

Here is a sample template of the file.

Hi,

I have created a pivot table in the attached file after adding few helper columns to split the attendance. See if this suits your requirement.

chrisf78 reported that there was inadvertently private data in the file, which was propagated by cbatrody's response. I have taken cbatrody's version and cleaned up data, using generic values for student ID, subject, and staff.

chrisf78, if this still doesn't address your concerns please report this post and we will just delete all instances of the file completely.