Precision displayed does not match precision in cell

[Wilkerson@discussions.microsoft.com James Wilkerson]

I entered 5420.06727. When I print out the formulas the number is
5420.06726999999. I have tried basic formatting techniques, but it doesn't
fix it. My co-workers have had this issue with different numbers as well. How
do I fix this? I need the formula sheet to display the same thing as the data
sheet.

[V. Wilkerson@discussions.microsoft.com James V. Wilkerson]

I've tried that as well as other basic formatting things. I've deleted the
cell and re-entered it. I've cleared the contents. I have set up the options
to calculate based on precision displayed so the result is correct. We have
to find a way to fix this so that our auditors don't have problems with it.
Thanks, for the suggestion.

"Marvin P. Winterbottom" wrote:

> highlight the cell or cells you want to format, on the menu bar pick Format /
> Cells / Number / set the number of decimal places to 5, or whatever you want.

[Ron Rosenfeld]

I cannot reproduce your results by following your directions.

When you say "entered 5420.06727" did you type that into the cell exactly, or
did you enter it some other way.

Do you see the same 5420.06726999999 when you do Print Preview?

What is the cell format?

What version of Excel and OS are you using?

What regional settings?

--ron

[Jerry W. Lewis]

I reproduced this (by directly entering 5420.06727 into a cell and
checking the value in the formula bar) in both Excel XP and Excel 2000).

This is probably another instance of the issue underlying

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q161234

Excel (and almost all other computer programs) stores numbers in binary,
not decimal

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q78113
http://www.cpearson.com/excel/rounding.htm

The binary representation for 5420.06727 is <5420.06727 but
>5420.067269999995, so it should be possible to display it as 5420.06727.

Based on previous discussions in the newsgroups, I believe this to be a
display issue, not a numeric issue. If so, then arithmetic will not be
impacted by this annoying issue. For instance if you enter 5420.06727
in A1 and 5420.06726999999 in A2, then =(A1-A2) will not be zero.

Jerry

[Ron Rosenfeld]

On Fri, 15 Apr 2005 06:11:02 -0700, James V. Wilkerson
<JamesVW...@discussions.microsoft.com> wrote:

>Ron,
>We are using Office 2000 on Windows 2000 Professional. The cell is currently
>formatted as general, but I've tried number - five decimals. I have typed the
>number in and copied it from another source. I have typed the number in on
>other workstations where I work which leads me to believe it is a setting
>that I have overlooked. It shows up as 5420.06727 in print preview. I'm not
>sure what you mean by regional settings.

So it shows as 5420.06727 in print preview but not if you print it on a
printer?

As I said, I am unable to reproduce that.

Under certain circumstances, what you say is possible. I just don't see it
happening using Print or Print Preview.

Excel is storing 5420.06727 as the binary equivalent of 5420.06726999999 so
that is why I say it is possible to occur under some circumstances. I just
don't see it happening printing a page in which I have entered that number.

Unless I can reproduce your problem, I don't think I'll be able to devise a
work-around. Maybe someone else can.

--ron

[Jerry W. Lewis]

The referenced KB article says nothing about a patch, so AFAIK there is
none. If challenged, just cite the KB article and point out that even
if it were a numeric rather than a display issue, it would be around a
0.00000000002% error. Your data is not that accurate.

Jerry

James V. Wilkerson wrote:

> As this may be the case, is there a "fix" for it. I can print the data sheet
> showing the correct number, but when I print out the formulas it changes the
> number to 5420.06726999999. I have to put this in a notebook and have these
> two match so that when our client or the FDA reviews it they won't have
> questions. I'm sure we could explain this issue to them, but it would be
> easier if we didn't have to. Any suggestions?

[Jerry W. Lewis]

Additional thoughts:

Your validation should have shown the result of the calculation to match
an independent source of the answer, so that should be sufficient for
regulartors, particularly given FDA's current "risk-based" environment.

If your QAV people need more, you can note that the IEEE binary
representation (see the cpearson.com link of my original reply) for
5420.06727 is equivalent to 11639505833385/2147483648 and the IEEE
binary representation for 5420.06726999999 is equivalent to
5959426986693109/1099511627776; their difference is 11/1099511627776 or
1.00044417195022106170654296875E-11. You can then subtract
5420.06726999999 from the cell that is supposed to contain 5420.06727 to
show that Excel reports that difference as 1.00044417195022E-11 (Excel's
documented limit is 15 digits -- see Help for "Excel specifications and
limits" sutopic "Calculation specifications"). Therefore the value in
the cell is the correct representation of 5420.06727, even though it
displays as 5420.06726999999.

Jerry

[Jerry W. Lewis]

Two final thoughts on this:

If 5420.06727 is a constant entered in a cell, you could use the
following VBA function to verify that the value is stored correctly (VBA
does not share Excel's problem with correctly displaying certain
floating point numbers)

Function whatVal(x As Double) As String
whatVal = CStr(x)
End Function

Whether 5420.06727 is a constant or part of a formula, you could
sidestep the issue by using the equivalent formula of 542006727/100000
which will display correctly.

Jerry