Cell presented value differs from cell result value

Copper Contributor

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 with the formula = previous cell * 10.

The 600+ column of cells is filled with the values 1e-307, 1e-306, 1e-305, ... ,1e+308.

Problem:

There are two exceptions:

  1. the cell that should contain 1.00e-292  shows 3.29e-308;
  2. the cell that should contain 1.00e-146  shows 8.149e-163.

The cell internal values are correct, as shown by several equality and arithmetic tests, but the presented value is incorrect.

 

Moreover, the presented result depends on

  • the 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  know how to submit images or a minimal working  spreadsheet, but I can send them privately to anyone who wishes.

 

Thoughts:

This is an inocuos bug, as calculations are unaffected, but its a mystifying behavior!!!

 

 

 

8 Replies

@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:

  1. the cell that should contain 1.00e-292  shows 3.29e-308;
  2. 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?!

 

@Joe User 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-3081E-292TRUE

 

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)

Hello @jmsoeiro

Did you check this web page?
https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-2...

I 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.

@Joe User  sorry for the late reply.

 

I tend to agree with most of @GeorgieAnne  observations , and my procedure was similar, even to the point of calling out for fellow Excellers. My suspicions went more towards MacOS than anything else!

 

I tested your 1Exxx defect xls, on both an up to date Windows computer and my Macs and something maddening happened!

Both Macs maintained the error, as I expected. (see  attached MAC Exxx.png)

However, the Windows machine, on loading the xls file, displayed, as the result of the TEXT($B$2$, ...) formula in cells B18 and B19, the wrong value 3.2878E-308 (formatted according to the formatting second parameter).

The moment you edit the formula or copy/paste it somewhere else, the correct value 1E-292 is displayed!

(see cell B18 of attached Win Exxx.png. No doctoring of underlying xls or png, scouts word! (wink) :))

 

I liked your idea of displaying the difference between 1E-292 and 2.32E-308, that, due to the huge difference, should result in the larger value.

 

As you point out, the difference between 1e-292 and 1e-308 is in the 15th decimal place for normalized presentation. If I'm not wrong, excel's display engine is 128 bits, and the calculation engine is 64. Also, the other value  (1e-163) lies at a similar place in the 32 bits realm. Could this be the culprit?

 

Anyway, I think this falls into the 0.01% issue, so I'll try to submit it to MSFT.

 

Thanks, and best regards!

 


@jmsoeiro  wrote:  ``I tend to agree with most of @GeorgieAnne observations``


There is nothing to agree or disagree with, at least not contrary to anything I've said.

 

There is no doubt that there is a formatting defect, since the value that is displayed is not even close to the actual value in the cell, and the value in the cell is approximately correct.

 

-----
@jmsoeiro  wrote: ``My suspicions went more towards MacOS than anything else!``


The O/S has nothing to do with this.

 

The fact that the underlying cell value is correct (as demonstrated by my difference calculation) and the fact that it occurs on Intel-based Macs but not on Intel-based PCs (i.e. the "same" CPUs) suggest that this is specific to the Mac implementation of Excel.

 

BTW, another person tested this on an Apple M1-based Mac, and the same formatting error occurs.  That tends to rule out a CPU defect, like the FDIV defect in the 1994 Intel Pentium.


-----
@jmsoeiro  wrote: ``the Windows machine [...] displayed, as the result of the TEXT($B$2$, ...) formula in cells B18 and B19, the wrong value 3.2878E-308 [....] The moment you edit the formula or copy/paste it somewhere else, the correct value 1E-292 is displayed!``


It appears that the Excel file that you opened on a PC is a file that you had opened, then saved on the Mac, not my original file.

 

Usually, Excel does not recalculate cell values when we open an Excel file. So it displays (formats) whatever value was calculated when we saved the file.

 

However, if we subsequently recalculate those cells -- for example, after "editing" them -- they might display new values if circumtances changed.

 

For the TEXT formulas, the calculated values on the Mac are the strings "3E-308" and "3.288E-308" in B18 and B19.

 

So when we open the Mac-saved file on a PC, we see those same string values initially.

 

But when you edit and recalculate the TEXT formulas on the PC, they return the PC-formatted values, which do not exhibit the formatting defect.

 

