AutoSum not calculating correctly

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)

@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.