+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Registered User
    Join Date
    04-18-2015
    Location
    saudi arabia
    MS-Off Ver
    2013
    Posts
    10

    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.
    Last edited by chrisf78; 05-07-2015 at 03:09 AM.

  2. #2
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,134

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

    Hi,

    Yes, it's possible to add a calculated field for the "Attendance %". Can you please post a sample template of your file in excel format (without any confidential info)?

  3. #3
    Registered User
    Join Date
    04-18-2015
    Location
    saudi arabia
    MS-Off Ver
    2013
    Posts
    10

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

    Here is a sample template of the file.
    Last edited by chrisf78; 05-06-2015 at 11:09 AM.

  4. #4
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,134

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

    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.
    Last edited by 6StringJazzer; 05-06-2015 at 10:28 PM. Reason: Removed attachment containing sensitive data

  5. #5
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,143

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

    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.
    Attached Files Attached Files
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  6. #6
    Registered User
    Join Date
    04-18-2015
    Location
    saudi arabia
    MS-Off Ver
    2013
    Posts
    10

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

    Thanks to cbatrody and 6string

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 11-04-2014, 04:34 PM
  2. [SOLVED] How to create a Calculated Field based off two Avg fields in a Pivot table?
    By rcocalm in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 02-22-2014, 03:18 AM
  3. [SOLVED] Referring to a Sub-Field on Calculated Field Pivot Table Column?
    By figo12 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 01-27-2014, 02:02 PM
  4. Replies: 0
    Last Post: 06-26-2012, 09:06 PM
  5. [SOLVED] Calculated Field in Pivot Table Based on Two Counted Fields
    By cmlits in forum Excel General
    Replies: 1
    Last Post: 03-30-2006, 12:50 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1