Hiding decimals change value

L

Legacy 386498

Guest
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.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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.
 
Last edited:
Upvote 0
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".
 
Upvote 0
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.
 
Upvote 0
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. :)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top