How Stop Rounding Errors?

On financial worksheets I am getting fractional rounding errors.  How can 
you format a cell so that if =(A1*B1) is the formula the result in the cell 
will be a value such as $50.45 and not $50.457 even though it is formatted 
for 2 decimal places.



0
albert3569 (90)
1/29/2005 2:03:39 PM
excel 39879 articles. 2 followers. Follow

3 Replies
611 Views

Similar Articles

[PageSpeed] 41

  A                     B                                B
50.457   =ROUND(A1,2)                 50.46  (Note the 6)
50.457   =ROUNDDOWN(A2,2)    50.45
50.457   =FLOOR(A3,0.01)             50.45
50.457   =INT(A4*100)/100             50.45

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
              Sys Spec - Win XP Pro /  XL 97/00/02/03

----------------------------------------------------------------------------
                  It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Al Franz" <albert@nospam.netmation.com> wrote in message
news:OqyXRugBFHA.3664@TK2MSFTNGP14.phx.gbl...
> On financial worksheets I am getting fractional rounding errors.  How can
> you format a cell so that if =(A1*B1) is the formula the result in the
cell
> will be a value such as $50.45 and not $50.457 even though it is formatted
> for 2 decimal places.
>
>
>


0
ken.wright (2489)
1/29/2005 2:25:03 PM
"Al Franz" <albert@nospam.netmation.com> wrote...
>On financial worksheets I am getting fractional rounding errors.  How can
>you format a cell so that if =(A1*B1) is the formula the result in the cell
>will be a value such as $50.45 and not $50.457 even though it is formatted
>for 2 decimal places.

Tools > Options, Calculation tab, check 'Precision as Displayed'. This will
make *ALL* calculations subject to fixed point arithmetic, so you'd need to
check that the implicit rounding/truncation does what you want. But if it
does *AND* you never have to deal with reconciling individual terms in NPV
calculations (compound interest calculations are a royal pain with fixed
point), then this would be the best approach.


0
hrlngrv (1990)
1/29/2005 9:49:40 PM
Try the formula =ROUND(A1*B1,2)
Cheers
Rob

"Harlan Grove" <hrlngrv@aol.com> wrote in message
news:%23OCd7xkBFHA.4028@TK2MSFTNGP15.phx.gbl...
> "Al Franz" <albert@nospam.netmation.com> wrote...
> >On financial worksheets I am getting fractional rounding errors.  How can
> >you format a cell so that if =(A1*B1) is the formula the result in the
cell
> >will be a value such as $50.45 and not $50.457 even though it is
formatted
> >for 2 decimal places.
>
> Tools > Options, Calculation tab, check 'Precision as Displayed'. This
will
> make *ALL* calculations subject to fixed point arithmetic, so you'd need
to
> check that the implicit rounding/truncation does what you want. But if it
> does *AND* you never have to deal with reconciling individual terms in NPV
> calculations (compound interest calculations are a royal pain with fixed
> point), then this would be the best approach.
>
>


0
mbamc (6)
1/30/2005 3:32:06 AM
Reply:

Similar Artilces:

Error message........
After a re-organisation when ever I save my excel file it throws up the message that autopublish cannot find a web address and gives some addresses of a defunct location. I know why it is happening but do not know how to stop the error messages. ...

Visual C++ Run Time error when opening Outlook 2000
I get the following error message when opening Outlook 2000 on a Win98 machine: Runtime Error! Program: C:\Programs\Microsoft Office\Office\Outlook.exe This program has requested the runtime to terminate it in an unusual way. I have uninstalled/reinstalled Office2000. I have downloaded newest updates to Office 2000. I have run a virus check on the PC. I turned off the anti-virus software. I have run the repair utility - all without success. I suspect a problem in the registry, but don't know what to look for. Has anyone come across this problem? Can someone help? I would be eve...

Error installing Extender.
Hi, When I try to install Extender on a machine, I receive the following error: "The required configuration information necessary for installing could not be found on this machine" I have GP 8.0 sp3, Latin American version. Any Idea?? I don´t know what need to check... -- Evelin. Ana - just a guess, but are you logged in as Administrator? Perhaps it's a issue with user rights to access/edit the registry. Brenner @ (www.KlenzmanConsulting.com) "Ana Evelin Rosa" wrote: > Hi, > > When I try to install Extender on a machine, I receive the following...

Function is not availalbe in expressions in query expression error message (0/1)
I recently received this error message when opening the source DB in Access 2007. I have attached two jpg pics. Does this error message give enough details for someone to point me in the right direction for a fix? The last time I developed and worked in this DB everything worked fine. I get this error when clicking on a control that runs a query macro against a backend DB consisting of about 15 tables. I should mention the error comes into play when trying to run the frontend on a client W/S. I don't see the error while on my W/S which is my developer W/S and has Acce...

