Newbie, Help needed with rounding errors

You say that "I cant round the figures in the query because this would give
incorrect totals."

But how can 239.5074 be a *correct* total? When did someone last pay you
0.0074 cents?

"John" <bravo6.DeleteThis@gofree.indigo.ie> wrote in message
news:2iook.26695$j7.472556@news.indigo.ie...
>I have a table which stores transctions with a currency price field.
> I use a query to put together relevant info from several tables including
> the sum of the price grouped by invoice.
> and a calculated field for sales tax.
> My problem is that when I print the invoice report the subtotal +tax dont
> always equal Invoice total as displayed.
>
> One example here
> subtotal 239.5074 displays after rounding as 239.51
> Tax 50.2965 displays after rounding as 50.30
> Invoice total = 289.8039 which of course displays as 289.80
> However this is incorrect as the rounded figures of 239.51 + 50.30 should
> equal 289.81.
> I cant round the figures in the query because this would give incorrect
> totals.
> This must be a common problem, what is the usual solution
>
> Any help greatly appreciated
> Regards John
>

That's exactly the problem because day-to-day consumer items are sold at a
quoted gross price not net price. It derives from computing the net price
and tax from this, e.g. if the gross price of an item is quoted as 30.00 GBP
and the tax rate is 17.5 per cent (the standard rate of VAT in the UK) then
the net price computes as 30 / 1.175 = 25.5319.

Ken Sheridan
Stafford, England

"bcap" wrote:

> You say that "I cant round the figures in the query because this would give
> incorrect totals."
>
> But how can 239.5074 be a *correct* total? When did someone last pay you
> 0.0074 cents?
>
> "John" <bravo6 DeleteThis @gofree.indigo.ie> wrote in message
> news:2iook.26695$j7.472556@news.indigo.ie...
> >I have a table which stores transctions with a currency price field.
> > I use a query to put together relevant info from several tables including
> > the sum of the price grouped by invoice.
> > and a calculated field for sales tax.
> > My problem is that when I print the invoice report the subtotal +tax dont
> > always equal Invoice total as displayed.
> >
> > One example here
> > subtotal 239.5074 displays after rounding as 239.51
> > Tax 50.2965 displays after rounding as 50.30
> > Invoice total = 289.8039 which of course displays as 289.80
> > However this is incorrect as the rounded figures of 239.51 + 50.30 should
> > equal 289.81.
> > I cant round the figures in the query because this would give incorrect
> > totals.
> > This must be a common problem, what is the usual solution
> >
> > Any help greatly appreciated
> > Regards John
> >
>
>
>

You got cut off mid-sentence John!

"Also, you might want to be aware how round works. Since it uses bankers
rounding."

"John Spencer" <spencer.TakeThisOut@chpdm.edu> wrote in message
news:OJ8EwbT$IHA.1224@TK2MSFTNGP02.phx.gbl...
> Since I don't know how you are doing the calculations it is difficult to
> be specific, but I would try
>
> Round(SubTotal,2) + Round(Tax,2)
>
> By the way, you might check the rules in your jurisdiction on calculating
> Tax. Some jurisdictions always round up to the next penny.
>
> Also, you might want to be aware how round works. Since it uses
>
> John Spencer
> Access MVP 2002-2005, 2007-2008
> The Hilltop Institute
> University of Maryland Baltimore County
>

What you say is obviously correct as far as it goes, but that doesn't mean
that someone sold you an item for 25.5319 pounds. If the seller gave you a
VAT invoice for that item, it would show the gross price was 25.53, and the
VAT was 4.47. You need to round at the most granular level.

So, I would suggest, that by coming up with a sub-total of 239.5074 the OP
has already erred. All the items making up that sub-total should have been
individually rounded.

I would suggest that he has also erred by applying tax to a total, instead
of to each individual item. Assuming that the items are priced gross, then
the invoice total will need to add up to the total of the gross prices,
otherwise eyebrows will be raised. But, by totalling the net prices and
then applying the tax to the total, you guarantee that the invoice total
will often *not* add up to the total of the gross prices.

