Decimal places and rounding up (or down)

  • Thread starter Thread starter Ash
  • Start date Start date
A

Ash

Need a bit of help please.

Have a spreadsheet showing rents charged out. For example in column 1 annual
rent, column 2 annual rent divided by 12, column 3 VAT is added at 17.5% and
in column 3 the monthly rent is added to the VAT to give a total.

Example

£6,400.00 / 12 months = £533.33 VAT is £93.33 Total = £626.66

But in my total column I get £626.67 and can't seem to get rid of that extra
penny.

Is there any way to force the calculations to work to the nearest penny as
£6,400.00 divided by 12 is actually £533.33333. What I want is it to
disregard anything after the two decimal places.

Hope that all makes sense.

Thanks for any help in advance.

Ash
 
The total is 626.6667 and rounded it is 66.67, if you want to round down to
the nearest penny

=FLOOR((6400/12)*1.175,0.01)
 
Ash

Looks like you need to round each result before you sum them.
Use in b2 =round(A2/12,2)
and in c2 =round(B2*0.175,2)
before you add them together in d2

Regards
Bill K
 
Bill Kuunders said:
Ash

Looks like you need to round each result before you sum them.
Use in b2 =round(A2/12,2)
and in c2 =round(B2*0.175,2)
before you add them together in d2

Regards
Bill K
Click to expand...

Thanks to both who replied. The Round solution seems to be working fine now.

Ash
 
Back
Top