With XL2BB, how to show exact value of formula?

[joeu2004]

In another posting, I asserted (incorrectly) that XL2BB cannot show the exact value (at least up to 15 significant digits) of a constant that is formatted to display less precision.

I "withdrew" the assertion when I discovered that the current version of XL2BB does.

But how can I show the exact value (at least up to 15 significiant digits) of the numeric result of a formula that is formatted to display less precision?

For example:

normalize pct chng.xlsx
	A	B	C
1			Acct #1
2	Day#	%Chg	End Bal
3			100,000.00
4	1	-14.53%	85,470.00
5	2	-1.50%	84,187.95
6	3	3.87%	87,446.02
7	4	-4.42%	83,580.91
8	5	6.08%	88,662.63
9
10	Total Chg	-11.34%	88,662.63
Sheet1

The exact value in B10 is -0.11337371288317388 (in XML) or -0.113373712883174+1.11E-16, which Excel displays as -0.113373712883174.

(The exact decimal representation is -0.11337371288317388273725327962893061339855194091796875. But 17 significant digits, rounded, is sufficient to convert binary and decimal with no loss of accuracy.)

When helping users, it would be useful to be able to "see" (or copy) the exact binary value. But at the very least, up to 15 significant digits.

I believe the only alternative is to ask users to upload an example file to a file-sharing website and post the download URL. That's fine with me. But some contributors object to that.

In Excel, we can highlight the formula (or subexpression) in the Formula Bar ("fx" field) and press f9 in order to see the value with up to 15 significant digits.

 

[Peter_SSs]

Perhaps I am not understanding what you are saying. If I use the copy feature to copy your XL2BB dispaly to my worksheet, what I initially see depends on the width of my columns.
Example

However, if I widen my columns and format B10 as percentage with 13 decimal places I see this. Isn't that what you are saying cannot be shown with XL2BB?

 

[joeu2004]

If I use the copy feature to copy your XL2BB dispaly to my worksheet, [... then ...] if I widen my columns and format B10 as percentage with 13 decimal places I see this. Isn't that what you are saying cannot be shown with XL2BB?

Yes. And in your description, it is Excel, not XL2BB, that is showing B10 to 13 dp. QED.

Be that as it may, the steps that you describe might be a suitable work-around, as long as the XL2BB excerpt in the user's posting includes all referenced cells.

If my XL2BB excerpt had contained only row 10, your work-around would not work.

For example, try to display B10 to 13 dp based on the following excerpt.

Book2
	A	B	C
10	Total Chg	-11.34%	88662.63
Sheet1

We have no control over what users include in their XL2BB excerpt. In fact, users might use XL2BB instead of a link to an Excel file for the very reason that they do not want to include all of the referenced cells, or they cannot because it is simply too much to include in a posting.

-----

BTW, when I copy the XL2BB excerpt in my original posting (and the one above), then paste into Excel, I get a #VALUE error in B10.

That is because B10 is not pasted as a array-entered formula.

I think that is a limitation of the copy-and-paste clipboard mechanism, not something that XL2BB can control.

But it makes the work-around less "straight-forward". I must array-enter the formula manually.

Fortunately, my example has only one array-entered formula. Imagine the difficulty if the user's XL2BB excerpt has many more.

Moreover, the corrected formula will not have the same result, because the referenced cells are empty.

-----

Bottom line: I think my issue is still valid: it would be nice if XL2BB per se included a mechanism for seeing the full precision of formulas, just as it does for constants.

Consider the following example.

Book2
	B	C	D
8	6.09%	6.09%	#N/A
Sheet1

