rounding error

My database in Access 2003 has a form which calculates Quantity by the Price 
in the footer which is then multiplied by a Tax rate of 12.5% and the two 
added to get a Total . I get the following figures in the form Subtotal: 
96,738.67 Tax: 12,092.33 TOTAL: 108,831.01. The correct answer should be 
108,831.00. I have tried various formats (currency, standard, fixed with 
rounding Auto & 2) but am not able to correct for that small difference. Any 
solutions
0
Utf
1/13/2010 3:16:01 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
426 Views

Similar Articles

[PageSpeed] 24

"pkeegs" wrote:
> My database in Access 2003 has a form which
> calculates Quantity by the Price in the footer
> which is then multiplied by a Tax rate of 12.5%
> and the two added to get a Total . I get the
> following figures in the form Subtotal:  96,738.67
> Tax: 12,092.33 TOTAL: 108,831.01. The correct
> answer should be 108,831.00. 

If you are asking about calculations performed in Excel (this is an Excel 
newsgroup, not an Access newsgroup), the simple answer is:  all calculations 
that should result in dollars-and-cents should be rounded to 2 decimal places.

There are two ways to do that:  I prefer explicitly using ROUND in the 
formulas.  For example,

B1:  =ROUND(SUM(A1:A100),2)
B2:  =ROUND(B1*12.5%,2)
B3:  =ROUND(B1+B2,2)

For example, note that 96738.67*12.5% is actually 12092.33375 if you do the 
compuation manually or with a calculator.  If you carry the extra fractional 
digits through other calculations, eventually you will see noticable "errors" 
in expected results.

But in your example, note that 96738.67 + 12092.33375 would still apear to 
be 108831.00 when displayed to 2 decimal places.  So we can infer that 
96738.67 is not exactly that either; in fact, it is between 96738.67125 and 
just less than 96738.68125.

There are several ways that might happen; but very likely, it is because 
some other intermedicate computation results in more than 2 fractional 
digits, just as multiplying by 12.5% did.

PS:  The alternative to explicit use of ROUND is to use the option Precision 
As Displayed under Tools > Options > Calculation (in Excel 2003).  I 
deprecate the use of that feature because it can be very dangerous if you are 
not careful.  If you choose to experiment with PAD, be sure to make a backup 
copy of the Excel file before you set the option.  PAD will implicitly round 
to 2 decimal places only if you use a numeric format (Number, Accounting, 
Currency, etc) with 2 decimal places.  In particular, it has not impact on 
the results in cells formatted as General.


