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

J

jahrend

New Member
Joined
Jul 25, 2014
Messages
5
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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 280"]
<tbody>[TR]
[TD="class: xl65, width: 280"]Facility[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="class: xl65, width: 98"]Total Assigned[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 103"]
<tbody>[TR]
[TD="class: xl65, width: 103"]Total Complete[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="width: 130"]Sum of % Complete[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]**manual calculation**[/TD]
[/TR]
[TR]
[TD][TABLE="width: 280"]
<tbody>[TR]
[TD="class: xl65, width: 280"]NSHC[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="class: xl65, width: 98, align: right"]158[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 103"]
<tbody>[TR]
[TD="class: xl65, width: 103, align: right"]138[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="class: xl65, width: 130, align: right"]0.873101984[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="width: 130, align: right"]0.873417722[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 280"]
<tbody>[TR]
[TD="class: xl65, width: 280"]ALLERGY PB NSM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="class: xl65, width: 98, align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 103"]
<tbody>[TR]
[TD="class: xl65, width: 103, align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 103"]
<tbody>[TR]
[TD="class: xl65, width: 103, align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="width: 130, align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 280"]
<tbody>[TR]
[TD="class: xl65, width: 280"]AMBULATORY NURSING PB NSM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="class: xl65, width: 98, align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="class: xl65, width: 98, align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="class: xl65, width: 130, align: right"]0.999988048[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="width: 130, align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 280"]
<tbody>[TR]
[TD="class: xl65, width: 280"]AMBULATORY SURGICAL CTR NSM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="class: xl65, width: 98, align: right"]25[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 103"]
<tbody>[TR]
[TD="class: xl65, width: 103, align: right"]22[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="class: xl65, width: 130, align: right"]0.879678738[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="width: 130, align: right"]0.88[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 280"]
<tbody>[TR]
[TD="class: xl65, width: 280"]CARDIO VASCULAR SURGERY PB NSM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="class: xl65, width: 98, align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 103"]
<tbody>[TR]
[TD="class: xl65, width: 103, align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="class: xl65, width: 130, align: right"]0.666220469[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="width: 130, align: right"]0.666666667[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 280"]
<tbody>[TR]
[TD="class: xl65, width: 280"]CARDIOLOGY PB MET[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="class: xl65, width: 98, align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="class: xl65, width: 98, align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="class: xl65, width: 130, align: right"]0.999474125[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="width: 130, align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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
 
Sort by date Sort by votes
A calculated field in a pivot table will not use Count - it will always use Sum. What exactly are you adding up in those fields?
 
Upvote 0
RoryA said:
A calculated field in a pivot table will not use Count - it will always use Sum. What exactly are you adding up in those fields?
Click to expand...

Rory:

"% Complete" is just simple division, but in a calculated field. It's "= Total Complete/Total Assigned". The results are incorrect.

The rightmost column is also "= Total Complete/Total Assigned", but the calculation is done outside of the pivot table. These results are correct.
 
Upvote 0
Try this formula in the Calculated Field

=ROUND('Total Complete' /'Total Assigned',2)

Hope this helps

M.
 
Upvote 0
Create a calculated field with this formula......

= SUM('Total Complete' )/SUM('Total Assigned' ) and you'll get the % you want. Format the cells to the decimal precision you want.
 
Upvote 0
I think you miss my point. Your calculated field is not doing:
=Count(DateComplete)/Count(DueDate)
it is doing:
=SUM(DateComplete)/SUM(DueDate)

If all the Date Complete fields are not equal to the Due Date fields (including any time portions), then the results will not mimic the counts.
 
Upvote 0
jahrend said:
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.
Click to expand...

jahrend said:
Why would it be that in Pivotland, 1/1=1, but 2/2=0.9999 and 3/3=0.9994?? (Further down in my table, 4/4=0.9995 and THEN 1/1=0.9993.)
Click to expand...

I suspect that we will not be able to answer your questions dispositively without seeing the actual workbook. The devil is in details that we cannot see when you simply copy displayed numbers.

I suggest that you upload an example Excel file to a file-sharing website and post the "shared" URL here. See footnote [1] for a list of some free file-sharing websites, if you do not have one in mind to use.

First, the problem is not with just 2/2, 3/3 or 4/4. It is with almost all of the rows, including 138/158, 22/25 and 2/3.

The root cause is that the displayed values in the "total complete" column are rounded versions of the actual values.

How they got that way is anyone's guess, since you do not show us the formulas used to calculate that column.

You say it is a "count". But that is unlikely. Rory says it is a "sum". If so, it is probably the sum of non-integers -- although you might have intended them to be integers.

In particular, what appears as "total complete" on the left below is actually in the range on the right. If you can change the format of the "total complete" column, you should see what I mean.
Code: 
row1 138:  137.9501133930 to 137.9501135510
row3 2:      1.9999760950 to   1.9999760970
row4 22:    21.9919684375 to  21.9919684625
row5 2:      1.9986614055 to   1.9986614085
row6 3:      2.9984223735 to   2.9984223765

[EDIT] Note: I am assuming that the values under "total assigned" are indeed integers. They might not be.

Instead of bandaiding the problem by rounding in the "total complete" or "% complete" column, you might want to understand why your "counts" are not exact integers in the "total complete" column, if that is what you intended, and fix the problem at its source.


-----
[1] Some free file-sharing websites:
Dropbox: http://dropbox.com
Box.Net: http://www.box.net/files
MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
RapidShare: http://www.rapidshare.com
Windows OneDrive: http://onedrive.live.com
 
Last edited:
Upvote 0
RoryA said:
I think you miss my point. Your calculated field is not doing:
=Count(DateComplete)/Count(DueDate)
it is doing:
=SUM(DateComplete)/SUM(DueDate)
Click to expand...

joeu2004 said:
Rory says it is a "sum". If so, it is probably the sum of non-integers -- although you might have intended them to be integers
Click to expand...

Scratch that! It is probably a misdirection. Sorry about that.

I believe Rory's response is correct. When I fill A1:A138 with =TODAY() and B1:B158 =TODAY()+7, SUM(A1:A138)/SUM(B1:B158) is indeed about 0.87, close to what Julie sees.

Of course, my choice of dates is arbitrary and unrealistic. It's just intended to be a "proof of concept".
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Calculated Field based on Data in Column
Replies
0
Views
115
sea_captain
S
M
  • Question Question
Pivot table and calculation issues
Replies
1
Views
217
RoryA
RoryA
B
  • Question Question
Create calculated field in pivot table that will sum all values greater than zero/exclude negative values
Replies
3
Views
159
RoryA
RoryA
J
  • Question Question
Employee Timesheet Pivot Table
Replies
2
Views
337
alansidman
alansidman
K
  • Solved
Column Returns Value Based on ISBLANK / COUNTS ALL CELLS IN PIVOT TABLE
Replies
2
Views
58
kraamerica
K

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top