Forum Discussion

Richard20201890's avatar
Richard20201890
Copper Contributor
Oct 14, 2021

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 ...
Excel
Formulas and Functions
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Oct 15, 2021

    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's avatar
Richard20201890
Copper Contributor
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?
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Oct 15, 2021

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's avatar
    Richard20201890
    Copper Contributor
    Oct 15, 2021
    Thank you for your help. 🙂

Resources