SOLVED

AutoSum not calculating correctly

Copper 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 (Copper 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. :)
1 best response

Accepted Solutions
best response confirmed by Richard20201890 (Copper 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

View solution in original post