why is excel rounding my totals down

[Lisa]

i am totaling a column with vat formulas in, but the total column keeps
rounding the figuredown. If i enter the amounts manually they add up fine.

[Roger Govier]

Hi Lisa
Can you post some examples of your data, the formula you are using for the
VAT calculation and the formula you are using to total.
Regards

Roger Govier


Lisa wrote:
> i am totaling a column with vat formulas in, but the total column keeps
> rounding the figuredown. If i enter the amounts manually they add up fine.

[Ian]

If your formulae are calculating to more that 2 decimal places, but only
showing 2 DP, you will get a discrepancy in the column total.

eg
1.234 (displayed as 1.23)
2.345 (displayed as 2.35)
3.456 (displayed as 3.46)
4.567 (displayed as 4.57)
sum=11.60

Entering the displayed values manually gives a sum of 11.61.

--
Ian
--
"Lisa" <[email protected]> wrote in message
news:[email protected]...
>i am totaling a column with vat formulas in, but the total column keeps
> rounding the figuredown. If i enter the amounts manually they add up
> fine.

[Lisa]

the formula for the vat is sum(cell)*17.5%. total is just sum(cells). it
seems to be only one entry that is causing the problem. everything is fine
until this cell has a value in it. if i enter the value manually it is fine.

£18,681.75 £3,269.31 £21,951.06

this is the data with the problem.

Thanks, Lisa


"Roger Govier" wrote:

> Hi Lisa
> Can you post some examples of your data, the formula you are using for the
> VAT calculation and the formula you are using to total.
> Regards
>
> Roger Govier
>
>
> Lisa wrote:
> > i am totaling a column with vat formulas in, but the total column keeps
> > rounding the figuredown. If i enter the amounts manually they add up fine.
>

[Ian]

Lisa

I put your data in A1:A3 and summed them in A4. I then did A4*17.5% and got
7682.871.
In B1 I did A1*17.5% and copied this down to row 3. I summed this column at
row 4 and got exactly the same value as before.

Are the 3 values you gave the result of formulae? Is there a rounding error
there?
--
Ian
--
"Lisa" <[email protected]> wrote in message
news:[email protected]...
> the formula for the vat is sum(cell)*17.5%. total is just sum(cells). it
> seems to be only one entry that is causing the problem. everything is
> fine
> until this cell has a value in it. if i enter the value manually it is
> fine.
>
> £18,681.75 £3,269.31 £21,951.06
>
> this is the data with the problem.
>
> Thanks, Lisa
>
>
> "Roger Govier" wrote:
>
>> Hi Lisa
>> Can you post some examples of your data, the formula you are using for
>> the
>> VAT calculation and the formula you are using to total.
>> Regards
>>
>> Roger Govier
>>
>>
>> Lisa wrote:
>> > i am totaling a column with vat formulas in, but the total column keeps
>> > rounding the figuredown. If i enter the amounts manually they add up
>> > fine.
>>

[MattShoreson]

Try the thread below. Might be able to help you.

http://www.excelforum.com/showthread.php?t=383481

[Lisa]

the vat total is made up of 20.13, 3269.31, 924.84, 3094.20, 172.03. the
formula for the total is sum(cells). but the total comes up with 7480.50.
if i enter 3269.31 manually the total then becomes 7480.51.

i have 5 calculations using the formula (cell)*17.5%. all add up correctly
except for the one below. it doesn't like 3269.31.

Rgds, Lisa

"Ian" wrote:

> Lisa
>
> I put your data in A1:A3 and summed them in A4. I then did A4*17.5% and got
> 7682.871.
> In B1 I did A1*17.5% and copied this down to row 3. I summed this column at
> row 4 and got exactly the same value as before.
>
> Are the 3 values you gave the result of formulae? Is there a rounding error
> there?
> --
> Ian
> --
> "Lisa" <[email protected]> wrote in message
> news:[email protected]...
> > the formula for the vat is sum(cell)*17.5%. total is just sum(cells). it
> > seems to be only one entry that is causing the problem. everything is
> > fine
> > until this cell has a value in it. if i enter the value manually it is
> > fine.
> >
> > £18,681.75 £3,269.31 £21,951.06
> >
> > this is the data with the problem.
> >
> > Thanks, Lisa
> >
> >
> > "Roger Govier" wrote:
> >
> >> Hi Lisa
> >> Can you post some examples of your data, the formula you are using for
> >> the
> >> VAT calculation and the formula you are using to total.
> >> Regards
> >>
> >> Roger Govier
> >>
> >>
> >> Lisa wrote:
> >> > i am totaling a column with vat formulas in, but the total column keeps
> >> > rounding the figuredown. If i enter the amounts manually they add up
> >> > fine.
> >>
>
>
>

