Forum Discussion

Davec62's avatar
Davec62
Copper Contributor
Jun 02, 2023
Solved

Excel Rounding On =SUM

Hi

 

I have created  spreadsheet which calculates VAT and has a total columns. The issue is that the numbers are rounded up or down. I would like them to be exact.

 

I have  =sum(D2:D4) which are 0.17 for column D The total should be 0.51 but it is round to 0.50

 

I have  =sum(E2:E4) which are 8.33 for column E The total should be 2.49 but it is round to 2.50

 

The cells are set as a number format with 2 decimal places. 

 

Any suggestions very much appreciated?

 

Thanks

 

Dave

excel
Formulas and Functions

10 Replies

Sort By
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jun 02, 2023

    Davec62 

    Totals are not rounded, they are exact

    If you'd like to round intermediate values, then as Lorenzo  suggested. Format itself rounds nothing, it only display not rounded value in the way you defined.

     

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Jun 02, 2023

    Hi Davec62 

     

    in E2 (and copy down): =ROUND(C2/1.2,2)

    (D2 can be simplified as: =C2-E2)

    • Davec62's avatar
      Davec62
      Copper Contributor
      Jun 02, 2023
      @L z

      thanks very much for your solution

      Dave

Resources