Pivot Table Strangeness

C

Craig M. Bobchin

Hi all,

I've got a pivot table query that in all but one case is running fine.
It sums up hours by user/Project across Dates. It is used to view users
time for a given period.

The table the hours is pulled from limits the time to 2 decimal places.
The query has the hours limited to 2 decimal places (e.g. 8.25). In once
case a user has entered time as .1 and .15. So far so good.

The table shows those numbers as well. But the pivot table is showing
them as 0.100000001 and 0.150000006 respectively.

Where did these extra decimals come from, and how do I get rid of them?

Many thanks

Craig
 
D

Douglas J. Steele

It's floating point round-off error. Just as humans can't represent, say,
1/3 as a non-repeating decimal, so too are there numbers that computers
can't represent exactly (a different set, since they're using base 2, while
we're using base 10). Simply changing the format of a number doesn't
actually change the number, just how it appears. That means that while you
may be seeing .1, it's actually stored as 0.1000000001.

Try using a Round function on your values to reduce the final values to only
2 decimal points, rather than simply formatting the values.
 
M

MGFoster

Craig said:
Hi all,

I've got a pivot table query that in all but one case is running fine.
It sums up hours by user/Project across Dates. It is used to view users
time for a given period.

The table the hours is pulled from limits the time to 2 decimal places.
The query has the hours limited to 2 decimal places (e.g. 8.25). In once
case a user has entered time as .1 and .15. So far so good.

The table shows those numbers as well. But the pivot table is showing
them as 0.100000001 and 0.150000006 respectively.

Where did these extra decimals come from, and how do I get rid of them?
Click to expand...

In the query's design view right click on the column grid that you want
to format. Select Properties on the pop-up menu. On the Field
Properties dialog box set the Decimal Places property to 2.
 
C

Craig M. Bobchin

Doug,

Round did not work, but when I changed the number from general to
Standard that seemed to do the trick.

Craig
 
Top