Hiding decimals change value

[Legacy 386498]

Hello,

I have an Excel file that is pretty simple, 2 cells are link to other tabs and the third one sums the first two. Problem is I want to hide the decimal (whole numbers only), but when I decrease the decimals in the first two columns, the decimal disappears. So for example, if we take cell A6 (192.93) and you decrease one decimal, the sum shows 192.90 and not 192.93. And if you undo, cell A6 is now 192.90 instead of the 192.93 we had initially.

Also, if you hide the decimals from the first sum (cell C3), cell F3 will show an error (#DIV/0!), even if the sum is higher than 0.

I've uploaded the file here:

http://www.filedropper.com/excel

And another one to show that in this new file if you decrease the decimals from cell C2 so it becomes 0, you'll see that cell F2 doesn't become #DIV/0!, the result stayed the same whether you can see the decimals or not. So I'm trying to know why it isn't the same with my other file.

http://www.filedropper.com/book1_20

Thank you and have a nice day.

 

[joeu2004]

You have the option "Precision as displayed" set, and this is one of the dangers of that option.

When PAD is set and you change the cell format to reduce the number of decimal places displayed, constant numbers (like column A) are irreversibly rounded down.

Even if you deselect PAD, the constant numbers retain their last displayed values, not their original values.

I hope you kept a backup copy of the Excel file before you set PAD, or at least before you changed cell formats to reduce the displayed decimal places.

If you did not, you're SOL.

By the way, formula results are also rounded the number of decimal places displayed. Of course, that it is the purpose of PAD. But the point is: changes to formula results are not irreversible.

If you deselect PAD or otherwise increase the number of displayed decimal places, formula results will change appropriates.

 

[joeu2004]

errata, too late to edit:

constant numbers (like column A) are irreversibly rounded down.
[....]
formula results are also rounded the number of decimal places displayed.

Just rounded, not "rounded down".

And "rounded to the number".

 

[Rick Rothstein]

Problem is I want to hide the decimal (whole numbers only), but when I decrease the decimals in the first two columns, the decimal disappears. So for example, i[/COLOR]f we take cell A6 (192.93) and you decrease one decimal, the sum shows 192.90 and not 192.93. And if you undo, cell A6 is now 192.90 instead of the 192.93 we had initially.

I am not sure what you mean specifically by "decrease the decimals", but it sounds like you want the cells to retain their actual value but to display the rounded values. You can do that using Cell Formatting. Just change the column's Cell Format to "Number" and set the number of decimal places to 0... a value of 192.93 will displace as 193 but the cell will still retain the actual value of 192.93.

 

[Legacy 386498]

Oh wow, that did the trick thanks a lot!!

It wasn't my file so I didn't check that option (to be honest, I didn't even know it existed and I don't know why anyone would check it).

I didn't have a backup, but unchecking it fix the problem. I have Office 2016, maybe the old version you were SOL? Or I'm just lucky, anyway thanks a ton.

 

[joeu2004]

Oh wow, that did the trick thanks a lot!! [....] I don't know why anyone would check it

You're welcome! Setting PAD might make sense to do in a new workbook, where the dangers are less likely to arise. It is easier to set PAD than to explicitly round (using the ROUND function) wherever you want calculations to be actually rounded to the way that they appear. But I think setting PAD in a pre-existing workbook is dangerous and unwise.

I didn't have a backup [....] maybe the old version you were SOL? Or I'm just lucky

I think the latter. You're own description demonstrates that the "SOL" problem exists in your version of Excel. You wrote: ``if we take cell A6 (192.93) and you decrease one decimal, the sum shows 192.90 and not 192.93. And if you undo, cell A6 is now 192.90 instead of the 192.93 we had initially``.

Oh well, "ignorance is bliss". As long as things look okay now, that's all that matters. The important take-away is: PAD is dangerous, and it should not be used in a pre-existing workbook without taking precautions, at the very least.