## DEV Community 👩‍💻👨‍💻

CP

Posted on • Updated on

# How-to Avoid Rounding "Errors" in Excel

While working on a financial calculator using Excel as the "reference of truth", I've run into this rounding error -- payments are off by pennies and it ends up off by dollars over time. Instinctively, I tend to believe Excel must be correct. But after digging into the details, I noticed that there is a round off error with Excel:

While Excel displays only 2-decimals, internally, it has a higher precision. And calculations use the highest internal precisions before rounding off.

And this causes problems for financial calculators, where we assume the precision is always set to 2 decimals.

See the following calculation:

`P = T - I (Principal = Total Payment - Interest Payment)` The principle (P) is off by 1-penny when using Excel, and here is why:

I = 32.93698323
T= 145.20 (fixed amount the user pays monthly)
P = 112.26535914 (calculated with Excel formula with previous round off error)

When displaying only 2 decimals, the numbers become:

I = 32.94
T = 145.20
P = 112.27
Plugging in the formula:
`P = T - I` and you get `112.27 = 112.26`

This does not sound right, does it? Since we are dealing with money--which only has two decimals precisions--to get the correct numbers in Excel, we need to round up both `T` and `I` by using the formula `=ROUND(..., 2)` in Excel.

Use `=ROUND(......, 2)` in your formula to force a 2 decimal precision in Excel

Similarly, while programming the calculator, we have to be mindful that we always round up the result to `.toFixed(2)` before subjecting it to additional calculations.

This maybe something accountants learned in their `Using Excel for Accounting 101` course, but it is something easily missed for non-accountants and would cause a lot of confusion when not done right. Devang Hingu

sometimes, excel not allow to write unrecognized character. right now i just replacing those character by `""` but yet not found perfect solution. CP

I agree, Python is amazing for dealing with numbers! While this is true for developers, we often get the requirements from the business people/product managers and they put the samples in Excel. 🤷‍♂️ Then archive their concept and deliver a solution in a less error prone programming language rather than patching up a buggy, flashy spreadsheet. We had inaccurate Covid 19 stats in the UK due to Excel errors lately, but the horrors are many, and usually buried by companies to save their embarrassment.