Cell presented value differs from cell result value

Question

Can you reproduce the following:Scenario:I needed a set of power of ten &nbsp;values covering the whole double&nbsp;floating point dominion.I started with a constant cell =1e-307, and generated a column with the formula = previous cell * 10.The 600+ column of cells is filled with the values&nbsp;1e-307,&nbsp;1e-306,&nbsp;1e-305, ... ,1e+308.Problem:There are two exceptions:the cell that should contain 1.00e-292 &nbsp;shows 3.29e-308;the cell that should contain 1.00e-146 &nbsp;shows 8.149e-163.The cell internal values are correct, as shown by several equality and arithmetic tests, but the presented value is incorrect.&nbsp;Moreover, the presented result depends onthe distance between the constant cell and the 1e-292 cell;on the way the constant cell was obtained, either by direct writing in the cell or from a copy/paste as value clipboard operation.Tests:I verified this behavior on several Intel Macs, but could not test it on Apple silicon.On Windows PC everything works fine.I didn't check for Excel versions.I don't &nbsp;know how to submit images or a minimal working &nbsp;spreadsheet, but I can send them privately to anyone who wishes.&nbsp;Thoughts:This is an&nbsp;inocuos bug, as calculations are unaffected, but its a&nbsp;mystifying behavior!!!&nbsp;&nbsp;&nbsp;

joeuser2004 · Answer

jmsoeiro&nbsp; wrote:&nbsp; ``Can you reproduce the following``&nbsp;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:&nbsp;=VALUE("1E" &amp; ROWS($A$4:A4)-308)&nbsp;The values are infinitesimally closer to the intended decimal values of the form 1.00E-307 to 1.00E+308 than your cascading values.&nbsp;If you want constants, you can copy the range of formulas and paste-value back into the same range.&nbsp;Does that eliminate the formatting defect on the Mac?&nbsp;For 1E+308 in A619, enter the formula&nbsp; =A618*10&nbsp;And if you want something close to 1E-308, enter the following formula into&nbsp; A3:&nbsp; =2^-1022&nbsp;That results in 2.2250738585072E-308.&nbsp; But at least the exponent is E-308.&nbsp;(FYI, we can calculate 1E-308 in VBA and return it to Excel.&nbsp; But Excel does not display it correctly, and it cannot be referenced in a numerical expression, because it is a "non-normalized" value.)&nbsp;-----jmsoeiro&nbsp; wrote:the cell that should contain 1.00e-292 &nbsp;shows 3.29e-308;the cell that should contain 1.00e-146 &nbsp;shows 8.149e-163.&nbsp;Really:&nbsp; just those two values?!&nbsp;-----jmsoeiro&nbsp; wrote:&nbsp; ``The cell internal values are correct, as shown by several equality and arithmetic tests``&nbsp;Exactly what formulas did you use to determine that?&nbsp;-----jmsoeiro&nbsp; wrote:&nbsp; ``I don't know how to submit images or a minimal working spreadsheet``&nbsp;I would like to see the Excel file and images.&nbsp;Click "browse" near the bottom of the reply window to attach files.&nbsp;If the forum does not allow that (yet), upload the files to a file-sharing website, and post the download URLs.&nbsp; I like box.net/files; others like dropbox.com.&nbsp; You might like onedrive.live.com because it uses the same login as this forum.&nbsp; But IMHO, be sure the shared URL does not allow editing.&nbsp; That avoids accidental (and purposeful) changes. (I'm all thumbs. sigh)&nbsp;-----jmsoeiro&nbsp; wrote:&nbsp; ``the presented result depends on [....] either by direct writing in the cell or from a copy/paste as value clipboard operation``&nbsp;Are you saying that copy-and-paste-value avoids the error?!&nbsp;Are you pasting-value into the same workbook?!&nbsp;

jmsoeiro · Answer

