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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|