> I have tried various formats (currency, standard,
> fixed with rounding Auto & 2

Unless you use Precision As Displayed (see above), formats with 2 decimal 
places only change the appearance of values, not the actual value in the cell.


----- original message -----

"pkeegs" wrote:
> My database in Access 2003 has a form which calculates Quantity by the Price 
> in the footer which is then multiplied by a Tax rate of 12.5% and the two 
> added to get a Total . I get the following figures in the form Subtotal: 
> 96,738.67 Tax: 12,092.33 TOTAL: 108,831.01. The correct answer should be 
> 108,831.00. I have tried various formats (currency, standard, fixed with 
> rounding Auto & 2) but am not able to correct for that small difference. Any 
> solutions
0
Utf
1/13/2010 4:34:01 AM
Errata....

I wrote:
> So we can infer that 96738.67 is not exactly that either;
> in fact, it is between 96738.67125 and just less than 96738.68125.

It is between about 96738.6711111111 and about 96738.6749999999 if the 
subtotal plus tax displays as 108,831.01, where subtotal displays as 
96,738.67 and tax displayes as 12,092.33.  Tax (12.5% of subtotal) is 
actually between about 12092.3338888889 and about 12092.3343750000 
respectively.  Their sum is actually between about 108831.005000000 and 
about 108831.009375000.


> There are several ways that might happen; but very likely, it is because
> some other intermedicate computation results in more than 2 fractional
> digits, just as multiplying by 12.5% did.

Another possible source of the "error" in the subtotal (i.e unexpected 
fractional digits) is due to the way that Excel (and Access, presumably) 
store numbers and perform arithmetic.  It is caused binary floating point. 
Consequently, most numbers with fractional digits cannot be represented 
exactly.  Summing a large quantity of such numbers might introduce a 
significant "error".

(I was trying to avoid the topic.)


----- original message -----

"Joe User" <joeu2004> wrote in message 
news:32C2A69B-0303-4F02-BAFF-6D860111D264@microsoft.com...
> "pkeegs" wrote:
>> My database in Access 2003 has a form which
>> calculates Quantity by the Price in the footer
>> which is then multiplied by a Tax rate of 12.5%
>> and the two added to get a Total . I get the
>> following figures in the form Subtotal:  96,738.67
>> Tax: 12,092.33 TOTAL: 108,831.01. The correct
>> answer should be 108,831.00.
>
> If you are asking about calculations performed in Excel (this is an Excel
> newsgroup, not an Access newsgroup), the simple answer is:  all 
> calculations
> that should result in dollars-and-cents should be rounded to 2 decimal 
> places.
>
> There are two ways to do that:  I prefer explicitly using ROUND in the
> formulas.  For example,
>
> B1:  =ROUND(SUM(A1:A100),2)
> B2:  =ROUND(B1*12.5%,2)
> B3:  =ROUND(B1+B2,2)
>
> For example, note that 96738.67*12.5% is actually 12092.33375 if you do 
> the
> compuation manually or with a calculator.  If you carry the extra 
> fractional
> digits through other calculations, eventually you will see noticable 
> "errors"
> in expected results.
>
> But in your example, note that 96738.67 + 12092.33375 would still apear to
> be 108831.00 when displayed to 2 decimal places.  So we can infer that
> 96738.67 is not exactly that either; in fact, it is between 96738.67125 
> and
> just less than 96738.68125.
>
> There are several ways that might happen; but very likely, it is because
> some other intermedicate computation results in more than 2 fractional
> digits, just as multiplying by 12.5% did.
>
> PS:  The alternative to explicit use of ROUND is to use the option 
> Precision
> As Displayed under Tools > Options > Calculation (in Excel 2003).  I
> deprecate the use of that feature because it can be very dangerous if you 
> are
> not careful.  If you choose to experiment with PAD, be sure to make a 
> backup
> copy of the Excel file before you set the option.  PAD will implicitly 
> round
> to 2 decimal places only if you use a numeric format (Number, Accounting,
> Currency, etc) with 2 decimal places.  In particular, it has not impact on
> the results in cells formatted as General.
>
>
>> I have tried various formats (currency, standard,
>> fixed with rounding Auto & 2
>
> Unless you use Precision As Displayed (see above), formats with 2 decimal
> places only change the appearance of values, not the actual value in the 
> cell.
>
>
> ----- original message -----
>
> "pkeegs" wrote:
>> My database in Access 2003 has a form which calculates Quantity by the 
>> Price
>> in the footer which is then multiplied by a Tax rate of 12.5% and the two
>> added to get a Total . I get the following figures in the form Subtotal:
>> 96,738.67 Tax: 12,092.33 TOTAL: 108,831.01. The correct answer should be
>> 108,831.00. I have tried various formats (currency, standard, fixed with
>> rounding Auto & 2) but am not able to correct for that small difference. 
>> Any
>> solutions 

0
Joe
1/13/2010 7:53:59 AM
Reply:

Similar Artilces:

text box must be empty error
Has anyone come across that error that says the "text box must be empty" when doing a Create Text Block Link, and the text box is empty? Any solutions? -- Robert Pearson ParaMind Brainstorming Software http://www.paramind.net Creative Virtue Press/Telical Books/Regenerative Music http://www.rspearson.com You can only link to an empty text box. Copy the text in box two, paste it to the end of box one, create an empty text box, link box one to box three, delete box two. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com <...

rounding #9
is there a way to have a column round automatically instead of having to round onto another column? I don't want to add another column to just be able to round. -- denhar ------------------------------------------------------------------------ denhar's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24089 View this thread: http://www.excelforum.com/showthread.php?threadid=378356 If the column contains formulas, the formulas can incorporate the Round() function within themselves. For example, if the formula is: =A1*B1 it could be revised to: =ROUND(A1*...

error opening saved .pub website
When I try to open the website I saved as a .pub document yesterday, the following error message displays "Publisher has detected a prolem in the file you are trying to open. To protect your computer, Publisher will not open this file." Are there any suggestions for what I can do? I put so much work into it yesterday and really want to recover it. Thanks! Do you have Norton? How to use Office programs with the Norton AntiVirus Office plug-in http://support.microsoft.com/kb/329820/en-us Error message when you try to open a publication in Publisher: "Publisher cannot op...

Cell showing date and time
I have a cell set up as format dd/mm/yyyy hh:mm:ss and the contents are 01/01/1995 00:00:00 In the cell below I put 01/01/1995 01:00:00 then fill the cells for a few hundred. After about 200 cells the value shows as 09/01/1995 10:00:01 - in other words the increment being added for each cell is slightly greater than the desired one hour. I assume this is a lack of precision in the cell value. Is there a way of changing this? I am using Excel 2007. Grateful for advice. Enter the first date/time in A1 and in A2 enter: =A2+TIME(1,0,0) and copy down -- Gary'...

round() ?
Hello How can i round float to 2 digits after '.' ? Thanx Quick and Dirty: Multiply by 100, add 0.5 (to round up correctly). convert to long, then back to float, then divide by 100. This is limited to values that will fit within a long, and is not efficient. It is reliable. also: float val; val = val - fmod(val, .01); Or, the real thing: char *_fcvt( double value, int count, int *dec, int *sign ); Which is a char string, which you can then feed to atof() and get your rounded value back. Alas, beware errors in precision limits! Balboos user@domain.invalid wrote: >...

excel LOOKUP( ) error #3
the data matches up and for all other cells in the column, the correc value is returned. in the first row however, N/A is returne -- apple ----------------------------------------------------------------------- apples's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1529 View this thread: http://www.excelforum.com/showthread.php?threadid=26922 ...

Exchange Application Error
Event Type: Error Event Source: MSExchangeSA Event Category: General Event ID: 9153 Date: 5/24/2004 Time: 5:41:28 AM User: N/A Computer: DomainExchange Description: Microsoft Exchange System Attendant reported an error '0xc0072030' when setting DS notification. For more information, click http://www.microsoft.com/contentredirect.asp. Any insite would be appreciated? Regards, We need more information of your environment. Mixed? Exchange 5.5 and Exchange 2000? There are other events that come right before this that should give us more information also. These may be info...

memory could not be read error
Hi All, Whenever I tried to open MS Outlook, I get an error "memory could not be read". After a lot of frustration, I tried to Uninstall MS Office and Install it again without success. Can any body tell me the probable reason and the way out. I guess, this might be some AddIns problem automatically installed while connecting to Internet. Any help will be highly appreciated. Thank you all, Sandip ...

MS Visual Basic error message
Whenever I try to enter a formula in cell, I get this MS Visual Basic warning: " Can't find project or library." After I click "OK", I can enter the formula in the cell. How can I get rid of this message? Thanks, CMA ...

CRM 3.0 Invalid Association error when sending email
On a fresh CRM 3.0 installation I receive the error "Invalid Association. The request association is not valid" when we try to send an email. Anyone know about this? Thanks! Hi. I also have the "Invalid Association" error when sending email, did anyone find a resolution to this? Thanks in Advance. Dav "Troy" wrote: > On a fresh CRM 3.0 installation I receive the error "Invalid Association. The > request association is not valid" when we try to send an email. Anyone know > about this? Thanks! Reply to self - turned out to be the authe...

Net Worth Error
In Money 2007, I get an error when I try to look at my net worth. The error reads, "An error occurred, but Money is unable to retrieve and display details about what happend. (Resource = rcreport. Error = 2109.)" I ran the Standard File Repair but received a "No broken records in the original file." result. Any suggestions? Thank you. I had a similar problem with the monthly reports. Look at my post called "Troubleshooting method" that I wrote on 9/13/06, it might give you some ideas. Regards Bill Wood "T. J. Allard" <T. J. Allard@discu...

Install error
Our network guy reinstalled Windows 2000 on one of our workstations and left me to reinstall all the applications (which I've never done before). I finally found the cd for Microsoft Office 2002 that matched that particular machine (as he didn't label them) and followed the prompts to install. It said the install was successful, but Outlook wouldn't work. I put the cd back in and followed the prompts to just install Outlook. Still wouldn't work so I put the cd back and told it to reinstall. It said the reinstall was successful, but Outlook wouldn't work. So I put the...

Receivables Aging Process Error
During processing Cash Receipts, we had a power failure at our offices. I am getting the following error when Aging Receivables: "Documents exist in a recovered batch or in an interrupted transaction-level posting session that have not been posted. If you continue, only the documents that have been posted will be included". Does anyone have an idea how we can troubleshoot this problem? Maria, Did you look in Batch Recovery? Is there anything in there? Have you tried running check links? -- Victoria Yudin GP MVP "Maria" <betty@discussions.microsoft.com> wr...

Error 1911
Dear all, I got error message "Error 1911. Setup cannot register type library for file c:\program files\microsoft office\office10\msacc.olb" when install Access XP. After installed, I can't view the module. Please tell me how to solve. Thanks. Chlaris ...

Error in Subtotal Wizard (Grouping and Outlining)
I'm having trouble with Excel 2003 and the Subtotals Wizard. When I perform a three-level grouping on the data listed below, it appears that some groups are missing and outline levels 3, 4, and 5 are messed up. Given the following list of data: Region columnb Office Employee columne Issue NW SEA 123 Salary NW PDX 234 Benefits NW PDX 244 Salary NW PDX 212 Salary NW BOI 232 Benefits SW LAX 673 Salary SW LAX 34 Benefits SW LAX 654 Benefits SW PHX 556 Salary SW DEN 322 Benefits SW DEN 65 Salary ...

Error Message on FormMailer
I have several form mailer pages set-up on my website in each page after the form is completed and submit is clicked, the results are sent to the correct address, but the screen dispalyes an error message. Below is that message Error Message User: please report details to this sirte's webmaster Webmaster: please see the server's application event log for more details This same form mailer set-up has worked on other sites that I've developed without the error message. Can anyone help point me to some sort of resolution for this problem. I'm currently having ...

Rounding question
I'm using Excel 2007 but 'save as' 97-2003 as colleagues have different versions. All number formats are set to 2 decimal places. I'm finding that percentage calculations are rounding up to 2 decimal places but when the result in currency is subtracted from another figure the sum is rounded down. This gives results such as 6 - 3 = 4. I'm sure there must be a simple answer but I can't find it. Hoping someone here knows, as checking all simple calcuations is getting ridiculous ! Carrie "Carrie" <pantscarolyn.smith2@ntlworld.com> w...

Error 130
Hope someone can point me in the right direction. OSX 10.63, MacOffice 2008 12.2.4 When I try to connect to the following news server ( that doesn't require a user name or password ) I get the following error when attempting to download the List of Newsgroups: "Too many connections in your class - Could not receive the Newsgroup List - ERROR 130 " Any pointers much appreciated. Using Msnews servers works fine. -- Welsh Gas Remove usual to reply direct. paulatwoodsforddotcodotuk On 4/27/10 10:49 PM, in article 59bb7979.-1@webcrossing.JaKIaxP2ac0, "We...

multiple "reply" buttons & illegal operation error
using Outlook 2000 for MS Excange server config. when i open received mail, there are 2 reply buttons on the task bar. When I reply to email and send, "illegal operation" error comes up and Outlook shuts down. tried to uninstall and reinstall...but prob still there. tried to repair outlook...but prob still there. any ideas ...

selected value error
I have a form view that when it goes to Edit I am running into an error on any drop list. The following is a sample from one of the drop list. 'ddlEDMSCategoryID' has a SelectedValue which is invalid because it does not exist in the list of items. The drop list is filled using a sqldatasource. It is below; <asp:SqlDataSource ID="EDMSCategoryEditSqlDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:ProteusConnectionString %>" SelectCommand="SELECT EDMSCategoryID, EDMSCategory ...

Relaying denied error
Occasionally my users have outbound messages bound back to them with the following error: The following recipient(s) could not be reached: email@domain.com on 11/5/2004 3:01 PM You do not have permission to send to this recipient. For assistance, contact your system administrator. <server1.corp.mydomain.com #5.7.1 smtp;550 5.7.1 <email@domain.com>... Relaying denied>. This doesn't happen all the time, and when it does it only happens to users on a couple of my servers. The users are all logged into the domain and using Outlook. Any ideas why this is happening? I...

Problem with rounding currency values
Hi, I am using the Sales Global Procedure SOP_Calculate_Trade_Discount_Split. This procedure returns the line trade discount. The line trade discount returned is rounded off. eg: if value is 6.76 then it returns 7.00. Is there some place where i can specify to turn off the rounding off? The output type is Currency. Any help is greatly appreciated. Thanks & Regards, Sup ...

Receiving error -17709 when trying to use Global Address List Entourage '04
Hello, I have a new Macintosh which I am trying to configure Entourage 2004 to a Small business Server 2003 SP1 running Exchange SP1. Entourage on the Mac is fully patched. The server is running IMAP4 and File services for the Macintosh. When I try to address a new email in the Entourage client and I navigate the drop down list to Default Global Address List (Exchange), and try to search on an entry in the GAL, I receive an error: Unknown error (-17709), LDAP server error, Account: Exchange. My current settings under the advanced tab of the Exchange account settings are: ldap server: FQDN ...

error #16
Everytime I click on outlook 2003 to start the program I get this message and when I click yes, then it works fine. "Some Functionalbility of outlook connector has been disabled choosing yes will automaticly enable this functionalbility. It is just annoying. thanks gino which connector are you using? -- <stingraymmcm7@gmail.com> wrote in message news:1135709208.782972.146940@g14g2000cwa.googlegroups.com... > Everytime I click on outlook 2003 to start the program I get this > message and when I click yes, then it works fine. > > "Some Functionalbility of out...

Error number 0x80000003
Hello, Have XP Home Edition,Service Pack 3..I can only use safe mode to a limited degree..The Internet will freexe in short order...Getting to desktop a box will say MSASCui.exe application error 0x80000003..ISP told me I have spyware and malware..Any fix here short of using the Restore disk by wiping out the hard drive...Thanks,Nick NICK MEALE wrote: > Hello, > Have XP Home Edition,Service Pack 3..I can only use safe mode to a limited > degree..The Internet will freexe in short order...Getting to desktop a box > will say MSASCui.exe application error...