Excel with two digits numbers after decimal has values in the 12th to 16th decimal positions

%3CLINGO-SUB%20id%3D%22lingo-sub-2060657%22%20slang%3D%22en-US%22%3EExcel%20with%20two%20digits%20numbers%20after%20decimal%20has%20values%20in%20the%2012th%20to%2016th%20decimal%20positions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2060657%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20I%20recently%20came%20across%20a%20rounding%20issue%20with%20an%20excel%20sheet%20that%20was%20causing%20problems.%26nbsp%3B%20We%20were%20attempting%20to%20move%20money%20from%20one%20bucket%20to%20another%2C%20so%20the%20total%20sum%20from%20various%20line%20items%20should%20be%20null%20or%20absolute%20zero%2C%20but%20we%20were%20getting%20very%20small%20value%20in%20far%20decimal%20places.%26nbsp%3B%20We%20cleared%20all%20values%20beyond%20the%20second%20decimal%20point%20and%20continued%20to%20get%20a%20value%20that%20was%20not%20absolute%20zero.%26nbsp%3B%20We%20then%20took%20some%20of%20the%20numbers%20with%20two%20decimal%20digits%20and%20hard%20input%20them%20into%20a%20separate%20excel%20sheet.%26nbsp%3B%20The%20same%20issue%20came%20up%20in%20that%20test%20where%20there%20are%20values%20far%20decimal%20positions%20in%20the%20formula%20total%20where%20there%20are%20none%20in%20the%20hard%20input%20numbers%20of%20the%20equation%20values.%26nbsp%3B%20See%20the%20attached%20screenshot%20and%20formula%20sample%20of%20the%20test.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2060657%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2060777%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20with%20two%20digits%20numbers%20after%20decimal%20has%20values%20in%20the%2012th%20to%2016th%20decimal%20positions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2060777%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F928987%22%20target%3D%22_blank%22%3E%40slongazelCU%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20is%20due%20to%20unavoidable%20tiny%20rounding%20errors.%20Change%20the%20formula%20in%20A3%20to%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DROUND(A2-A1%2C2)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESee%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Foffice%2Ftroubleshoot%2Fexcel%2Ffloating-point-arithmetic-inaccurate-result%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EFloating-point%20arithmetic%20may%20give%20inaccurate%20results%20in%20Excel%3C%2FA%3E%20for%20some%20background.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2061167%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20with%20two%20digits%20numbers%20after%20decimal%20has%20values%20in%20the%2012th%20to%2016th%20decimal%20positions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2061167%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F928987%22%20target%3D%22_blank%22%3E%40slongazelCU%3C%2FA%3E%26nbsp%3B%26nbsp%3B%20See%20the%20simple%20explanation%20in%20excelforum.com%20(%3CA%20href%3D%22https%3A%2F%2Fwww.excelforum.com%2Fexcel-formulas-and-functions%2F1337937-simple-formula-with-two-decimal-values-showing-values-in-far-digits.html%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Eclick%20here%3C%2FA%3E).%20When%20you%20%22crosspost%22%2C%20be%20sure%20to%20postings%20in%20other%20forums%20so%20that%20we%20do%20not%20waste%20time%20responding%20to%20questions%20that%20might%20have%20been%20answered%20elsewhere.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi I recently came across a rounding issue with an excel sheet that was causing problems.  We were attempting to move money from one bucket to another, so the total sum from various line items should be null or absolute zero, but we were getting very small value in far decimal places.  We cleared all values beyond the second decimal point and continued to get a value that was not absolute zero.  We then took some of the numbers with two decimal digits and hard input them into a separate excel sheet.  The same issue came up in that test where there are values far decimal positions in the formula total where there are none in the hard input numbers of the equation values.  See the attached screenshot and formula sample of the test.

 

2 Replies

@slongazelCU 

This is due to unavoidable tiny rounding errors. Change the formula in A3 to

 

=ROUND(A2-A1,2)

 

See Floating-point arithmetic may give inaccurate results in Excel for some background.

@slongazelCU   See the simple explanation in excelforum.com (click here). When you "crosspost", be sure to postings in other forums so that we do not waste time responding to questions that might have been answered elsewhere.