Forum Discussion

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

Expenses and Income Spreadsheets Revised - Nov 22.xlsx15 KB

excel

Formulas and Functions

Lorenzo
Jun 02, 2023
Hi Davec62

in E2 (and copy down): =ROUND(C2/1.2,2)
(D2 can be simplified as: =C2-E2)

Davec62

Copper Contributor

Jul 03, 2023

Lorenzo

sorry to bother you again. I am now about to use the sheet with the formulas suggested. However if I enter 2 in A2 the total for C2 is then 1.67 when it should be 1.66. Is there a way to overcome this. This means B2 is 0.33 when it should be 0.34.

Any advice would be greatly appreciated

Thanks

Dave

VAT Formula.xlsx11 KB

SergeiBaklan

Diamond Contributor

Jul 03, 2023

Davec62

2/1.2 is about 1.6666666667. What is the logic why it shall be 1.66, you trunk all decimals after first two?

Davec62
Copper Contributor
Jul 03, 2023
SergeiBaklan
As this is for a VAT return I am trying to achieve exact figures. 0.83 x 2 is 1.66.
Thanks
- SergeiBaklan
  Diamond Contributor
  Jul 03, 2023
  Davec62
  
  If so why not
  
  =0.83*A2
  - Davec62
    Copper Contributor
    Jul 04, 2023
    SergeiBaklan
    
    Column A is the gross price inc VAT, C is the price excluding VAT and B is the amount of VAT.
    
    So the formula as it is works apart from the fact that figures are rounded up. It may be a case that what I am trying to achieve is not possible.
    
    **** SOLVED ****
    
    I think I may have solved this by using =ROUNDDOWN(C2/1.2,2). I have tested a number of quantities and the amounts calculate correctly.
    
    Thanks
    
    Dave