Excel not doing the sums - random decimal just appearing

Seve OB · 20-11-2018 04:42PM #1

Just something I notice from time to time in Excel

Happing now and really doing my nut in.

A column of numbers, all with 2 places of decimal, I've even given them 30 places of decimal to make sure everything after the first 2 is 0

add them up down the bottom

total has a is x,xxx.xx0000000000100000000000000

so this total should match another, which it does with the exception of the anomaly, and once it matches, there are subsequent formulas in after that

because it is picking up this mad difference, all the formulas are not working

has anyone ever seen excel throw random decimals up like this, and more importantly, figured out how to fix them

DubCount · 20-11-2018 05:24PM

Yes - I have had this come up as well.

Use the =round(formula,number of decimals wanted) and it goes away

Amirani · 20-11-2018 05:31PM

It's basically because of the rounding involved in storing floating point numbers: https://www.microsoft.com/en-us/microsoft-365/blog/2008/04/10/understanding-floating-point-precision-aka-why-does-excel-give-me-seemingly-wrong-answers/

Excel not doing the sums - random decimal just appearing

Comments