Forum Discussion
Cell presented value differs from cell result value
Can you reproduce the following: Scenario: I needed a set of power of ten values covering the whole double floating point dominion. I started with a constant cell =1e-307, and generated a column ...
jmsoeiro wrote: ``Can you reproduce the following``
As you note, I cannot duplicate any such misbehavior on a PC.
And usually there is no way to explain defective behavior, especially formatting defects.
But if you want to a possible work-around, then instead of cascading formulas of the form =A4*10, try entering the following formula into A4 and copy down through A618:
=VALUE("1E" & ROWS($A$4:A4)-308)
The values are infinitesimally closer to the intended decimal values of the form 1.00E-307 to 1.00E+308 than your cascading values.
If you want constants, you can copy the range of formulas and paste-value back into the same range.
Does that eliminate the formatting defect on the Mac?
For 1E+308 in A619, enter the formula =A618*10
And if you want something close to 1E-308, enter the following formula into A3: =2^-1022
That results in 2.2250738585072E-308. But at least the exponent is E-308.
(FYI, we can calculate 1E-308 in VBA and return it to Excel. But Excel does not display it correctly, and it cannot be referenced in a numerical expression, because it is a "non-normalized" value.)
-----
jmsoeiro wrote:
- the cell that should contain 1.00e-292 shows 3.29e-308;
- the cell that should contain 1.00e-146 shows 8.149e-163.
Really: just those two values?!
-----
jmsoeiro wrote: ``The cell internal values are correct, as shown by several equality and arithmetic tests``
Exactly what formulas did you use to determine that?
-----
jmsoeiro wrote: ``I don't know how to submit images or a minimal working spreadsheet``
I would like to see the Excel file and images.
Click "browse" near the bottom of the reply window to attach files.
If the forum does not allow that (yet), upload the files to a file-sharing website, and post the download URLs. I like box.net/files; others like dropbox.com. You might like onedrive.live.com because it uses the same login as this forum. But IMHO, be sure the shared URL does not allow editing. That avoids accidental (and purposeful) changes. (I'm all thumbs. sigh)
-----
jmsoeiro wrote: ``the presented result depends on [....] either by direct writing in the cell or from a copy/paste as value clipboard operation``
Are you saying that copy-and-paste-value avoids the error?!
Are you pasting-value into the same workbook?!
JoeUser2004 thanks for your interest.
First, this seems to be a Mac-exclusive issue.
Second, the calculated values are correct, and I got what I intended.
Just TWO displayed cell values are wrong (and that's the strange thing!) (see tab ORIGINAL)
The arithmetic test is simple: the sequence is correctly calculated, although the displayed values are wrong (remember, all cells have the same formula):
| 1E-294 |
| 1E-293 |
| 3,288E-308 |
| 1E-291 |
| 1E-290 |
The comparison test (see tab EX3:) is just left-cell = right-cell and the astonishing result is TRUE !!!
| 3,288E-308 | 1E-292 | TRUE |
Also, it seems to involve the formatting of the cell, not the value itself.
That could explain the different behavior for copying and pasting instead of directly writing values.
The copy /paste as value is done in the same cell to delete the formula and keep only its result.
(tab EX1: and EX2: show this)
I think I don't have the standing to include media and data files so that you can get them here (Zip):
Best regards
jmsoeiro .... Thanks for the zip archive.
Please forgive my incessant posting. But I do not think I explained things very well in my previous response (which I deleted).
-----
jmsoeiro wrote: ``the presented result depends on the distance between the constant cell and the 1e-292 cell``
Only by coincidence.
Presumably, the following reproduces the formatting errors that you see on the Mac. Please confirm.
1. =1E-292 - 2.23E-308 (*) displays 3.288E-308 instead of 1.000E-292 when it is formatted as Scientific with 3 decimal places. It displays 1.000E-292 in Excel 2010 on a PC.
2. =1E-146 - 4.45E-162 displays 8.149E-163 instead of 1.000E-146 when it is formatted as Scientific with 3 decimal places. It displays 1.000E-146 in Excel 2010 on a PC.
Both should (but probably don't) display 9.99999999999999E-xxx when formatted as Scientific with 14 decimal places, and 1.0000000000000E-xxx when formatted as Scientific with 13 or fewer decimal places.
The point is: the formatting defect has to do with the internal binary value, not with how it was created.
(*) We should be able to enter 2.23E-308 because it is greater than 2.2251E-308, which is the smallest number that we can enter (although we can calculate smaller values). But if Excel for Mac has trouble with 2.23E-308, use 2^-1022 instead. Actually, that is more correct for my purposes.
-----
jmsoeiro wrote:
- ``the presented result depends on [....] the way the constant cell was obtained, either by direct writing in the cell or from a copy/paste as value clipboard operation``
- ``That [the cell format] could explain the different behavior for copying and pasting instead of directly writing values``
Again, only by coincidence.
It depends on whether the original cell that was copied was created by a constant or by a calculation. And if the latter, it depends on the binary result of the calculation.
Copy-and-paste(value) preserves the internal binary value when it is performed in the same workbook.
The only difference is: copy-and-paste also copies the original format, whereas copy-and-paste-value retains the destination format.
Again, the formatting defect has to do with the internal binary value, not with how it was created.
-----
jmsoeiro wrote: ``it seems to involve the formatting of the cell``
That is very possible.
But I do not see any evidence of that in your PNG and Excel files. Forgive me if I overlooked it. I cannot "see the forest for the trees".
Please download the attached file and provide a PNG file that shows how it appears on your Mac. Please also include your copy of the Excel file.
-----
The bottom line is: The formatting defect is triggered by the internal binary representation. Forgive me if the following is TMI.
In EX3, the calculated binary value in D24 (and A25) is &h034FEEF6,3F97D79A.
In contrast, the binary value of the constant 1E-292 is &h034FEEF6,3F97D79C.
And let's not forget the formatting error in Original!I176, which I cannot see in the PNG and Excel files.
The calculated binary value is &h219FF779,FD329CB5, which you say incorrectly displays 8.149E-163.
In contrast, the binary value of the constant 1E-146 is &h219FF779,FD329CB9.
Off-hand, I don't see an "obvious" pattern that might explain this particular formatting defect.
I doubt that this formatting defect affects only two binary values. It's just that we have not discovered the pattern that is the crux of the problem.
Perhaps a pattern will become apparent if you and others uncover other examples.
Unfortunately, my efforts to investigate this further are hampered because I do not have a Mac computer. So I cannot duplicate this particular formatting defect.
I am relunctant to speculate -- although the temptation is strong. Biting my fingers as I type. (smile)