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

 

[Marcelo Branco]

See if this helps
Floating-point arithmetic may give inaccurate results in Excel

M.

 

[RoryA]

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?

 

[jahrend]

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?

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.

 

[jahrend]

See if this helps
Floating-point arithmetic may give inaccurate results in Excel

M.

Marcelo:

Thanks for the link. I read through it and am not sure that that's the issue here. (Granted, I haven't been a math whiz in many many MANY years.)

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

 

[Marcelo Branco]

Try this formula in the Calculated Field

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

Hope this helps

M.

 

[BGY23]

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.

 

[RoryA]

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.

 

[joeu2004]

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.

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

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.

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

 

[joeu2004]

I think you miss my point. Your calculated field is not doing:
=Count(DateComplete)/Count(DueDate)
it is doing:
=SUM(DateComplete)/SUM(DueDate)

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

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