Error checks number of decimals

error checks
2 posts / 0 new
Last post
Winthrop Morgan A+ 11
Error checks number of decimals

Can anyone help me avoid having an error check flag a discrepancy of 0.00000001 between two dollar amounts?  My error formula is checking whether the sum in one area of the model (by activity) matches the sum in another area of the model (by categories).  So far, the only solution I can think of is changing the IF statement to IF(x<.01,0,1), where x is the difference between the minuend and subtrahends.

Michael Hutchens A+ 189

Hi Winthrop.

The best way to get around this issue is to include a ROUND function in any checks that check for something equaling something else. You will need to use your discretion based on each check to determine the number of decimal places to which to round, but we usually find that 5 decimal places is sufficient.

An example of this can be found in the Balance Sheet module available on the Modano website, in which the balance sheet check has been rounded to 5 decimal places to prevent an error being triggered when net assets is only different to total equity by, for example, 0.0001, as shown below:

We needed to include this ROUND function for the same reason as you need one in your model - i.e. we found that the balance sheet is regularly off by tiny amounts due to Excel limiting decimals (to 15 digits last time I checked).