Precision As Displayed Has Failed Me--Help

[Eric]

I have a worksheet I distribute to others to complete and return. I need data
entered to no more than one decimal place. So I have formatted the numbers to
one decimal place and enacted "precision as displayed." Once PAD is enacted,
if you enter 3.67 in a cell, both the display and the underlying value become
3.7.

At least this is the way it's supposed to work. Somebody somehow managed to
get the 3.67 value into a cell (entering into another cell, copying and
pasting?), the cell displays as 3.7, but it calculates on the 3.67.

I can sort of understand how the underlying value might show as 3.67, but I
definitely can't understand why PAD isn't overriding the calculation since
it's displayed as 3.7.

Is there an additional safeguard I could deploy, or is this just one way
that Excel isn't perfect? TIA.

 

[Jim Rech]

Best guess is that the cell now has General number format due to the
copy/paste. If the column width is narrow enough 3.67 will display as 3.7.

--
Jim
|I have a worksheet I distribute to others to complete and return. I need
data
| entered to no more than one decimal place. So I have formatted the numbers
to
| one decimal place and enacted "precision as displayed." Once PAD is
enacted,
| if you enter 3.67 in a cell, both the display and the underlying value
become
| 3.7.
|
| At least this is the way it's supposed to work. Somebody somehow managed
to
| get the 3.67 value into a cell (entering into another cell, copying and
| pasting?), the cell displays as 3.7, but it calculates on the 3.67.
|
| I can sort of understand how the underlying value might show as 3.67, but
I
| definitely can't understand why PAD isn't overriding the calculation since
| it's displayed as 3.7.
|
| Is there an additional safeguard I could deploy, or is this just one way
| that Excel isn't perfect? TIA.

 

[Eric]

Thanks, but it's plenty wide and when I check the formatting, it's the same
as the others.

Another thing I should add is that the worksheet is protected (except
obviously for the area where the value is entered), but I'm not sure how that
would affect anything.

 

[Niek Otten]

Hi Eric,

How do you know its value is 3.67? What does the formula bar show?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Thanks, but it's plenty wide and when I check the formatting, it's the same
| as the others.
|
| Another thing I should add is that the worksheet is protected (except
| obviously for the area where the value is entered), but I'm not sure how that
| would affect anything.
|
| "Jim Rech" wrote:
|
| > Best guess is that the cell now has General number format due to the
| > copy/paste. If the column width is narrow enough 3.67 will display as 3.7.
| >
| > --
| > Jim
|

 

[joeu2004]

Somebody somehow managed to
get the 3.67 value into a cell (entering into another cell, copying and
pasting?), the cell displays as 3.7, but it calculates on the 3.67.

What revision of Excel are you using?

Based on my experiments with Excel 2003, I would guess that something
is wrong with your understanding or explanation of the situation.

I am looking at the internal binary representation.

When I enable Precision As Displayed, format a cell as Number with 1
DP and enter 3.67, the value stored and displayed as 3.7 has exactly
the same binary representation as when I enter 3.7 into another
similarly formatted cell.

When I format a cell as General and enter 3.67 (Jim's speculation), it
has a different binary representation, as expected. But after that,
if I format the cell as Number with 1 DP (what you claim to see post
facto), the value is displayed as 3.7 (as expected), and its binary
representation is the same as the 2 cells in the previous paragraph.

In summary, when Precision As Displayed is set, I have not found a way
for a cell formatted as Number with 1 DP and displaying a value of 3.7
to have a binary representation that differs from the literal constant
3.7.


----- original posting -----

 

[Eric]

1) Formula bar shows 3.67. 2) The value in that cell is part of a formula,
and the results of that formula clearly indicate it used 3.67 and not 3.7.

When I click to edit the value in the formula bar and then hit the
checkmark, it then locks in as 3.7 and the formula results update
accordingly. The action can then be undone and it returns to 3.67.

It was created in xl2003 but I just opened it in xl2007 and it does the same
thing.

I will be happy to send this to anyone who wants to provide an address to
see for themselves. I'm stumped.

Thanks.

 

[Jim Rech]

Eric - Please sent it to me at (e-mail address removed).

 

[Niek Otten]

Hi Jim,

Please keep us informed!

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Eric - Please sent it to me at (e-mail address removed).
|
| --
| Jim
| | > 1) Formula bar shows 3.67. 2) The value in that cell is part of a formula,
| > and the results of that formula clearly indicate it used 3.67 and not 3.7.
| >
| > When I click to edit the value in the formula bar and then hit the
| > checkmark, it then locks in as 3.7 and the formula results update
| > accordingly. The action can then be undone and it returns to 3.67.
| >
| > It was created in xl2003 but I just opened it in xl2007 and it does the
| > same
| > thing.
| >
| > I will be happy to send this to anyone who wants to provide an address to
| > see for themselves. I'm stumped.
| >
| > Thanks.
| >
| > "Niek Otten" wrote:
| >
| >> Hi Eric,
| >>
| >> How do you know its value is 3.67? What does the formula bar show?
| >>
| >
|
|

 

[Jim Rech]

Eric sent the workbook and it exhibited the problem exactly as he said.
Which is to say:

-Precision as Displayed was set.
-A cell, number formatted to show 1 decimal place, displayed 31.1 but the
formula bar showed 31.14 and a formula referencing the cell clearly showed
that Excel was using 31.14.
-No amount of Calcing, CalculateFullRebuild, etc. fixed this but pressing
F2, Enter did.

