Total is off by a couple of cents

  • Thread starter Thread starter FCP
  • Start date Start date
F

FCP

I have a report that feeds of a query. The query has the following fields
matsales: [order details]!unitprice*[order details]!quantity
scharge: [order details]!surcharge*[order details]!quantity
Taxes: ([matsales]+[scharge])*0.0875

0.0875 is that tax values

On the report the sum in the footer =Sum(matsales) and = Sum(scharge) are
correct however, =Sum(Taxes) is off. For example the total should be $161.88
after the taxes has been rounded off in two decimal places but its giving me
$161.85 the actual total if the taxes were not rounded off.

The matsales and scharge total are correct and they are rounded as well. I
think the problem im having is similar in excel if its not "Set as precision
as displayed."
I have tried every possible format in both the sum field in the report and
query, I even converted the numbers to CSng or Cdbl. Tried rounding off the
quesry as well, and none worked.

Is there a way on access to sum the values as displayed if not is there a
solution for the problem I described above.

Thanks
 
The problem is that Taxes needs to be rounded off during the calculation.

Taxes: Round(([matsales]+[scharge])*0.0875,2)

AND when you sum
SUM(Round(([matsales]+[scharge])*0.0875,2))

Otherwise small discrepancies are going to creep into your calculation of the
Total Tax. You may see 0.39 for the calculation [matsales]+[scharge])*0.0875
but the actual result may be 0.39375. When you total a few like that before
rounding you end up with extra pennies. On the other hand .3965 will display
as .40 and total a few of those and you might loose a penny or two.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
John,

I appreciate the respond and it works however, I have one more question if
we have a number/currency like 10.325 and we round it to two decimal place as
you suggested would this number be 10.32 or 10.33. Access is giving me 10.32
I thought in math anything 5 or above should be rounded to the next number,
does Access think the same way. I do see 10.33 if I round the field in the
report into two decimal place under the field properties by not using the
formula of Round([exp],2).

Thanks again

John Spencer said:
The problem is that Taxes needs to be rounded off during the calculation.

Taxes: Round(([matsales]+[scharge])*0.0875,2)

AND when you sum
SUM(Round(([matsales]+[scharge])*0.0875,2))

Otherwise small discrepancies are going to creep into your calculation of the
Total Tax. You may see 0.39 for the calculation [matsales]+[scharge])*0.0875
but the actual result may be 0.39375. When you total a few like that before
rounding you end up with extra pennies. On the other hand .3965 will display
as .40 and total a few of those and you might loose a penny or two.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I have a report that feeds of a query. The query has the following fields
matsales: [order details]!unitprice*[order details]!quantity
scharge: [order details]!surcharge*[order details]!quantity
Taxes: ([matsales]+[scharge])*0.0875

0.0875 is that tax values

On the report the sum in the footer =Sum(matsales) and = Sum(scharge) are
correct however, =Sum(Taxes) is off. For example the total should be $161.88
after the taxes has been rounded off in two decimal places but its giving me
$161.85 the actual total if the taxes were not rounded off.

The matsales and scharge total are correct and they are rounded as well. I
think the problem im having is similar in excel if its not "Set as precision
as displayed."
I have tried every possible format in both the sum field in the report and
query, I even converted the numbers to CSng or Cdbl. Tried rounding off the
quesry as well, and none worked.

Is there a way on access to sum the values as displayed if not is there a
solution for the problem I described above.

Thanks
Click to expand...
.
Click to expand...
 
Round uses Banker's Rounding. That means if the last digit is 5 the rounding
takes place toward the nearest even number so .235 rounds to .24 and .245
rounds to .24. The theory is that this will be closer to the correct amount
if you round a lot of numbers.

There are other rounding algorithms that will round the way you wish.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
John,

I appreciate the respond and it works however, I have one more question if
we have a number/currency like 10.325 and we round it to two decimal place as
you suggested would this number be 10.32 or 10.33. Access is giving me 10.32
I thought in math anything 5 or above should be rounded to the next number,
does Access think the same way. I do see 10.33 if I round the field in the
report into two decimal place under the field properties by not using the
formula of Round([exp],2).

Thanks again

John Spencer said:
The problem is that Taxes needs to be rounded off during the calculation.

