XL2BB does not always capture the exact cell value and displayed value

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,682
Office Version
2010
Platform
Windows
Consider the following example:

Book1
A
115818.00001
216000
Sheet1


In fact, the value in A1 is the constant 15818.0000098765, and the value in A2 is the constant 16000.0000012345.

Moreover, both A1 and A2 are formatted as General, and with the default cell width, only the integer part is displayed (i.e. 15818 and 16000).

Note that XL2BB shows how A2 is displayed correctly. But not A1.

Obviously, __I__ could change the format to display 10 decimal places. Then XL2BB displays all 15 significant digits.

But when I am helping a user who does not understand why 1+1 <> 2, it is important that I am able to see the exact values, at least to 15 significant digits.

And in that case, I have no control over how the user formats the values. In fact, that might be part of the issue that the user needs to learn.

I am fine with asking users to upload an Excel file to a file-sharing website and post the download URL. In fact, there are situations that is necessary anyway, because even seeing 15 significant digits is not good enough.

But I have gotten pushback from other participants.

It would be helpful if XL2BB correctly showed how values are displayed, and at the same time, it provided a way to see the exact value, at least to 15 significant digits. Kinda like the feature that allows us to see the underlying formulas.

In fact, entering the constants as formulas -- that is, =15818.0000098765 and =16000.0000012345 -- works the issue of seeing the exact value (to 15 significant digits), regardless of how they are displayed.

But again, we cannot control how other users do things.

Moreover, A1 is still not captured exactly as it is displayed in the Excel file.

Book1
A
115818.00001
216000
Sheet1
 

Some videos you may like

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,682
Office Version
2010
Platform
Windows
Withdrawn.

The problems seem to be corrected in the latest version.

The moderator can delete this thread. (I don't believe I can.)

Book1
A
115818
216000
Sheet1
 

Watch MrExcel Video

Forum statistics

Threads
1,095,729
Messages
5,446,176
Members
405,390
Latest member
RafalKowalski

This Week's Hot Topics

Top