Cell presented value differs from cell result value

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

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