How to compute the error of the displayed value vs. actual value?

Question

When displaying numbers the cell formatting affects the actual value displayed. (For reference 1/16th == 0.0625.)

For example if the display format is 16ths (# ??/16), any value between approximately 0.04 and 0.09 will be displayed as 1/16.

I'd like another column to display the difference between the displayed value (1/16) and the actual value -- especially for the case when the difference is 0. For the value 0.05, excel displays 1/16, and in the next column I'd like to show 0.0125.

I'd also like this to adjust automatically if I later change the formatting to something else, such as 8ths.

I think this would be fairly easy if I could get the displayed value of a cell. Is that possible?

Ideally a conditional formatting could be applied that changes the color of the value based on the amount of error. Normal black on white text for no error, and black on deep red for lots of error.

You've tagged only `excel`, so I presume you're happy with a VBA solution? It could also be achieved using an Excel 4.0 macro within Name Manager, though in any case either manual user intervention or else a worksheet change event would be required to force a recalculation should the format of the cell being queried change. — Jos Woolley, Mar 19 '23 at 06:42
Neat -- you mean XLM? (Had to look this up on Wikipedia). Sounds like you can only write these macros with older versions of excel though. I assume they (XLM) have their problems but I so often have wanted to have simple formula based macros instead of digging into VBA. The modern answer to this now appears to be lambda functions. Forcing recalculation on format change isn't something I thought about, but it makes sense. — joeking, Mar 19 '23 at 17:46
You can still access the old macro functions through name manager. Here's an example of how: https://stackoverflow.com/questions/15887257/how-to-count-up-text-of-a-different-font-colour-in-excel AND here's a reference on functions available: https://d13ot9o61jdzpp.cloudfront.net/files/Excel%204.0%20Macro%20Functions%20Reference.pdf — Michael, Mar 20 '23 at 00:50

score 2 · Answer 1 · answered Mar 19 '23 at 07:20

Non-VBA solution

This will work without VBA, but may result in you losing some data accuracy. It also requires you to store the original value in another cell to calculate the difference.

Step 1: Excel Options > Advanced > When calculating this workbook > Set precision as displayed

Step 2: Enter original value in a cell without any formatting

Step 3: Use a formula to set a different cell equal to the original cell and apply desired formatting

Step 4: Use a formula to calculate the difference between the cells

Step 5: Optionally, hide the column with the original value, or set the font color to white so it's not visible.

Interesting! I would never have expected to have a workbook global setting. I haven't had a chance to experiment with it yet. While this does solve my problem it sort of inverts the problem -- how to get the original value of "c1" instead of the as displayed value (you solved by using cell b1). — joeking, Mar 19 '23 at 17:37

Michael · Accepted Answer · 2023-03-19T23:31:04.107

1

Excel VBA solution

Add a User Defined Function to a Module in the workbook or in an installed Add-In:

Public Function DisplayDiff(ra As Range)

    Application.Volatile

    DisplayDiff = Evaluate(Application.Trim(ra.Text)) - ra.Value

End Function

Use the formula in a function that references the cell with the value:

Using the WorksheetFunction.Trim function as opposed to the VBA Trim function also replaces double spaces within the string (as opposed to VBA Trim which only removes outside spaces). This allows you to use full fraction formatting of # ??/16 for good alignment.

Application.Volatile helps keep the calculated difference up to date, but it still won't update as soon as you change the format of the cell containing the value. It will update as soon as you change the value in any cell, or select an empty cell and press Del.

edited Mar 19 '23 at 23:31

answered Mar 19 '23 at 07:11

Michael

4,563
2
11
25

Needing to force recalculation on format change isn't something I thought about, but it makes sense - I don't think it will be much of a problem. – joeking Mar 19 '23 at 17:48
This mostly works ... but not for fractions like `1 1/16` -- seems like the extra space that Excel inserts confuses the Evaluate(). A value of `1 11/16` is OK though. Hmm.. the default format for "As sixteenths" uses `# ??/16`. Change that to `# ?/16` fixes this problem. – joeking Mar 19 '23 at 22:55
That's a fair point about not handling the default fraction format. I've updated it to handle the double space. – Michael Mar 19 '23 at 23:30

Michael · Answer 3 · 2023-03-19T23:35:29.103

Alternate VBA Solution

This solution always compares the INTENDED format of the cell to what's actually displayed. This could be useful if values aren't displaying because the values accidentally become too wide for the column width. However, it then doesn't accurately display the actual perceived difference for users looking at decimal places (which the original function does handle).

Public Function DisplayDiff2(ra As Range)

    Application.Volatile

    DisplayDiff2 = Evaluate(Application.Trim(Application.WorksheetFunction.Text(ra, ra.NumberFormat))) - ra.Value

End Function

Applying cell formatting to the cell value is more reliable than .text, which errors out if the column width is too narrow to display the formatted number.

Using the WorksheetFunction.Text function as opposed to the VBA Format function allows Excel formatting input such as fractions which aren't understood by VBA.

Using the WorksheetFunction.Trim function as opposed to the VBA Trim function also replaces double spaces within the string (as opposed to VBA Trim which only removes outside spaces). This allows you to use full fraction formatting of # ??/16 for good alignment.

Application.Volatile helps keep the calculated difference up to date, but it still won't update as soon as you change the format of the cell containing the value. It will update as soon as you change the value in any cell, or select an empty cell and press Del.

Working for fractions not displayed at all:

Not working for decimals not displayed in full:

Thanks. I enhanced this a bit to return a string that includes the text + the error. For example the value 0.1875 with format "# ??/8" displays as "2/8", but my format function returns "2/8 [0.0625]" — joeking, Mar 25 '23 at 20:42

How to compute the error of the displayed value vs. actual value?

3 Answers3