The problem with this approach is compounded when you consider that most
jurisdictions have different sales tax rates for different products. Here
in the UK, we have two rates: zero and 17.5%. In the Republic of Ireland
(where the OP is from it would seem) they have three rates: zero, 13.5% and
21%. So, how can you calculate the tax on a total when the constituents of
that total might need different rates applied to them?

"Ken Sheridan" <KenSheridan.TakeThisOut@discussions.microsoft.com> wrote in message
news:79A9561A-F6CF-45BA-B6C0-5C2EEBE8EC89@microsoft.com...
> That's exactly the problem because day-to-day consumer items are sold at a
> quoted gross price not net price. It derives from computing the net price
> and tax from this, e.g. if the gross price of an item is quoted as 30.00
> GBP
> and the tax rate is 17.5 per cent (the standard rate of VAT in the UK)
> then
> the net price computes as 30 / 1.175 = 25.5319.
>
> Ken Sheridan
> Stafford, England
>
> "bcap" wrote:
>
>> You say that "I cant round the figures in the query because this would
>> give
>> incorrect totals."
>>
>> But how can 239.5074 be a *correct* total? When did someone last pay you
>> 0.0074 cents?
>>
>> "John" <bravo6.TakeThisOut@gofree.indigo.ie> wrote in message
>> news:2iook.26695$j7.472556@news.indigo.ie...
>> >I have a table which stores transctions with a currency price field.
>> > I use a query to put together relevant info from several tables
>> > including
>> > the sum of the price grouped by invoice.
>> > and a calculated field for sales tax.
>> > My problem is that when I print the invoice report the subtotal +tax
>> > dont
>> > always equal Invoice total as displayed.
>> >
>> > One example here
>> > subtotal 239.5074 displays after rounding as 239.51
>> > Tax 50.2965 displays after rounding as 50.30
>> > Invoice total = 289.8039 which of course displays as 289.80
>> > However this is incorrect as the rounded figures of 239.51 + 50.30
>> > should
>> > equal 289.81.
>> > I cant round the figures in the query because this would give incorrect
>> > totals.
>> > This must be a common problem, what is the usual solution
>> >
>> > Any help greatly appreciated
>> > Regards John
>> >
>>
>>
>>
>

The best practice, i.e. that which produces the most accurate result, is to
compute VAT on each total net price of all items attracting the same rate of
tax, rounding the results in each case, and then summing the subtotals to get
the total invoice amount. Examining a few invoices from my own UK suppliers
where both standard and zero-rated goods or services are supplied I find that
they all conform to this practice, though maybe I'm just lucky and I deal
with suppliers who adopt good accounting practices. I've no doubt that
there are cowboys out there who'd do otherwise if they could make a few extra
quid here and there out of it.

I also see on my suppliers' invoices that the sum of the individual line
amounts net of VAT does not always tally with the total net amount, the
former being rounded per line item, the latter being a rounding of the sum of
the underlying unrounded values. So my supplier is in fact charging me
25.5319 GBP, not the 25.53 GBP which appears in the invoice line, or to be
more accurate they are charging me 25.5319 GBP adjusted pro rata the rounded
total net amount for all items attracting standard rate VAT. I could work
that out from the invoice in front of me, but life is too short!

BTQ, while on the subject of rounding in general you might find the
following of interest:


http://www.pldesignline.com/howto/showArticle.jhtml?articleID=175801189


Ken Sheridan
Stafford, England

"bcap" wrote:

