Decimal places and rounding up (or down)
 

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)

--

Regards,

Peo Sjoblom


"Ash" wrote in message
...
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

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

"Peo Sjoblom" wrote in message
...
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)

--

Regards,

Peo Sjoblom


"Ash" wrote in message
...
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

"Bill Kuunders" wrote in message
...
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

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

Ash