Aug 27, 2015

The first thing I’d like to point out about rounding numbers in Excel is that simply hiding decimal places isn’t true rounding; the decimal places are still there, just hidden. Therefore the number is still the same.

Rounding numbers involves using one of the many worksheet functions available that actually change the value so that the decimal places are gone completely.

For example, if two cells contain the number 4.5 and you format the cells to have no decimal places shown, then the cells will look like they contain the number 5 but adding them together will give you 9. Of course most people will notice if their formula appears to be adding 5 and 5 and getting 9, but if there were a larger number of cells with bigger number, the discrepancy may not be noticed.

The ROUND Function

This function rounds a number to a specified number of digits. For example, if cell B4 contains the number 3.141593 and we round to 2 decimal places we would get 3.14:

=ROUND(B4,2)

The ROUND function rounds up or down so if we round cell B4 (which contains 3.141593) to 4 decimal places we would get 3.1416:

=ROUND(B4,4)

The ROUNDUP & ROUNDDOWN Functions

These two work the same way as ROUND except that they always round up (ROUNDUP) or down (ROUNDDOWN).

There are other rounding functions, some of which will be the subject of a future post.

In my next post, I will talk about rounding numbers to a certain 'mutliple of significance'.

For more information, have a look at our Excel Training Courses.