> What you say is obviously correct as far as it goes, but that doesn't mean
> that someone sold you an item for 25.5319 pounds. If the seller gave you a
> VAT invoice for that item, it would show the gross price was 25.53, and the
> VAT was 4.47. You need to round at the most granular level.
>
> So, I would suggest, that by coming up with a sub-total of 239.5074 the OP
> has already erred. All the items making up that sub-total should have been
> individually rounded.
>
> I would suggest that he has also erred by applying tax to a total, instead
> of to each individual item. Assuming that the items are priced gross, then
> the invoice total will need to add up to the total of the gross prices,
> otherwise eyebrows will be raised. But, by totalling the net prices and
> then applying the tax to the total, you guarantee that the invoice total
> will often *not* add up to the total of the gross prices.
>
> The problem with this approach is compounded when you consider that most
> jurisdictions have different sales tax rates for different products. Here
> in the UK, we have two rates: zero and 17.5%. In the Republic of Ireland
> (where the OP is from it would seem) they have three rates: zero, 13.5% and
> 21%. So, how can you calculate the tax on a total when the constituents of
> that total might need different rates applied to them?
>
> "Ken Sheridan" <KenSheridan RemoveThis @discussions.microsoft.com> wrote in message
> news:79A9561A-F6CF-45BA-B6C0-5C2EEBE8EC89@microsoft.com...
> > That's exactly the problem because day-to-day consumer items are sold at a
> > quoted gross price not net price. It derives from computing the net price
> > and tax from this, e.g. if the gross price of an item is quoted as 30.00
> > GBP
> > and the tax rate is 17.5 per cent (the standard rate of VAT in the UK)
> > then
> > the net price computes as 30 / 1.175 = 25.5319.
> >
> > Ken Sheridan
> > Stafford, England
> >
> > "bcap" wrote:
> >
> >> You say that "I cant round the figures in the query because this would
> >> give
> >> incorrect totals."
> >>
> >> But how can 239.5074 be a *correct* total? When did someone last pay you
> >> 0.0074 cents?
> >>
> >> "John" <bravo6 RemoveThis @gofree.indigo.ie> wrote in message
> >> news:2iook.26695$j7.472556@news.indigo.ie...
> >> >I have a table which stores transctions with a currency price field.
> >> > I use a query to put together relevant info from several tables
> >> > including
> >> > the sum of the price grouped by invoice.
> >> > and a calculated field for sales tax.
> >> > My problem is that when I print the invoice report the subtotal +tax
> >> > dont
> >> > always equal Invoice total as displayed.
> >> >
> >> > One example here
> >> > subtotal 239.5074 displays after rounding as 239.51
> >> > Tax 50.2965 displays after rounding as 50.30
> >> > Invoice total = 289.8039 which of course displays as 289.80
> >> > However this is incorrect as the rounded figures of 239.51 + 50.30
> >> > should
> >> > equal 289.81.
> >> > I cant round the figures in the query because this would give incorrect
> >> > totals.
> >> > This must be a common problem, what is the usual solution
> >> >
> >> > Any help greatly appreciated
> >> > Regards John
> >> >
> >>
> >>
> >>
> >
>
>
>

Well it depends partly on whether your suppliers are pricing net (as is the
normal practice business-to-business) or gross (as is the normal practice in
retailing). If you go into a shop and buy 10 widgets priced at £30.00 each
and 5 gizmos priced at £10 each the invoice must add up to £350.00, not
£349.99 or £350.01.

It doesn't really matter what the gross total is when pricing net, but
nevertheless the invoice needs to be internally consistent: the total of a
column of net prices must equal the sum of those prices, likewise tax and
gross. At least, this is what my customers expect from the order processing
systems I build for them.