[Roger Govier]

Hi Lisa

You don't need sum(cell)*17.5% just A1*17.5% will suffice.
That having been said, entering 18861.75 on my machine and multiplying by
17.5% gives 3269.30625 which shows as 3269.31 when displayed to 2 decimal
places.

If you have a multiline invoice, then because the individual amounts will be
different from that which you are "seeing" on the screen, the sum of them
may well be less than the amount you would get if you totalled the displayed
values.

As far as HMCE are concerned, a diference of .01 in the VAT amount is quite
acceptable.



Regards

Roger Govier


Lisa wrote:
> the formula for the vat is sum(cell)*17.5%. total is just sum(cells). it
> seems to be only one entry that is causing the problem. everything is fine
> until this cell has a value in it. if i enter the value manually it is fine.
>
> £18,681.75 £3,269.31 £21,951.06
>
> this is the data with the problem.
>
> Thanks, Lisa
>
>
> "Roger Govier" wrote:
>
>
>>Hi Lisa
>>Can you post some examples of your data, the formula you are using for the
>>VAT calculation and the formula you are using to total.
>>Regards
>>
>>Roger Govier
>>
>>
>>Lisa wrote:
>>
>>>i am totaling a column with vat formulas in, but the total column keeps
>>>rounding the figuredown. If i enter the amounts manually they add up fine.
>>

[Ian]

As Roger Govier points out in his response, this is a rounding error. You
can either live with the slight discrepency in the total, or round each of
the VAT calculations to 2DP. To do this, change your VAT calculation ot read
eg =ROUND(A1*17.5%,2).

--
Ian
--
"Lisa" <[email protected]> wrote in message
news:[email protected]...
> the vat total is made up of 20.13, 3269.31, 924.84, 3094.20, 172.03. the
> formula for the total is sum(cells). but the total comes up with 7480.50.
> if i enter 3269.31 manually the total then becomes 7480.51.
>
> i have 5 calculations using the formula (cell)*17.5%. all add up
> correctly
> except for the one below. it doesn't like 3269.31.
>
> Rgds, Lisa
>
> "Ian" wrote:
>
>> Lisa
>>
>> I put your data in A1:A3 and summed them in A4. I then did A4*17.5% and
>> got
>> 7682.871.
>> In B1 I did A1*17.5% and copied this down to row 3. I summed this column
>> at
>> row 4 and got exactly the same value as before.
>>
>> Are the 3 values you gave the result of formulae? Is there a rounding
>> error
>> there?
>> --
>> Ian
>> --
>> "Lisa" <[email protected]> wrote in message
>> news:[email protected]...
>> > the formula for the vat is sum(cell)*17.5%. total is just sum(cells).
>> > it
>> > seems to be only one entry that is causing the problem. everything is
>> > fine
>> > until this cell has a value in it. if i enter the value manually it is
>> > fine.
>> >
>> > £18,681.75 £3,269.31 £21,951.06
>> >
>> > this is the data with the problem.
>> >
>> > Thanks, Lisa
>> >
>> >
>> > "Roger Govier" wrote:
>> >
>> >> Hi Lisa
>> >> Can you post some examples of your data, the formula you are using for
>> >> the
>> >> VAT calculation and the formula you are using to total.
>> >> Regards
>> >>
>> >> Roger Govier
>> >>
>> >>
>> >> Lisa wrote:
>> >> > i am totaling a column with vat formulas in, but the total column
>> >> > keeps
>> >> > rounding the figuredown. If i enter the amounts manually they add
>> >> > up
>> >> > fine.
>> >>
>>
>>
>>