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