A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Total is off by a couple of cents



 
 
Thread Tools Display Modes
  #1  
Old May 25th, 2010, 09:50 PM posted to microsoft.public.access.reports
FCP
external usenet poster
 
Posts: 3
Default Total is off by a couple of cents

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
  #2  
Old May 26th, 2010, 02:01 PM posted to microsoft.public.access.reports
John Spencer
external usenet poster
 
Posts: 7,815
Default Total is off by a couple of cents

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

  #3  
Old June 1st, 2010, 05:13 PM posted to microsoft.public.access.reports
FCP
external usenet poster
 
Posts: 3
Default Total is off by a couple of cents

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" wrote:

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

.

  #4  
Old June 1st, 2010, 07:32 PM posted to microsoft.public.access.reports
John Spencer
external usenet poster
 
Posts: 7,815
Default Total is off by a couple of cents

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

FCP wrote:
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" wrote:

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

.

  #5  
Old June 4th, 2010, 05:02 PM posted to microsoft.public.access.reports
FCP
external usenet poster
 
Posts: 3
Default Total is off by a couple of cents

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" wrote:

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

FCP wrote:
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" wrote:

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
.

.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:53 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.