Excel changing value
Hello, i am using excel 2013 on a "excel 1997-2003" spreadsheet.
i have a simple formula with a simple result. result should be 3 decimals or 4, cant be any more. yet i notice that excel didnt round it up right. when I uped the number of decimals to 15-16 digit i noticed it changed my number.
value being calculated is as follow : =+ABS(((H24+H23)/2)-((N24+N23)/2))
H24: 2.561
H23: 2.575
N24: 2.584
N23: 2.593
answer is : 0.0205 i want only 3 decimals (should round off to 0.021) excel rounds it off to 0.020
if i up the number of digit it changes into 0,0204999999999997.
WHY???!!!! help please!
i wrote the the value it was supposed to be over the formula, but thats only a quick fix.
can someone help me? or at least explain
Thank you
Hi Tania,
That's the nature of floating point operations, see more at 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/ and you may find other articles.
To correct, round all intermediate calculations (that's what usual practice for accountants who use Excel) like
=ABS(ROUND((H24+H23)/2,3)-ROUND((N24+N23)/2,3))
