Excel Rounding incorrectly starting at 8 decimal places - HELP!!

[Jmorrison677]

Good afternoon All,

I'm currently experiencing a weird event in excel where only at 8 dp's excel is changing the values. See attached workbook.
This is a journal posting with debits and credits.
Note: I have tried using the ROUND to 2 dp, 4 dp function on the numbers in rows 9-219 but still get the same result.

Every number in column's F + G from rows 9-219 is at 2 decimal places.
SUM of debits to 2 dp's = 306,741.38
SUM of credits to 2 dp's = 306,741.38

However when we start to pull the formula out, after 8 dp's it doesnt balance anymore?? But all the numbers the formula is summing are only at 2 dp's, so we are royally confused why this is happening? The problem it is creating is, when this is loaded in to our ERP system, the system thinks the journal doesnt balance, but we cannot identify which line it is (per the attachment) as all lines from 9-219 are only at 2 dp's.....

SUM of debits to 7 dp's = 306,741.3800000
SUM of credits to 7 dp's = 306,741.3800000

SUM of debits to 8 dp's = 306,741.37999999
SUM of credits to 8 dp's = 306,741.38000000

Any help would be appreciated?

Kind regards
Jmorrison677

[AliGW]

Welcome to the forum.

Floating point errors (Google it - it catches many people out).

=ROUND(SUM(F9:F219),2)
=ROUND(SUM(G9:G219),2)

[MrShorty]

Search your favorite knowledge base for floating point error. This has been a part of computer arithmetic since the introduction of floating point data types decades ago.

In Excel, solutions usually revolve around the use of rounding functions to force decimal values to be consistently represented.

[Jmorrison677]

Hi AliGW,

So even though all the numbers in rows 9 - 219 are at 2 decimal places only...excel is somehow changing the numbers?
I would understand it, if each of the numbers had 8 dp's or more, but they dont. Like if you pull the numbers on rows 9-219 to 100 dp's none of the numbers ever go above 2 dp's i.e. 19.920000000000000, the 3rd to infinity digits are zero.....

[AliGW]

So even though all the numbers in rows 9 - 219 are at 2 decimal places only...excel is somehow changing the numbers?

If you Google 'Excel floating point errors' you will be able to read all about how/why it happens. All you need to know is how to overcome it. It isn't someting that you are going to be able to prevent, so you (we all) have to roll with it.

Use ROUND in your SUM calculations.

I would understand it, if each of the numbers had 8 dp's or more, but they dont.

They don't need to have. As I said, read up on it, please.

[AliGW]

Here's a link: https://learn.microsoft.com/en-us/of...ccurate-result

[Jmorrison677]

OK thanks
The ROUND of the SUM does work to get the rounding error resolved, but the file itself is loaded in to our ERP system and the ERP is kicking out the entry saying it doesnt balance - all rows 9-219 as i said above are only at 2 dp's, so would the floating point error occur out-with Excel? or is it because the raw data has originated in excel where the floating point error functionality has occurred that this will always happen?

[AliGW]

I don't know the answer to that question (was it the chicken or the egg?) and I am afraid I know nothing about your ERP system, so I don't think that I can advise any more. If you can live with it in Excel and your ERP accepts the results, then don't use ROUND.

The article I linked to explains it - it's to do with binary code at the end of the day, so in theory any computer program can be susceptible to it, not just Excel.