Pivot Table Averages with Partial Data / Default Values

[jingrah1]

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...

[NBVC]

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.