Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Precision As Displayed Has Failed Me--Help
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Precision As Displayed Has Failed Me--Help
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 "Eric" wrote in message ... |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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Precision As Displayed Has Failed Me--Help
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Precision As Displayed Has Failed Me--Help
Hi Eric,
How do you know its value is 3.67? What does the formula bar show? -- Kind regards, Niek Otten Microsoft MVP - Excel "Eric" wrote in message ... | 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 | |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Precision As Displayed Has Failed Me--Help
On Jul 10, 12:48 pm, Eric wrote:
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 ----- On Jul 10, 12:48*pm, Eric wrote: 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Precision As Displayed Has Failed Me--Help
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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Precision As Displayed Has Failed Me--Help
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Precision As Displayed Has Failed Me--Help
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 "Jim Rech" wrote in message ... | Eric - Please sent it to me at . | | -- | Jim | "Eric" wrote in message | ... | 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? | | | | |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Precision As Displayed Has Failed Me--Help
Hi Joe(?),
Just (very) curious: How do you examine the internal binary representations of Excel's data? -- Kind regards, Niek Otten Microsoft MVP - Excel "joeu2004" wrote in message ... On Jul 10, 12:48 pm, Eric wrote: 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 ----- On Jul 10, 12:48 pm, Eric wrote: 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. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Precision As Displayed Has Failed Me--Help
One way would be to use my VBA functions from
http://groups.google.com/group/micro...ab03078dd128db 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). "Niek Otten" wrote: .... Just (very) curious: How do you examine the internal binary representations of Excel's data? .... |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Precision As Displayed Has Failed Me--Help
On Jul 11, 10:02*am, "Niek Otten" wrote:
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 z0 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 |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Precision As Displayed Has Failed Me--Help
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. Last, I wrote: ' With OnError, "dbl.val=arg" converts (&h7ffxNNNN,z) ' to (&h7ffyNNNN,z), where x=0-7, y=8-f and z0 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 ----- On Jul 16, 6:40*am, joeu2004 wrote: On Jul 11, 10:02*am, "Niek Otten" wrote: 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 z0 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Please help me... Set precision as displayed issue | Excel Discussion (Misc queries) | |||
How do I get "Precision as Displayed" for Excel workbooks? | Excel Discussion (Misc queries) | |||
Precision as displayed problems | Excel Worksheet Functions | |||
Precision displayed does not match precision in cell | Excel Discussion (Misc queries) | |||
Precision as displayed | Setting up and Configuration of Excel |