So, how did a user get 31.14 to stick in a cell formatted to show one
decimal place?

Eric mentioned a paste maybe being involved so I fooled with it. It didn't
take long to find a, ahhh, problem in Excel 2003 (and repro'ed in 2007).
Some might say a bug.

-Create 2 new workbooks
-In Book1 set Precision as Displayed on.
-In Book1 number format cell A1 to show 1 decimal place.
-In Book2 enter 31.14 in a cell (this cell should have the General number
format)
-Copy this cell and Paste Special, Values it into formatted cell A1 in
Book1.

The formula bar shows 31.14 and the cell shows 31.1. Press F2 and Enter and
the formula bar then shows 31.1 as it should have from the start. Agreed, a
bug? Workarounds? An worksheet change macro could fix it as this shows:

Range("A1").Value = Range("A1").Value

Thanks, Eric.

--
Jim
| Eric - Please sent it to me at (e-mail address removed).
|
| --
| Jim
| | > 1) Formula bar shows 3.67. 2) The value in that cell is part of a
formula,
| > and the results of that formula clearly indicate it used 3.67 and not
3.7.
| >
| > When I click to edit the value in the formula bar and then hit the
| > checkmark, it then locks in as 3.7 and the formula results update
| > accordingly. The action can then be undone and it returns to 3.67.
| >
| > It was created in xl2003 but I just opened it in xl2007 and it does the
| > same
| > thing.
| >
| > I will be happy to send this to anyone who wants to provide an address
to
| > see for themselves. I'm stumped.
| >
| > Thanks.
| >
| > "Niek Otten" wrote:
| >
| >> Hi Eric,
| >>
| >> How do you know its value is 3.67? What does the formula bar show?
| >>
| >
|
|

 

[Niek Otten]

Hi Joe(?),

Just (very) curious:
How do you examine the internal binary representations of Excel's data?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

Somebody somehow managed to
get the 3.67 value into a cell (entering into another cell, copying and
pasting?), the cell displays as 3.7, but it calculates on the 3.67.

What revision of Excel are you using?

Based on my experiments with Excel 2003, I would guess that something
is wrong with your understanding or explanation of the situation.

I am looking at the internal binary representation.

When I enable Precision As Displayed, format a cell as Number with 1
DP and enter 3.67, the value stored and displayed as 3.7 has exactly
the same binary representation as when I enter 3.7 into another
similarly formatted cell.

When I format a cell as General and enter 3.67 (Jim's speculation), it
has a different binary representation, as expected. But after that,
if I format the cell as Number with 1 DP (what you claim to see post
facto), the value is displayed as 3.7 (as expected), and its binary
representation is the same as the 2 cells in the previous paragraph.

In summary, when Precision As Displayed is set, I have not found a way
for a cell formatted as Number with 1 DP and displaying a value of 3.7
to have a binary representation that differs from the literal constant
3.7.


----- original posting -----

 

[Jerry W. Lewis]

One way would be to use my VBA functions from
http://groups.google.com/group/microsoft.public.excel/msg/5aab03078dd128db

Note that even formulas, such as
=3+67/100
when formatted to disply only 1 decimal place, will have the same binary
representation under "Precision As Displayed" as if you had entered the
constant value 3.7 (verified in xl 2003).

:

....

Just (very) curious:
How do you examine the internal binary representations of Excel's data?

....

 

[joeu2004]

Just (very) curious:
How do you examine the internal binary representations
of Excel's data?

In its simplest form, I use the following function, dbl2bin(). The
actual implementation has some bells and whistles that complicate
things.


Function dbl2bin(arg) As String
Dim lng As Tlong2
Dim dbl As Tdouble
Dim lng1 As String, lng0 As String
Dim out1 As String, out0 As String

' LSet requires user-defined types
'
' With OnError, "dbl.val=arg" converts (&h7ffxNNNN,z)
' to (&h7ffyNNNN,z), where x=0-7, y=8-f and z>0 if x=0,
' otherwise z>=0; that is, everything except INF, which
' is (&h7ff00000,0). Without OnError, assignment causes
' error. CopyMemory avoids error on assignment; and it
' should avoid conversion. but CopyMemory might not be
' portable; and it might not be worth the trouble since
' we cannot find a way for "arg" to be any of the values
' (&h7ffxNNNN,z) as described above.

On Error Resume Next
dbl.val = arg
LSet lng = dbl

lng1 = Hex(lng.val(1)): len1 = Len(lng1)
out1 = "&h00000000": Mid(out1, 11 - len1, len1) = lng1

lng0 = Hex(lng.val(0)): len0 = Len(lng0)
out0 = "00000000": Mid(out0, 9 - len0, len0) = lng0

dbl2bin = out1 & "," & out0
End Function

 

[joeu2004]

Sorry, I omitted the user-defined type definitions, namely:

Type Tdouble
val As Double
End Type

Type Tlong2
val(1) As Long
End Type

Also, I should note that the implementation presumes a little-endian
architecture. This is true for Intel (and similar) PCs. I don't know
about Macs.

' With OnError, "dbl.val=arg" converts (&h7ffxNNNN,z)
' to (&h7ffyNNNN,z), where x=0-7, y=8-f and z>0 if x=0,
' otherwise z>=0

I should have written y=x+8. It's a nitpick anyway. The situation
should never arise when the function is called from an Excel formula.
The issue is more relevant to a companion function, makedbl, which
constructs a double floating-point value from two hex strings
representing the upper and lower 32 bits.


----- original posting -----