Convert Lead to Account, Contact, or Opportunity Error CRM 4.0 #2
Hi, in CRM 4.0 when I try to convert a Lead to an account, lead or opportunity it tells me there is an error and to try again. It gives me no details on the error. But when I try to disqualify a lead it allows me to do that. So what would cause me not to be able to convert a lead? Thanks. -- Christina Hi Christina, Do you have any Plug-in associate with Account or Opportunity? If not, I believed a platform trace will able to tell you what is wrong. Darren Liu, Microsoft CRM MVP Crowe Horwath http://www.crowecrm.com On Dec 8, 10:45=A0am, Christina <Christ...@discussions.microsof...

Rounding off Time Clock hours to 1/4 hour
How can I modify the Time clock or cashier report to round off Time Clock hours to the nearest 1/4 hour for payroll purposes. I can in the Crystal Report but not sure in the active report. ...

Error Code 800736B2
I have a relatively new laptop with Vista, and I cannot install any updates. I receive an error code 800736B2. Lots on the web about removing Kaspersky Internet Security, but I don't seem to have this software. What can I do to update my laptop? 800736B2 ERROR_SXS_INVALID_ACTCTXDATA_FORMAT The application binding data format is invalid. Suggest running the System File Checker: <http://support.microsoft.com/kb/936212> no joy.......then/or Start a free Windows Update support incident request: <https://support.microsoft.com/oas/default.aspx?gprid=6527&...

