From the Not Just Numbers blog:
Anyone who works with numbers for a living knows all too well the perils of rounding errors.
One of the positive aspects of Excel is that, by default, it doesn’t round numbers. It stores (and uses) the full number, even if it is only displaying, say, two decimal places.
This is great for ensuring that calculations are accurate, but can lead to some apparent oddities when adding up calculated figures.
You can run the risk of displaying, say, three figures with a total and the total appears not to add up. This is because the numbers are displayed to, say, two decimal places, despite the fact that the numbers underneath might have four or five.The complete numbers do add up, but the displayed numbers don’t.
This is where you need to know how to round in Excel.
Excel offers three rounding functions (ROUND, ROUNDDOWN and ROUNDUP) which all work in a similar way.
The ROUND function has the following syntax:
=ROUND(Number,number of decimal places)
This rounds “Number” to the number of decimal places specified using the rule I was taught at school – round down up to 4 and round up 5 and above. The number of decimal places can be negative to allow you to round to the nearest ten (-1), hundred (-2), etc.
The other two rounding functions work in exactly the same way, except ROUNDDOWN always rounds down and ROUNDUP always rounds up.
One other point to mention is the Excel setting (in Options -> Advanced) to “Set precision as displayed”. This calculates formulae based upon the displayed number of decimal places, throughout the whole workbook. In my opinion, this is a very dangerous way to address the issue. Just imagine the damage if you format a section of a worksheet to display as a whole number – not realising that there is a percentage within that range that is used in calculations. Any percentage below 50% would be evaluated as zero, and any above 50% as 100%. Far better that any rounding used in the spreadsheet is done deliberately, using the functions above.
Why not treat yourself and take a look at our Excel Expert Advanced Excel Training?
If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report “The 5 Excel features that you NEED to know”.