Excel’s round formula

Excel stores numbers in cells to a great many decimal places. But when you view those numbers in an Excel financial model, you usually only format to a few decimal places. For example in totalling a column of numbers in a spreadsheet, Excel will have added up all the long numbers with lots of decimal places, and then formatted the final total at the bottom to just a few decimal places. This means you can get some infuriatingly strange things can happening in your spreadsheets.

Why rounding can give you a problem in Excel

Because of the way Excel works, storing numbers and then rounding in the formatting, you can get totals which look like they should add up but don’t (generally not great to present those to your finance director client, unless you are sure they are happy about the whole rounding thing in Excel). Most infuriatingly of all is where you have some kind of an error check in your spreadsheet which is showing no error (e.g. total liabilities = total assets) but, because of rounding, Excel refuses to believe the two numbers match.

Using Excel's Round function

Please download the spreadsheet to see an example of the Round function solving this kind of problem.

Round: an example of numbers not adding up

Excel’s Round function provides us with a helpful solution. What we can do is employ the Round function BEFORE the total or check is made. No more confused finance directors! No more checks not checking when really you know they should.

In the next course lesson

Next in the financial modelling course extract we look at using Iserror and Iferror functions to identify and reduce errors in an Excel financial model.

About this online financial modelling course material

You’re looking at a free online course extract covering Excel functions.



...join the conversation...

Your email address will not be published. Required fields are marked *