For the numeric formulas, the calculated values on the Mac are the correct numeric values. That is what is save in the Excel file, not their (faulty) appearance.

 

When we open the Mac-saved file on the PC, the saved numeric values are correctly formatted on the PC.

 

The difference is the type of the cell value (text vs numeric) and when the formatting is done (recalculation of the TEXT formula vs display of the numeric formulas).


-----
@jmsoeiro  wrote: ``As you point out, the difference between 1e-292 and 1e-308 is in the 15th decimal place for normalized presentation``

 

I don't see where I said anything like that at all. If I did, it is wrong.

 

I assume you are talking about the difference between the actual cell value 1E-292 - 2.225E-308 and the displayed value 3.2878E-308.

 

The difference is much greater than just the 15th significant digit.  And that is really the point.

 

64-bit BFP values can be approximated by decimal values with 17 significant digits (rounded) with no loss in precision when converting between binary and decimal.

 

The approximate decimal values are:

1E-292:

10000000000000001 preceded by 291 decimal places (dp) with zeros
1E-292 - 2.225E-308:

099999999999999983 preceded by 291 dp
3.28784308806229E-308:

000000000000000032878430880622900 preceded by 291 dp


-----
@jmsoeiro  wrote: ``If I'm not wrong, excel's display engine is 128 bits, and the calculation engine is 64.``


I'm afraid that is wrong.

 

I don't know what you mean by "display engine" vs "calculation engine". There is no such distinction. Excel uses the same "calculation engine" (CPU) for formatting as it does for all 64-bit BFP arithmetic.

 

I don't know anything about the Apple M1.  But you indicated that you use Intel-based Macs.

 

In Intel-compatible CPUs, each arithmetic operation is performed using an 80-bit binary floating-point form.  The 80-bit result is left in an 80-bit register that can be accessed by software. It is rounded to 64-bit BFP when it is stored into memory or 64-bit register.

 

Generally, Excel rounds each arithmetic operation in a formula to 64-bit BFP.

 

Under some specific conditions, VBA tries to use the 80-bit results.

 

In C, we can save the 80-bit result by declaring a variable to be long double.

 

Internal Excel algorithms are free to use the 80-bit results. But I suspect that most internal Excel algorithms do not, either on purpose or by accident of implementation.

 

Arguably, the 80-bit arithmetic might be a factor in the formatting defect.  But my guess is: it is not.


-----
@jmsoeiro  wrote: ``the other value (1e-163) lies at a similar place in the 32 bits realm``

 

(The "other value" is 1E-146, not 1E-163. I assume that's what you mean.)

 

That's an interesting theory, actually one that I had considered initially. But I concluded, perhaps incorrectly, that is not the case.

 

Please elaborate and show me how you arrived at that conclusion.

 

As I wrote previously, 1E-292 is &h034FEEF6,3F97D79C, whereas 1E-292 - 2.225E-308 is &h034FEEF6,3F97D79A. The difference is binary 10, which is 2^-1022.

 

In contrast, 1E-146 is &h219FF779,FD329CB9, whereas 1E-146 - 4.4455E-162 is &h219FF779,FD329CB5. The difference is binary 100, which is 2^-536.

 

Not only do I fail to see any positional similarity, but also those powers of 2 exceed the limits of 32-bit BFP (2^-126 to 2^127).

 

FYI, I found some other values that produce formatting errors on the Mac (and not the PC), to wit:

1E-146 - 1*2^-538
1E-146 - 2*2^-538
1E-146 - 3*2^-538
1E-146 - 4*2^-538 (your value)

 

1E-292 - 1*2^-1023
1E-292 - 2*2^-1023 (your value)
1E-292 - 3*2^-1023
1E-292 - 4*2^-1023

 

None of the other binary values that display 1.0...0E-146, 9.9...9E-147, 1.0...0E-292 or 9.9...9E-293 exhibit formatting defects on the Mac (or PC).

 

Note:  We cannot enter 2^-1023. So we must use the following paradigm:

(2*1E-292 - 1*2^-1022)/2

 

PS....

 

@jmsoeiro  wrote:  ``No doctoring of underlying xls or png, scouts word! (wink) ``

 

I believe you.  But with your regional configuration, B19 does not appear as I intended.

 

The reason is:  you need to change the TEXT format from "0.000" (period) to "0,000" (comma).