(Aside: If you use the XL2BB copy feature, D8 will display 1 instead of #N/A. So the result of the XL2BB copy feature does not always reproduce the problem. B8 is the result of copy-and-paste-value of the result of a formula. C8 is a constant that was entered manually.)

Even though B8 displays 6.09%, we can see the full precision (up to 15 significant digits) by hovering the cursor over the XL2BB cell.

Apparently, that is because the raw XL2BB text contains the field ``XD=v:m|fz:10pt|cls:ww|tx:6.08512345123451E-02`` surrounded by square brackets.

I think I am asking that a similar field be added to the raw XL2BB text for cells with formulas, as well.

(Errata: Maybe not. Not sure how hovering the cursor over the cell would show both the formula and the exact value ("tx" field). "The details are left to the student" (wink).)

-----

However, even 15 significant digits is not sufficient to represent the exact binary value. And sometimes, we need the exact binary value in order to explain anomalous Excel results.

So I wonder if the "tx" presentation can be embellish to show constant and formula cell values in the following form (using B8 as an example):

tx:6.08512345123451E-02 - 4.86E-17

Ideally, avoid the factor at the end if it is 0.00E+0, which is exact zero. Thus, the "tx" presentation will look "normal" for most constants.

This can be created with the following VBA expression:

Replace(Replace(Format(Range("b8"), "0.00000000000000E+0") & _
" + " & Format(Range("b8") - Format(Range("b8"), "0.00000000000000E+0"), "0.00E+0"), "+ -", "- "), _
" + 0.00E+0", "")

(That is 14 zeros after "0.". That format converts all values to 15 significant digits, regardless of magnitude.)

For C8, we see just tx:6.08512345123451E-02.

The difference of -4.86E-17 is necessary to explain why MATCH returns #N/A in D8.

 

[MARK858]

But it makes the work-around less "straight-forward". I must array-enter the formula manually.

I am not going to try and get involved with the rest of the post (mainly because with most of the post it will be down to smozgur to look at to see if any of it will be possible down the line), but with reference to the above if you are entering the formula with Ctrl-Shift + Enter (as you would be) then I would check the Cell formulas box which will produce the message in red below.

This behaviour is no different to what we used to get with both the HTML Maker or Excel Jeanie. There is a bit more of an issue if you changed to 365 as it won't show on XL2BB as an array formula that needs entering on older versions with Ctrl-Shift + Enter.
Obviously there is no issue if the person pasting the data is on 365 as it will calculate automatically anyway.

 

[joeu2004]

if you are entering the formula with Ctrl-Shift + Enter (as you would be) then I would check the Cell formulas box which will produce the message in red below

I am glad for you. (wink)

Once again, I have no control over how __other_users__ create XL2BB excerpts.

And it is for __their__ benefit that we sometimes need to see the exact values, at least to 15 significant digits, if not the exact "binary" value in some form (namely, the way that I suggested).

Moreover, I have no problem recognizing array-entered formulas, even without the Cell Formulas option.

We can see the curly-brace-surrounded formula when we click the XL2BB cell or simply hover over it.

But perhaps my difficulty in copying array-entered formulas from the XL2BB excerpt arises from my use of Excel 2010 instead of Office 365. Mark wrote: ``there is no issue if the person pasting the data is on 365 as it will calculate automatically anyway``.

Be that as it may, I demonstrated that using the XL2BB copy feature is not sufficient for my purposes (to see values of formulas per se with more precision than displayed), in genenal, since the user might not include all referenced cells in the XL2BB excerpt.

I think we beat this horse to death. I was merely trying to convey a suggestion for improvement to the Powers That Be.

It is up to them to weigh the pros and cons and feasibility. I believe I have said everything that I can.

 

[MARK858]

I am glad for you. (wink)

Oh I do enjoy a bit of sarcasm

Once again, I have no control over how __other_users__ create XL2BB excerpts

Moreover, I have no problem recognizing array-entered formulas, even without the Cell Formulas option.

Then there is no issue with the Ctrl-Shift + Enter part raised as you are only doing what you always had to on the board.

 

[joeu2004]

there is no issue with the Ctrl-Shift + Enter part raised as you are only doing what you always had to on the board.

I never said I had an issue with how XL2BB copy works with array-entered formulas. Here, "XL2BB copy" refers to copying from this forum's webpage into Excel.

On the contrary, I dismissed it as ``a limitation of the copy-and-paste clipboard mechanism, not something that XL2BB can control``.

(You pointed out that it is also due to a limitation of the version of Excel that I use. That is, it is a contributing factor.)

-----

The only issue (proposal) that I raised is the ability to see the "full precision" of values of formula, no matter how they are displayed.

XL2BB already provides the ability to see the "full precision" of constant values, no matter how they are displayed.

I would like to see the same feature applied to formula values as well.

(I noted that Peter's work-around -- the XL2BB copy feature -- does not always solve that problem.)

In that context, "full precision" (with quotes) means up to the first 15 significant digits (rounded). That is a formatting limitation of Excel.

As an added bonus, I demonstrated how XL2BB might present the exact binary value (truly the full precision) for both constants and formulas.

Sometimes, that is more helpful than just the first 15 significant digits.

 

[MARK858]

On the contrary, I dismissed it as ``a limitation of the copy-and-paste clipboard mechanism, not something that XL2BB can control``.

No you stated
"I think that is a limitation of the copy-and-paste clipboard mechanism, not something that XL2BB can control"
and then you followed it up with

But it makes the work-around less "straight-forward". I must array-enter the formula manually.

Which gave me the impression that you did have an issue with it.
After your clarification it is now obvious to me that you don't have an issue with that part so all is good ?
Sorry for my misinterpretation.

 

[smozgur]

I am trying to follow the discussion but I am not sure if I get the point.

Book1
	A	B	C
1			Acct #1
2	Day#	%Chg	End Bal
3			100000
4	1	-0.1453	85470
5	2	-0.015	84187.95
6	3	0.0387	87446.02367
7	4	-0.0442	83580.90942
8	5	0.0608	88663.65778
9
10	Total Chg	-11.3363422150863%	88663.65778
Sheet1

Cell Formulas
Range	Formula
C4:C8	C4	=C3*(1+B4)
B10	B10	=PRODUCT(1+B4:B8)-1
C10	C10	=C3*(1+B10)

Formulas are only "displayed text" in XL2BB - no portability concerns since they are supposed to do the math in Excel, where they are supposed to be pasted. They will be still displayed however they are formatted in Excel in XL2BB.
Constant values are different as also noted above. We want to transfer the actual value as "entered" in Excel but still show as formatted in XL2BB. So we store the actual value as a data attribute to be able to copy them back to Excel. In fact - this is not even possible between Excel Desktop and Excel Online - you can copy and paste constants only how they are displayed in Excel, not as actual values.

So, if B8 is actually 0.0608123123 (I changed the value in the range) but formatted as 4 digits then it will show as 0.0608 in XL2BB, but transferred to Excel as 0.0608123123, and formula in C8 will still calculate the correct value in Excel as it depends on the actual value in B8.

Am I missing something?

 

[joeu2004]

I am trying to follow the discussion but I am not sure if I get the point.

I can understand why: too many distracting comments.

Please just focus on my comments in posting #3, but ignore the "aside" about pasting array-entered formulas. (An irrelevant distraction.)

Formulas are only "displayed text" in XL2BB [....] they are supposed to do the math in Excel, where they are supposed to be pasted. [....] Am I missing something?

I believe so, namely: that works only ``as long as the XL2BB excerpt in the user's posting includes all referenced cells. [.... Users might] not want to include all of the referenced cells, or they cannot because it is simply too much to include in a posting``.

See the example where the XL2BB excerpt includes only row 10 in posting #3.

Because B4:B8 is not included, we cannot copy the (array) formula {=PRODUCT(1+B4:B8)-1} into Excel in order to improve the precision of the value that XL2BB displays (as Excel did) only -11.34%.

Constant values are different as also noted above. We want to transfer the actual value as "entered" in Excel but still show as formatted in XL2BB. So we store the actual value as a data attribute to be able to copy them back to Excel.

As I demonstrate with the row-10 example, the same need applies to calculated values, as well.

So why not provide the same "data attribute" for formulas?

(Is it even possible to do? I can imagine that there might a problem with redendering both "fx" and "tx" attributes for the same cell.)

Moreover, even for constants, I demonstrate that showing the actual value "as entered" is not always sufficient.

See the example where the XL2BB excerpt includes row 8 in posting #3.

The constant in B8 was "entered" by copy-and-pasting a calculated value. Consequently, the precision of the value goes beyond 15 significant digits (rounded).

And sometimes we need to see that extra precision, for example to explain why MATCH fails for two constants that appear to be identical.

Posting #3 offers a suggested change to the "tx" attribute so that we can "see" (reconstruct) the exact binary precision when cellValue - Format(cellValue,"0.00000000000000E+0") is non-zero, the latter representing the cellValue to 15 significant digits (rounded).

And of course, such infinitesimal differences are much more common with formulas.

(But again, is that even possible to do? I presume that XL2BB can access the actual cell value, not just the cell text, on the data-capture side when we click Generate Output in Excel.)

-----

I hope that clarifies my points.

What you do with them, if anything, is entirely up to you, of course. No need to explain.

I appreciate your consideration.