CODEWITHSUNDEEP
excelexcel-formula
Jend DimShu
Jend DimShu

Reputation: 97

Excel Weird Decimal Point Calculation

We have some weird calculation scenario in Microsoft excel, this simple addition operation resulting 1 at 13 decimal digit whereby it should be zero

enter image description here

But when i extract the value in formula the result is correct (both formula value is the same but the result is different)

enter image description here

From human understanding addition calculation will reduce the decimal digit rather than add it. Is this by design or bugs ?

Upvotes: 0

Views: 913

Answers (1)

JulianG
JulianG

Reputation: 442

I strongly guess that this is a gap from the limited precision of floating point numbers. Accuracy of digital numbers is limited. Numbers in excel are saved in binary format(but displayed in decimal format). This means that the "0" is not as protected as in the decimal system. Usually Excel tries to cover this up for examples like yours.

Also, if your numbers derive from complex calculations (e.g. square-roots), the accuracy can be limited as most functions use approximation with limited iterations to give a result.

You can find more information about floating point arithmetic here. The blog is about pythin but the way it works is similar. https://docs.python.org/3/tutorial/floatingpoint.html

Upvotes: 1

Related Questions