JoeUser2004&nbsp;thanks for your interest.&nbsp;First, this seems to be a Mac-exclusive issue.Second, the calculated values are correct, and I &nbsp;got what I intended.Just TWO displayed cell values are wrong (and that's the strange thing!) (see tab ORIGINAL)&nbsp;The arithmetic test is simple: the sequence is correctly calculated, although the displayed values are wrong (remember, all cells have the same formula):1E-2941E-2933,288E-3081E-2911E-290&nbsp;The comparison test &nbsp;(see tab EX3:) is just left-cell = right-cell and the astonishing result is TRUE !!!3,288E-3081E-292TRUE&nbsp;Also, it seems to involve the formatting of the cell, not the value itself.&nbsp;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)&nbsp;I think I don't have the standing to include media and data files so that you can get them here (Zip):&nbsp;Best regards

joeuser2004 · Answer

jmsoeiro&nbsp; ....&nbsp; Thanks for the zip archive.&nbsp;Please forgive my incessant posting.&nbsp; But I do not think I explained things very well in my previous response (which I deleted).&nbsp;-----jmsoeiro&nbsp; wrote:&nbsp; ``the presented result depends on the distance between the constant cell and the 1e-292 cell``&nbsp;Only by coincidence.&nbsp;Presumably, the following reproduces the formatting errors that you see on the Mac.&nbsp; Please confirm.&nbsp;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.&nbsp; It displays 1.000E-292 in Excel 2010 on a PC.&nbsp;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.&nbsp; It displays 1.000E-146 in Excel 2010 on a PC.&nbsp;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.&nbsp;The point is:&nbsp; the formatting defect has to do with the internal binary value, not with how it was created.&nbsp;(*) 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).&nbsp; But if Excel for Mac has trouble with 2.23E-308, use 2^-1022 instead.&nbsp; Actually, that is more correct for my purposes.&nbsp;-----jmsoeiro&nbsp;&nbsp; wrote:&nbsp;``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``&nbsp;Again, only by coincidence.&nbsp;It depends on whether the original cell that was copied was created by a constant or by a calculation.&nbsp; And if the latter, it depends on the binary result of the calculation.&nbsp;Copy-and-paste(value) preserves the internal binary value when it is performed in the same workbook.&nbsp;The only difference is:&nbsp; copy-and-paste also copies the original format, whereas copy-and-paste-value retains the destination format.&nbsp;Again, the formatting defect has to do with the internal binary value, not with how it was created.&nbsp;-----jmsoeiro&nbsp; wrote:&nbsp; ``it seems to involve the formatting of the cell``&nbsp;That is very possible.&nbsp;But I do not see any evidence of that in your PNG and Excel files.&nbsp; Forgive me if I overlooked it.&nbsp; I cannot "see the forest for the trees".&nbsp;Please download the attached file and provide a PNG file that shows how it appears on your Mac.&nbsp; Please also include your copy of the Excel file.&nbsp;-----The bottom line is:&nbsp; The formatting defect is triggered by the internal binary representation.&nbsp; Forgive me if the following is TMI.&nbsp;In EX3, the calculated binary value in D24 (and A25) is &amp;h034FEEF6,3F97D79A.&nbsp;In contrast, the binary value of the constant 1E-292 is &amp;h034FEEF6,3F97D79C.&nbsp;And let's not forget the formatting error in Original!I176, which I cannot see in the PNG and Excel files.&nbsp;The calculated binary value is &amp;h219FF779,FD329CB5, which you say incorrectly displays 8.149E-163.&nbsp;In contrast, the binary value of the constant 1E-146 is &amp;h219FF779,FD329CB9.&nbsp;Off-hand, I don't see an "obvious" pattern that might explain this particular formatting defect.&nbsp;I doubt that this formatting defect affects only two binary values.&nbsp; It's just that we have not discovered the pattern that is the crux of the problem.&nbsp;Perhaps a pattern will become apparent if you and others uncover other examples.&nbsp;Unfortunately, my efforts to investigate this further are hampered because I do not have a Mac computer.&nbsp; So I cannot duplicate this particular formatting defect.&nbsp;I am relunctant to speculate -- although the temptation is strong.&nbsp; Biting my fingers as I type.&nbsp; (smile)

georgieanne · Answer

Hello jmsoeiroDid you check this web page?https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3I tend to take it forgathered that MS-Excel is a stable, working software package. If I do find something strange I will not blame it first I will test the heck out of it, and after all is extenuated and after I have asked all my Excelers Friends THEN and ONLY then do I blame Excel.To Err is Human, but MS-Excel does a good job 99.99% of the time.If in fact you stumbled on an "issue" then I would suggest contacting Microsoft Office Support and let them know.

joeuser2004 · Answer

GeorgieAnne&nbsp; wrote: ``Did you check this web page?https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-2...``&nbsp;&nbsp;What is your point?

Forum Discussion

Cell presented value differs from cell result value

8 Replies

Resources