Total is off by a couple of cents
in [Reports]
|
From: FCP on 25 May 2010 16:50 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
From: John Spencer on 26 May 2010 09:01 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
From: FCP on 1 Jun 2010 12:13 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 > . >
From: John Spencer on 1 Jun 2010 14:32 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 >> . >>
From: FCP on 4 Jun 2010 12:02 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 > >> . > >> > . >
|
Pages: 1 Prev: simple textbox data source syntax question Next: Label Report |