Forum Discussion
AutoSum not calculating correctly
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.
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".
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?
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".
