Cell presented value differs from cell result value

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)