"Ken Sheridan" <KenSheridan.RemoveThis@discussions.microsoft.com> wrote in message
news:18B390CF-00BD-4348-9718-2B2E55B67821@microsoft.com...
> The best practice, i.e. that which produces the most accurate result, is
> to
> compute VAT on each total net price of all items attracting the same rate
> of
> tax, rounding the results in each case, and then summing the subtotals to
> get
> the total invoice amount. Examining a few invoices from my own UK
> suppliers
> where both standard and zero-rated goods or services are supplied I find
> that
> they all conform to this practice, though maybe I'm just lucky and I deal
> with suppliers who adopt good accounting practices. I've no doubt that
> there are cowboys out there who'd do otherwise if they could make a few
> extra
> quid here and there out of it.
>
> I also see on my suppliers' invoices that the sum of the individual line
> amounts net of VAT does not always tally with the total net amount, the
> former being rounded per line item, the latter being a rounding of the sum
> of
> the underlying unrounded values. So my supplier is in fact charging me
> 25.5319 GBP, not the 25.53 GBP which appears in the invoice line, or to be
> more accurate they are charging me 25.5319 GBP adjusted pro rata the
> rounded
> total net amount for all items attracting standard rate VAT. I could work
> that out from the invoice in front of me, but life is too short!
>
> BTQ, while on the subject of rounding in general you might find the
> following of interest:
>
>
> http://www.pldesignline.com/howto/showArticle.jhtml?articleID=175801189
>
>
> Ken Sheridan
> Stafford, England
>
> "bcap" wrote:
>
>> What you say is obviously correct as far as it goes, but that doesn't
>> mean
>> that someone sold you an item for 25.5319 pounds. If the seller gave you
>> a
>> VAT invoice for that item, it would show the gross price was 25.53, and
>> the
>> VAT was 4.47. You need to round at the most granular level.
>>
>> So, I would suggest, that by coming up with a sub-total of 239.5074 the
>> OP
>> has already erred. All the items making up that sub-total should have
>> been
>> individually rounded.
>>
>> I would suggest that he has also erred by applying tax to a total,
>> instead
>> of to each individual item. Assuming that the items are priced gross,
>> then
>> the invoice total will need to add up to the total of the gross prices,
>> otherwise eyebrows will be raised. But, by totalling the net prices and
>> then applying the tax to the total, you guarantee that the invoice total
>> will often *not* add up to the total of the gross prices.
>>
>> The problem with this approach is compounded when you consider that most
>> jurisdictions have different sales tax rates for different products.
>> Here
>> in the UK, we have two rates: zero and 17.5%. In the Republic of Ireland
>> (where the OP is from it would seem) they have three rates: zero, 13.5%
>> and
>> 21%. So, how can you calculate the tax on a total when the constituents
>> of
>> that total might need different rates applied to them?
>>
>> "Ken Sheridan" <KenSheridan.RemoveThis@discussions.microsoft.com> wrote in message
>> news:79A9561A-F6CF-45BA-B6C0-5C2EEBE8EC89@microsoft.com...
>> > That's exactly the problem because day-to-day consumer items are sold
>> > at a
>> > quoted gross price not net price. It derives from computing the net
>> > price
>> > and tax from this, e.g. if the gross price of an item is quoted as
>> > 30.00
>> > GBP
>> > and the tax rate is 17.5 per cent (the standard rate of VAT in the UK)
>> > then
>> > the net price computes as 30 / 1.175 = 25.5319.
>> >
>> > Ken Sheridan
>> > Stafford, England
>> >
>> > "bcap" wrote:
>> >
>> >> You say that "I cant round the figures in the query because this would
>> >> give
>> >> incorrect totals."
>> >>
>> >> But how can 239.5074 be a *correct* total? When did someone last pay
>> >> you
>> >> 0.0074 cents?
>> >>
>> >> "John" <bravo6.RemoveThis@gofree.indigo.ie> wrote in message
>> >> news:2iook.26695$j7.472556@news.indigo.ie...
>> >> >I have a table which stores transctions with a currency price field.
>> >> > I use a query to put together relevant info from several tables
>> >> > including
>> >> > the sum of the price grouped by invoice.
>> >> > and a calculated field for sales tax.
>> >> > My problem is that when I print the invoice report the subtotal +tax
>> >> > dont
>> >> > always equal Invoice total as displayed.
>> >> >
>> >> > One example here
>> >> > subtotal 239.5074 displays after rounding as 239.51
>> >> > Tax 50.2965 displays after rounding as 50.30
>> >> > Invoice total = 289.8039 which of course displays as 289.80
>> >> > However this is incorrect as the rounded figures of 239.51 + 50.30
>> >> > should
>> >> > equal 289.81.
>> >> > I cant round the figures in the query because this would give
>> >> > incorrect
>> >> > totals.
>> >> > This must be a common problem, what is the usual solution
>> >> >
>> >> > Any help greatly appreciated
>> >> > Regards John
>> >> >
>> >>
>> >>
>> >>
>> >
>>
>>
>>
>