DSUM error# Syntax problem?
I am trying to use DSUM to create a running total via Method 1 in MS TID 290136. I am getting an error in my DSUM statement. Very simple query: SELECT SalesForGraph.Week AS CWeek, SalesForGraph.[2006Profit], DSum("2006Profit","SalesForGraph","[WEEK] <= " & [Cweek]) AS RunTot FROM SalesForGraph ORDER BY SalesForGraph.Week; The string for Criteria resolves to: [week] <=1 [week] <=2 etc. What am I doing wrong? Phil Changed it to SELECT SalesForGraph.Week AS CWeek, SalesForGraph.[2006Profit], DSum("2006Profit","SalesForGraph&quo...

Error opening Outlook 2007
I am getting a "Either there is no default mail client or the current mail client cannot fulfill the messaging request. Please run Microsoft Office Outlook and set it as the default mail client." I have it as the default email client. Also under the mail icon in control panel I have another profile that is {d9734f9-8cfb-411d-bc59-833e334fcb5e}. I can delete all the profiles and create just one profile that other profile comes back again. I have tried all the solutions that I have found in the net. I am using Windows Vista and Outlook 2007. Thanks Ed is this a home or...

Error message when replying to email
I connect remotely via dial-up VPN connection and able to receive emails, so the server connection is OK. I was able to send emails to both an inside and outside email, however when I reply to certain emails, I receive an error message "Network Problems are preventing connection to the microsoft exchange server". I am running W2k and Outlook 2000 and the server is Exchange 5.5. Any help would be appreciated. ...

Stopping a macro based Setwarning diag box
I have created a Macro that includes a spreadsheet transfer and append. My SetWarnings is Yes. However, I noticed that when I get the dialog box indicating what I am about to do and ask if I am sure I want to run this query, I select no but the macro continues anyway. How can I get the Macro to stop running? Answered in your post 30 minutes later. Hi AB1, Sounds to me like it's time to make the move away from macros to VBA code. This is very easy to do with a little VBA code. The same thing applies for your follow-on question "SetWarning in macro". Perhaps ...

Stop scrolling
I have a worksheet where I want to limit how far a user can scroll down. For example, I want people to be able to scroll to row 27, but have the view stop there and not allow them to be able to scroll down passed that line. Even if I hide the rows under row 27, you can still keep scrolling down. I want to stop that from happening. Is there a way? Thanks. have a look in vba help index for scrollarea -- Don Guillett SalesAid Software donaldb@281.com "gottahavit" <gottahavit@discussions.microsoft.com> wrote in message news:6FB03B59-7681-4271-B37A-FDF1DF35EADF@microsoft.com.....

link error #5
Hi, After I change the project setting to 'multithreaded DLL' I got the link errors: uafxcwd.lib(appcore.obj) : error LNK2001: unresolved external symbol ___wargv uafxcwd.lib(appcore.obj) : error LNK2001: unresolved external symbol ___argc Is there any lib missing? thanks, chi ...

Email Errors> no download of messages>error messages. #2
Email Errors> no download of messages>error messages. My email has been working just fine until today. I can't think of anything I've done to create a problem. I'm using WindowsXP-pro-sp2; Outlook 2003, and Norton AntiVirus 2005. The same problem just started in Outlook Express as well. Was on the phone with Charter for over two hours removing email accounts and then adding them from scratch. I've been trying for hours and hours fix this to no avail--the error message keeps coming up and I'm expecting some very important email. The error messages will say someth...

Cannot access office out, getting POP3 Reporting Error 0X80042108
Hi there, i understand MS made some changes and now i am locked out from my MS Office Outlook. I get a POP3 Reporting Error # 0X80042108. The server does not recognize me. Is there any instructions on how to set or re-set this? Is there any REAL PERSON i can talk to for FREE Tech Support??? Thanks, Ray @ manauto@msn.com The changes were to how Outlook pulls Hotmail. Verify your settings against this: http://support.microsoft.com/kb/813514/en-us "Ray" <Ray@discussions.microsoft.com> wrote in message news:23A6252E-7B5F-454D-BB5A-5C9AB2B24DCD@microsoft.com... > Hi there...

Outlook Error #42
I keep getting the following error when I startup the application. "Microsoft Outlook; Error in registry for Exchange Extensions;?"The sintax format of the registry entry is incorrect. Check the registry settings and compare the registry for this extension to other extensions in the registry. Then the ok button displays. What could be causing this, and what's the solution. Thanks in advance Denise You might try searching for and deleting the extend.dat file while Outlook is closed, and see if that solves the problem. The file may be in a hidden folder, so make sure you ...

Error ID 1016 (Network Compromise)
I've been going through my event logs, and starting two days ago, I've seen domain users showing up in Error ID 1016 with the following message: domain\username has logged on to domain\username's mailbox and is not the Primary WinNT account. It would suggest that someone is reading someone else's mail. However, it's rampant- I'm receiving the error from multiple users accessing multiple mailboxes. It does not coincide with a backup and I've never seen the message prior to two days ago? What on earth is this? Additionally, I had a strong password (just changed it)...

Error Opening Reports Node on New Installation.
We had a pretty uneventful CRM installation, the product installed on the first attempt with no errors, and all aspects of it appear to operate correctly with the exception of Reports. When opening the reports node in CRM, we were getting the ever so useful "An Error has occurred, please contact your system administrator". I enabled verbose error messages in the web.config file and captured the followng error message. ========= Microsoft CRM Unhandled Error Details: Server Error in '/' Application. --------------------------------------------------------------------...

Outlook 2000 error #17
I cannot seem to keep Outlook open on our server. Dr. Watson comes up and says: An application error has occurred and an application error log is being generated. OUTLOOK.EXE Exception: access violation (0xc0000005) Address: 0x3f99a142 What in the world is that and what can I do about it? Thank you! Hello, The problem you are experiencing is most likely caused by some add-ins in Outlook. Try the following troubleshooting steps to narrow down the problem: Note: Since some anti-virus programs will cause unexpected application conflicts, please temporarily disable any anti-virus programs o...

Run-time error 3426
Hey everyone, I have an error thats popping after a recent update. I have a form, that I've added a Jump To field. The field is called txtGoTo. Its a simple Text Box in my form header, the user enters a six digit class number and the form jumps directly to that class. Everything works fine and it takes my user to the correct record. However, when the user then go into the record and edits any field in the record, and then returns to teh Jump To field, I get an Run-time error 3426. It says that This action was cancelled by an associated object. If the suer makes no change...

Round Function
Insert round function for all the cells in an Excel Worksheet? You could use a help sheet, I doubt you want to use round in all 16777216 cells assume you have values in A1:H500 in Sheet1, insert a new sheet and select A1:H500, with A1 as the active cell type =Sheet1A1 press ctrl + enter, now you can copy and paste special as values over the old values in Sheet1 or paste as values in place and if you want you can delete Sheet1 and rename the help sheet to Sheet1 Regards, Peo Sjoblom "Pedro Serra" wrote: > Insert round function for all the cells in an Excel Worksheet? On...

Error posting cash receipt
We are receiving the follwoing error in one database only; Batch 20090908_LW failed to complete posting. Use the Batch Recovery winodow to complete the posting process. More Info button says; A get/change operation on table 'CM_Deposit_Work' failed. A record was already locked. We are on GP9.0 SP1 "TMack" wrote: > We are receiving the follwoing error in one database only; > Batch 20090908_LW failed to complete posting. Use the Batch Recovery winodow > to complete the posting process. > > More Info button says; > A get/change operation on table 'C...

How to stop pivot table crashing in Excel 2007
In a large spreadsheet with several tabs I’m having a problem with excel crashing while making a pivot table after a specific series of events happen. On the main data tab I have both several columns populated by data from an Access query and several columns populated by assorted formulas (date 1st of month & vlookup). If I do this excel crashes: 1) Start a pivot table & chart from main data tab by clicking the icon from the toolbar 2) Put the chart on a separate tab from the pivot table 3) Try to drag & drop any field that is populated by a formula I can drag around other f...

SVC00106 table errors with vendor combiner
Does anyone know what table SVC00106 is for and how it is populated? We are on Dynamics 10 and use RMA and RTV, but not service. When I try to combine vendors using the professional services tool it gives me a duplicate key error referencing this table. It appears to be the existence of the line it doesn't like, not the information the line contains. This table contains vendors that were added through sometime in 2006, nothing more recent. If I combine two vendors and at least one of them was added after 2006 I received no errors. Can this table be deleted? Since it hasn'...

rules in error #16
I am using outlook on two different machines/locations with the data .pst file on a mobile drive. When connecting this mobile drive and opening outlook for the first time at the machine were I arrive, a 'Rules in error' pop up window shows up. The pop-up window lists all rules for which new messages are arriving. When opening 'rules and alerts' those rules for which messages have arrived are unchecked. I have to recheck the rules and click apply, click ok - in order for all registered rules to work/apply. Is there a way to avoid this - so that even when switching bet...