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".
Rather unclear, quite frankly. No idea how to use this in pratical terms. Can number be a cell reference? Must the unrounded number appear on the spreadsheet in addition to the rounded number?
ReplyDeleteAn example would be useful.
ReplyDeleteAnonymous
ReplyDeleteSorry it hasn't come across as clear.
Typically "Number" will be a calculation, rather than a direct reference to a number.
Example:
If cell A1 was to contain a number, let's say 20, and cell B1 was to include the formula:
=A1/3
and the number format in cell B1 was set to 2 decimal places.
B1 would display 6.67
but, if you were to use B1 in a formula, the value of B1 is actually 6.666666666666666666666666666666.... and this is what would be used in the calculation.
If we replaced the formula in cell B1 with:
=ROUND(A1/3,2)
The value held in cell B1 (and used in any calculations) would actually be 6.67.
Does that help?
With the example, that's really useful to know, Paul Lynam
ReplyDeleteFor many practical purposes, I find MROUND to be more useful than ROUND. Thanks.
ReplyDeleteThanks Kevin
ReplyDeleteTo be honest, I wasn't aware of MROUND until a reader pointed it out as a comment on this post in a LinkedIn group.
There's always more to learn!
It looks very useful, and I think I might cover it in a future post
I too was unaware of the MROUND function. Quickly reviewing it, for the most part it seems to be the inverse of ROUND. That is to say, =ROUND(A1,2) will yield the same result as =MROUND(A1,.01), which is rounding to the nearest penny. Likewise, =ROUND(A1,-2) will yield the same result as =MROUND(A1,100), which is rounding to the nearest hundred.
ReplyDeleteWhere MROUND will shine is when you want to round to a value other than a multiple of 10, which I have needed to do on occasion. Let's say I wanted to round to the nearest 40. Before the happy discovery of MROUND, I would have used =ROUND(A1/4,-1)*4. The simpler formula is MROUND(A1,40).
The examples are very useful & helps understand the tip better.Thanks to all.
ReplyDeleteplease advise me to how to add non-roundup decimal cell with round up decimal cell which result will be shown in non roundup format and also the result will be display as the sum after the roundup not the result calculate by the excel as its done exactly.
ReplyDeleteI want the exact formula for solving for e.g.
add. Result of 10.7+25.7+24.4+25=85.8(as excel done)
but i want the ans as 11(round up 10.7)+26(round up 25.7)+24.4(non-round up )+25(non-round up)=86.4(non-round up) rather than 85.8 or 86.
urgently revert soon
Bikash
DeleteJust use the ROUNDUP function on those numbers you want to round up.
=ROUNDUP (10.7,0)+ROUNDUP (25.7,0)+24.4+25
Thanks Kevin.
Delete