Taxes: Round(([matsales]+[scharge])*0.0875,2)

AND when you sum
SUM(Round(([matsales]+[scharge])*0.0875,2))

Otherwise small discrepancies are going to creep into your calculation of the
Total Tax. You may see 0.39 for the calculation [matsales]+[scharge])*0.0875
but the actual result may be 0.39375. When you total a few like that before
rounding you end up with extra pennies. On the other hand .3965 will display
as .40 and total a few of those and you might loose a penny or two.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I have a report that feeds of a query. The query has the following fields
matsales: [order details]!unitprice*[order details]!quantity
scharge: [order details]!surcharge*[order details]!quantity
Taxes: ([matsales]+[scharge])*0.0875

0.0875 is that tax values

On the report the sum in the footer =Sum(matsales) and = Sum(scharge) are
correct however, =Sum(Taxes) is off. For example the total should be $161.88
after the taxes has been rounded off in two decimal places but its giving me
$161.85 the actual total if the taxes were not rounded off.

The matsales and scharge total are correct and they are rounded as well. I
think the problem im having is similar in excel if its not "Set as precision
as displayed."
I have tried every possible format in both the sum field in the report and
query, I even converted the numbers to CSng or Cdbl. Tried rounding off the
quesry as well, and none worked.

Is there a way on access to sum the values as displayed if not is there a
solution for the problem I described above.

Thanks
Click to expand...
.
Click to expand...
Click to expand...
 
Hi John,

Again thank you for responding, the other rounding algorithm you mentioned
where can I find those formulas? Also would the sum in the report be
accurate or precise if I round the values using those algorithms? For
example

The Report values using the Round([xpr],2)
3.74
10.32 (this can be rounded to 10.33, however the total will still
be 17.80 in the Sum Report, which was my original problem)
3.74
Total: 17.80

We would prefer that 10.325 becomes 10.33 and the total Sum in the report
equals to 17.81. Can this be possible?

John Spencer said:
Round uses Banker's Rounding. That means if the last digit is 5 the rounding
takes place toward the nearest even number so .235 rounds to .24 and .245
rounds to .24. The theory is that this will be closer to the correct amount
if you round a lot of numbers.

There are other rounding algorithms that will round the way you wish.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
John,

I appreciate the respond and it works however, I have one more question if
we have a number/currency like 10.325 and we round it to two decimal place as
you suggested would this number be 10.32 or 10.33. Access is giving me 10.32
I thought in math anything 5 or above should be rounded to the next number,
does Access think the same way. I do see 10.33 if I round the field in the
report into two decimal place under the field properties by not using the
formula of Round([exp],2).

Thanks again

John Spencer said:
The problem is that Taxes needs to be rounded off during the calculation.

Taxes: Round(([matsales]+[scharge])*0.0875,2)

AND when you sum
SUM(Round(([matsales]+[scharge])*0.0875,2))

Otherwise small discrepancies are going to creep into your calculation of the
Total Tax. You may see 0.39 for the calculation [matsales]+[scharge])*0.0875
but the actual result may be 0.39375. When you total a few like that before
rounding you end up with extra pennies. On the other hand .3965 will display
as .40 and total a few of those and you might loose a penny or two.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

FCP wrote:
I have a report that feeds of a query. The query has the following fields
matsales: [order details]!unitprice*[order details]!quantity
scharge: [order details]!surcharge*[order details]!quantity
Taxes: ([matsales]+[scharge])*0.0875

0.0875 is that tax values

On the report the sum in the footer =Sum(matsales) and = Sum(scharge) are
correct however, =Sum(Taxes) is off. For example the total should be $161.88
after the taxes has been rounded off in two decimal places but its giving me
$161.85 the actual total if the taxes were not rounded off.

The matsales and scharge total are correct and they are rounded as well. I
think the problem im having is similar in excel if its not "Set as precision
as displayed."
I have tried every possible format in both the sum field in the report and
query, I even converted the numbers to CSng or Cdbl. Tried rounding off the
quesry as well, and none worked.

Is there a way on access to sum the values as displayed if not is there a
solution for the problem I described above.

Thanks
.
Click to expand...
Click to expand...
.
Click to expand...
 
Back
Top