Excel Rounding On =SUM

[Davec62]

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

[#2 · Accepted solution]

Hi @Davec62 

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

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

[#3]

@Davec62 

Totals are not rounded, they are exact

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

[#4]

@L z

thanks very much for your solution

Dave

[#5]

@Sergei Baklan

thanks for your additional input

Dave

[#6]

Glad we could help @Davec62 & Thanks for providing feedback

[#7]

@L z. 

Hi

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

[#8]

@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?

[#9]

@Sergei Baklan
As this is for a VAT return I am trying to achieve exact figures. 0.83 x 2 is 1.66.
Thanks

[#10]

@Davec62 

If so why not

=0.83*A2

[#11]

@Sergei Baklan 

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