![]() |
#1
|
|||
|
|||
![]()
I'm new here, so I'm not sure exactly how much or what to include. I'll do my best...
I have this large set of data that needs to be placed into a pivot table... Here's a pseudo-sample: course id - course name - quiz id - quiz name - email - school - content_completed 1 - english - 1 - quiz 1 - email@email.com - USC - 1 1 - english - 2 - quiz 2 - email@email.com - USC - 1 2 - french - 1 - quiz 1 - email@email3.com - UNC - 1 2 - french - 1 - quiz 1 - email@email2.com - MIT - 1 2 - french - 2 - quiz 2 - email@email2.com - MIT - 1 This table is being generated by c# using tables that only keep track of completed courses. So, to fill the table with the courses that the users didn't take, I would need to do a cartesian join which is impractical for the application's purpose. So, I went and generated the pivot table... Columns are: Course Name -> Quiz Name. Rows are School -> Email. Values are Average of content_completed. However, the problem is that the blank rows do not get computed as 0's into the total averages. That means if the user has completed any quiz, the user gets 100% of the course. If the user has not completed any quiz, they get a 0%... Whereas if the course has 2 quizes and he has only completed 1, he should get a 50%... So I tried the function everyone recommends which is to display blank fields as 0.. This results in the fields showing as 0 but has no effect on the averages. So, trying to think outside of the box, I tried creating a calculated field with the formula as: content_completed / 1. This fills all blank cells with 0's and all completed cells with 1's. However, when I change the field subtotal settings from 'Automatic' to 'Custom -> Average', all the subtotal values disappear and I don't get any averages. I would think that a default value functionality would be worked into a sophisticated statistical application like Excel. Is there even a way to accomplish this? Or am I wasting my time? Please let me know if you need clarification.. Again, this is my first time posting on this forum... |
#2
|
||||
|
||||
![]()
Perhaps you can add a helper column to the main data with formula like:
=IF(F2="",0,F2) Where F2 is first Content_Completed column entry. Copy formula down. Put a header title in the column and use that column in the Pivot table instead. |
![]() |
Tags |
averages, default values, pivot tables |
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
![]() |
elizbeth6339 | Excel | 1 | 03-09-2016 07:14 AM |
How to create a pivot table with text as values | ljd108 | Excel Programming | 0 | 01-22-2015 07:48 PM |
Pivot table auto print. For multiple values. | Nicholaspoe | Excel | 0 | 10-08-2013 09:08 PM |
Pivot Table:delete zero values when using calculated objects | Serge 007 | Excel | 1 | 06-05-2013 11:47 AM |
![]() |
andrei | Excel | 2 | 01-19-2012 08:45 AM |