SOLVED

a*b≠a*b error in excel

%3CLINGO-SUB%20id%3D%22lingo-sub-1841156%22%20slang%3D%22en-US%22%3Ea*b%E2%89%A0a*b%20error%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1841156%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI'm%20currently%20working%20in%20a%20very%20large%20excel%20spreadsheet%20that%20contains%20many%20tiny%20numbers.%20As%20you%20can%20see%20in%20the%20picture%20(I'm%20using%20an%20if%20statement%20which%20is%201%20if%20they%20are%20equal%20and%200%20if%20not)%20it%20says%20that%20AHP31%3DAHO31%20and%20AHP32%3DAHO32%20but%20apparently%20AHO31*AHO32%3DAHP31*AHP32%20is%20false.%20The%20difference%20is%20around%201.3*10%5E-28.%20I%20haven't%20formatted%20any%20cells%20manually%20so%20this%20is%20very%20confusing.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20315px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F230635iEAB57A4892E1EB20%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1841156%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-1841207%22%20slang%3D%22en-US%22%3ERe%3A%20a*b%E2%89%A0a*b%20error%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1841207%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F852899%22%20target%3D%22_blank%22%3E%40Banu1337%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EExcel's%20uses%20IEEE%20754%20-%20see%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%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20short%2C%20you%20cannot%20expect%20results%20to%20be%20exactly%20correct%2C%20so%20you%20should%20round%20the%20result%20of%20formulas.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hello,

I'm currently working in a very large excel spreadsheet that contains many tiny numbers. As you can see in the picture (I'm using an if statement which is 1 if they are equal and 0 if not) it says that AHP31=AHO31 and AHP32=AHO32 but apparently AHO31*AHO32=AHP31*AHP32 is false. The difference is around 1.3*10^-28. I haven't formatted any cells manually so this is very confusing.

 

image.png

 

1 Reply
Best Response confirmed by Banu1337 (Occasional Visitor)
Solution

@Banu1337 

Excel's uses IEEE 754 - see Floating-point arithmetic may give inaccurate results in Excel 

 

In short, you cannot expect results to be exactly correct, so you should round the result of formulas.