Incorrect results from simple division in calculated field in Pivot Table (e.g. 2/2=.99)

[jahrend]

I have been tasked with creating a pivot table that illustrates what percent of employees in each department have completed Lesson A, which was assigned to them via our Learning Management System (LMS). The LMS spits out a report that lists each assigned person on a separate row. The last 2 columsn in this report are "Due Date" and "Complete Date". Everyone has a value in the "Due Date" column, but only those who have completed the lesson have a value in the "Date Complete" field. That field is blank for people who have not completed the lesson.

In my pivot table, I have a "Total Assigned" column which does a COUNT of the number of entries in the report's "Due Date" column, and then a "Total Complete" column which does a COUNT of the number of entries in the "Complete Date" column.

I created a calculated field called "% Complete" to compute the percent complete by dividing the number in the "Total Complete" field by the number in the "Total Assigned" field. Then, because those results looked weird, I added a non-pivot table manual calculation column to the far right to compare the results. See below:

Facility <tbody> </tbody>	Total Assigned <tbody> </tbody>	Total Complete <tbody> </tbody>	Sum of % Complete <tbody> </tbody>	**manual calculation**
NSHC <tbody> </tbody>	158 <tbody> </tbody>	138 <tbody> </tbody>	0.873101984 <tbody> </tbody>	0.873417722 <tbody> </tbody>
ALLERGY PB NSM <tbody> </tbody>	1 <tbody> </tbody>	1 <tbody> </tbody>	1 <tbody> </tbody>	1 <tbody> </tbody>
AMBULATORY NURSING PB NSM <tbody> </tbody>	2 <tbody> </tbody>	2 <tbody> </tbody>	0.999988048 <tbody> </tbody>	1 <tbody> </tbody>
AMBULATORY SURGICAL CTR NSM <tbody> </tbody>	25 <tbody> </tbody>	22 <tbody> </tbody>	0.879678738 <tbody> </tbody>	0.88 <tbody> </tbody>
CARDIO VASCULAR SURGERY PB NSM <tbody> </tbody>	3 <tbody> </tbody>	2 <tbody> </tbody>	0.666220469 <tbody> </tbody>	0.666666667 <tbody> </tbody>
CARDIOLOGY PB MET <tbody> </tbody>	3 <tbody> </tbody>	3 <tbody> </tbody>	0.999474125 <tbody> </tbody>	1 <tbody> </tbody>

<tbody>
</tbody>

What am I doing wrong?


This seems like a simple question, but I have googled my brains out for weeks, and have not been able to find anyone else who has come across this issue.

Your advice will be most appreciated. Thanks so much.

Julie

 

[jahrend]

All:

Thanks so much for looking into this issue. I imagine this all boils down to my lack of understanding of how pivot tables work.

As suggested, I have uploaded a sample of this data to Dropbox. You can find the file here:

https://www.dropbox.com/l/CGhs0kjBkcqyGbC3I2QqVq

Tab 1 is the list of learners who were assigned the various curricula. The rightmost columns are "Due Date" and "Completion Date". Anyone with a blank in the "Completion Date" field has not completed their assignments.

Tab 2 is the Pivot Table, where I want to calculate the percent of employees at each institution who have completed their assignments.

In building the table, when I dragged "Due Date" and "Completion Date" into the "Values" area, the label for both changed to "Count of <field name>", which led me to assume that it was actually COUNTING the number of entries in those columns on Tab 1 -- which is what I wanted. For that reason, I assumed that dividing the "Complete" count by the "Due" count in a calculated field would yield the results I needed, but it doesn't. (And yes, I noticed that all the percentages were off, but the fields where I expected 100% to appear and didn't were the ones that tipped me off to the fact that there was a problem.)

So, as you can see, I've been doing a lot of assuming here, which was obviously my big mistake. Your guidance/suggestions are most appreciated.

Thanks.

 

[jahrend]

(Sorry -- Reposting. I did a direct reply instead of "Reply to Thread".)

All:

Thanks so much for looking into this issue. I imagine this all boils down to my lack of understanding of how pivot tables work.

As suggested, I have uploaded a sample of this data to Dropbox. You can find the file here:

https://www.dropbox.com/l/CGhs0kjBkcqyGbC3I2QqVq

Tab 1 is the list of learners who were assigned the various curricula. The rightmost columns are "Due Date" and "Completion Date". Anyone with a blank in the "Completion Date" field has not completed their assignments.

Tab 2 is the Pivot Table, where I want to calculate the percent of employees at each institution who have completed their assignments.

In building the table, when I dragged "Due Date" and "Completion Date" into the "Values" area, the label for both changed to "Count of <field name="" style="color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">", which led me to assume that it was actually COUNTING the number of entries in those columns on Tab 1 -- which is what I wanted. For that reason, I assumed that dividing the "Complete" count by the "Due" count in a calculated field would yield the results I needed, but it doesn't. (And yes, I noticed that all the percentages were off, but the fields where I expected 100% to appear and didn't were the ones that tipped me off to the fact that there was a problem.)

So, as you can see, I've been doing a lot of assuming here, which was obviously my big mistake. Your guidance/suggestions are most appreciated.

Thanks.</field>

 

[RoryA]

As I said, calculated fields only ever use SUM.
If we consider the OMC-BAP line in your pivot and the relevant source data:

Your two count fields total 4. However the % Complete field actually calculates SUM(Completion Date) / SUM(Due Date):

Excel 2010
	G	H	I
1	Due Date	Completion Date
2	31/03/2014	20/02/2014
3	31/03/2014	25/03/2014
4	31/03/2014	08/01/2014
5	31/03/2014	12/03/2014
6
7	166,916.00	166,770.00	0.999125
Sheet1

Cell Formulas
Range	Formula
G7	=SUM(Sheet1!$G$2:$G$5)
H7	=SUM(Sheet1!$H$2:$H$5)
I7	=H7/G7

(remembering that in Excel all dates are stored as the number of days since 31/12/1899, with times being represented by fractional parts)

If you want a true % complete, you would need to add additional columns to the source data that return 1 for each row with a due date (presumably all of them) and 1 for each row with a completion date. Your calculated field can then simply divide one by the other.