Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-23-2017, 08:29 AM
jingrah1 jingrah1 is offline Pivot Table Averages with Partial Data / Default Values Windows 7 64bit Pivot Table Averages with Partial Data / Default Values Office 2016
Novice
Pivot Table Averages with Partial Data / Default Values
 
Join Date: Aug 2017
Posts: 1
jingrah1 is on a distinguished road
Default Pivot Table Averages with Partial Data / Default Values

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...
Reply With Quote
  #2  
Old 08-23-2017, 10:21 AM
NBVC's Avatar
NBVC NBVC is offline Pivot Table Averages with Partial Data / Default Values Windows 10 Pivot Table Averages with Partial Data / Default Values Office 2013
The Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 215
NBVC will become famous soon enoughNBVC will become famous soon enough
Default

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.
Reply With Quote
Reply

Tags
averages, default values, pivot tables



Similar Threads
Thread Thread Starter Forum Replies Last Post
Pivot Table Averages with Partial Data / Default Values How do I create a report that works like a pivot table but does not calculate values 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
Pivot Table Averages with Partial Data / Default Values add values from different variables with pivot table andrei Excel 2 01-19-2012 08:45 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 08:36 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft