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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
news:[email protected]...
> 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