SOLVED

AutoSum not calculating correctly

%3CLINGO-SUB%20id%3D%22lingo-sub-2846782%22%20slang%3D%22en-US%22%3EAutoSum%20not%20calculating%20correctly%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2846782%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I've%20never%20seen%20this%20before%20and%20can't%20work%20out%20why%20it%20is%20happening.%20The%20numbers%20in%20red%20text%20are%20incorrect%20based%20on%20428%20x%20%C2%A338.31%20or%20428%20x%20%C2%A335.04.%20The%20error%20sorts%20itself%20out%20if%20the%20sum%20of%20B2*C2%20is%20changed%20to%20a%20manual%20entry%20of%20the%20same%20value.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20anyone%20have%20any%20ideas%20on%20why%20this%20could%20be%20happening%3F%20The%20number%20formats%20for%20the%20cells%20are%20correct.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20image-alt%3D%22Screenshot%202021-10-14%20171012.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F317384i1941A361633958D8%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-10-14%20171012.png%22%20alt%3D%22Screenshot%202021-10-14%20171012.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-10-14%20171126a.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F317386i24092AEA7E697F16%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-10-14%20171126a.png%22%20alt%3D%22Screenshot%202021-10-14%20171126a.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2846782%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-2847107%22%20slang%3D%22en-US%22%3ERe%3A%20AutoSum%20not%20calculating%20correctly%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2847107%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1185734%22%20target%3D%22_blank%22%3E%40Richard20201890%3C%2FA%3E%26nbsp%3BThis%20is%20a%20rounding%20issue.%20B2*C2%2C%20unrounded%20equals%20to%2038.306%20which%20is%20not%20equal%20to%2038.31.%20Use%20%3DROUND(B2*C2%2C2)%20in%20stead.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2849199%22%20slang%3D%22en-US%22%3ERe%3A%20AutoSum%20not%20calculating%20correctly%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2849199%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%20Thank%20you.%20I%20thought%20that%20if%20the%20figures%20shown%20in%20cells%20were%20rounded%20it%20would%20not%20make%20a%20difference%20to%20the%20end%20calculation.%20For%20example%20if%20I%20manually%20type%20%C2%A338.31%20(instead%20of%20AutoSum%20B2*C2)%20I%20get%20the%20right%20answer%20(%3F)%20Is%20there%20a%20setting%20I%20can%20adjust%20so%20figures%20don't%20get%20rounded%20to%20save%20me%20using%20the%20formular%20you%20have%20(kindly)%20shown%20me%20above%3F%3C%2FLINGO-BODY%3E
New Contributor

Hi, I've never seen this before and can't work out why it is happening. The numbers in red text are incorrect based on 428 x £38.31 or 428 x £35.04. The error sorts itself out if the sum of B2*C2 is changed to a manual entry of the same value. 

 

Does anyone have any ideas on why this could be happening? The number formats for the cells are correct.

 

Screenshot 2021-10-14 171012.png

Screenshot 2021-10-14 171126a.png

4 Replies

@Richard20201890 This is a rounding issue. B2*C2, unrounded equals to 38.306 which is not equal to 38.31. Use =ROUND(B2*C2,2) in stead.

@Riny_van_Eekelen Thank you. I thought that if the figures shown in cells were rounded it would not make a difference to the end calculation. For example if I manually type £38.31 (instead of AutoSum B2*C2) I get the right answer (?) Is there a setting I can adjust so figures don't get rounded to save me using the formular you have (kindly) shown me above?
best response confirmed by Richard20201890 (New Contributor)
Solution

@Richard20201890 Not sure what you mean by "Autosum B2*C2". It is multiplying the values in two cells and displaying the result. Formatting the result to be displayed with 2 decimals does not change the underlying number. Excel doesn't know you want to calculate further with a precision of 2 decimals unless you tell it to do so with the ROUND function.

Alternatively, in Options > Advanced > When calculating this workbook, you can select to "Set precision as displayed". This will force Excel to calculate with the number "as displayed".

 

Screenshot 2021-10-15 at 11.44.53.png

Thank you for your help.