Excel decimal Rounding?

Question

How can I round a number in excel such as by formatting it so that the actual number changes and not just it's appearance in the cell.

For example, if I have 229.729599999998 in a cell and I format the cell to only show 4 decimal places it would now show 229.7296 in the cell but the value of the cell would still be 229.729599999998 so if I were to do any calculations excel would use the longer decimal number as oppose to the 4 decimal number.

Is there way I can make it so that not just the appearance of the number in the cell shows 4 decimal places but so that the actual value of the cell will reflect the 4 decimal number so that the cell and the value in the formula bar will show 229.7296 and not 229.729599999998.

I know I can probably use a function or formula in an adjacent cell to round the number but I just put together a huge spread sheet and it would take a really long time if I had to do the same thing for all the cells in my spread sheet. Any ideas?

Solution 1

The 2 ways I can think of would be:

• if it is a one off: create a new tab where each cell is calculated based on the original sheet, for example in cell A1: =ROUND(OriginalTab!A1,4) etc. Then copy paste special values back to the original sheet.
• to run a vba macro that would round each cell on your sheet to 4 decimal places.

Solution 2

Just copy the numbers into a sheet and save that as a new CSV. Then close the CSV and open it again and your numbers are rounded...

Solution 3

There is another option: uset the 'Precision as displayed' setting (found under calculation options).

From excel help

Permanently changes stored values in cells from full precision (15 digits) to whatever format, including decimal places, is displayed.

Solution 4

Rounding decimal numbers can be done with a formula where you can change the decimal and not have all the trailing numbers.

I prefer the alternate method but I will show both

First take the value (input or formula) subtract the int and multiply by 100. This will make the decimal value an integer. Use MROUND to round. Now divide this by 100 and add back the original integer.

Example: =((MROUND(((K7-J8)-INT(K7-J8))*100,10))/100)+INT(K7-J8) if the value was 229.729599999998 the new value 229.700000000000

The alternate method Depending on the number of places you want for the decimal. MOD(value,1) which gives you the decimal portion * by 100 for two places 1000 for 3 places 10000 for 4 places etc. MROUND That then divide by the same number you multiplied above Ex. 1000 for 3 places and finally add it back to the integer or value.

Ex. =(MROUND(INT(MOD(U9,1)*1000),10))/1000+INT(U9) This would make the number 229.730

View more solutions

Comments

[ktdrv]

I second that. These are pretty much the two options, which kind of means that the OP is SOL.

[michelson]

I was going to recommend the VBA macro to do a one-time procedural change to the column or columns

[Ross]

Is this rounding or truncating?

[Admin]

@Ross - This setting literally sets the value to whatever is displaying in the cell before you check it. E.g., you have 3.456 in the cell, but have formatted it to display only one decimal place. So, it looks like 3.5 now in the cell. When you checkbox the setting, it will change the cell value to 3.5 permanently. I haven't played much with formulas like ROUND to see what happens there.