Display without rounding

This should work:

Please Login or Register  to view this content.

You can't display the unrounded value; formatting 0.05498381180 as 0.0000 will indeed display 0.0550, but downstream calculations will use the unrounded value.

Originally Posted by dr01allen

I need to display a certain number of decimal places but the displayed value cannot be rounded. It's similar to the Trunc() function but the value of the cell using the Trunc() function does not account for all of the decimal places available in the original number.
[....]
changing the displayed format of the cell results in a rounded value (e.g. 0.0550)
The displayed value needs to be 0.0549 but subsequent calculations need to use all of the available decimals.

First, be sure the option "Precision as displayed" (PAD) is not enabled. If it is enabled, formatting to Number with 4 decimal places, for example, does round the underlying value as well.

If the underlying value is a constant, it is permanently rounded, even after you disable PAD. If the underlying value is derived from a formula, disabling PAD will restore the full precision of the value.

Second, Excel does not have a formatting option to display 4 decimal places and truncate instead of rounding the 5th decimal place.

So if you must truncate the appearance of a value while retaining the full precision of the value, you will need to use two cells: one for display; the other for the actual value. The latter can be hidden.

On the other hand, if you misspoke and rounding the appearance is acceptable as long as the value retains the full precision, simply format as Number with 4 decimal places, for example, with PAD disabled.

Dont confuse the actual contents of a cell, with HOW the contents of a cell is displayed.

A cell can contain up to 15 digits, all of them decimal, but you can format that to show only 3 (or 4 or whatever) decimal places. That is what the cell will show, but that is not what teh cell contains

shg and joeu speak truth.
Can't get there from here unless you use additional columns and hide them.

Originally Posted by joeu2004

First, be sure the option "Precision as displayed" (PAD) is not enabled. If it is enabled, formatting to Number with 4 decimal places, for example, does round the underlying value as well.

If the underlying value is a constant, it is permanently rounded, even after you disable PAD. If the underlying value is derived from a formula, disabling PAD will restore the full precision of the value.

Second, Excel does not have a formatting option to display 4 decimal places and truncate instead of rounding the 5th decimal place.

So if you must truncate the appearance of a value while retaining the full precision of the value, you will need to use two cells: one for display; the other for the actual value. The latter can be hidden.

On the other hand, if you misspoke and rounding the appearance is acceptable as long as the value retains the full precision, simply format as Number with 4 decimal places, for example, with PAD disabled.

To expand on the issue:
I work in a Federally Regulated environment. By law, we cannot use rounded numbers in calculations. We are also required to show the values used in calculations to 2 digits more than the criteria used to evaluate the results. The spreadsheets are printed and used when auditors ask for the data.

The displayed values are used by reviewers to verify results. Using the example data above, the results from un-altered data are significantly different than results from the displayed values. Many auditors recognize the difference and accept review of the formulas as sufficient. One particular auditor is requiring the presented data to be sufficient to replicate the results as displayed.

The presented data is an example of where displayed data is not sufficient.

Adding an additional column or cell for intermediate data is not desirable since any additional cells require additional review by a second person. Hiding the cells is not acceptable.

I still struggle to see the point of this question, if you dont want rounding on the fourth decimal place, then DISPLAY five decimals. I would go tell management excel is designed to round at the level of decimals shown BUT the underlying data does NOT change. This means any calculations off that cell will still be correct.