Summation: Difference in Decimal

I am computing this simple computation but when i am on the summation i got 
to have a difference in the decimal places. i compare my computation with a 
calculator and excel..How can i solve this simple problem. the situation is 
this.

I formated the cell into currency and a two decimal places only.

Can you help me figure this problem?

Thank you

		
		

0
Mels (1)
8/25/2009 1:22:01 AM
excel.newusers 15348 articles. 2 followers. Follow

2 Replies
414 Views

Similar Articles

[PageSpeed] 50

"Mels" <Mels@discussions.microsoft.com> wrote:
> I am computing this simple computation but when i am on the
> summation i got to have a difference in the decimal places.
> [....]
> I formated the cell into currency and a two decimal places only.
> Can you help me figure this problem?

These problems are quite common.  They arise from the the internal 
representation and arithmetic that Excel (and most applications) uses on 
binary computers (viz. floating point arithmetic).  Consequently, most 
decimal fractions cannot be represented exactly, and small numerical 
aberrations arise as a consequence.

Formatting only affects the appearance of values.  To ensure that the value 
itself is "accurate" to 2 decimal places, use ROUND(expression,2).  For 
example,

=IF(10.1 - 10 = 0.1, TRUE)

returns FALSE(!).  But:

=IF(ROUND(10.1 - 10, 2) = 0.1, TRUE)

returns TRUE as expected.

Similarly, if you have a formula like:

=SUM(A1:A10)

you could write:

=ROUND(SUM(A1:A10), 2)

Using ROUND() prolifically might seem tedious.  Alternatively, you could set 
the calculation option "Precision as displayed" (Tools > Options > 
Calculation).  But I do not recommend it for several reasons.


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

"Mels" <Mels@discussions.microsoft.com> wrote in message 
news:14A6E226-8EDA-4CAA-83BA-567E542E0C35@microsoft.com...
>I am computing this simple computation but when i am on the summation i got
> to have a difference in the decimal places. i compare my computation with 
> a
> calculator and excel..How can i solve this simple problem. the situation 
> is
> this.
>
> I formated the cell into currency and a two decimal places only.
>
> Can you help me figure this problem?
>
> Thank you 

0
joeu2004 (766)
8/25/2009 1:59:38 AM
You have given us little to go on.

It is important to know that formatting a cell changes what is displayed but 
not what is stored
Suppose we are computing after-tax prices
13.45	15.06          (formula =A1*(1+12%) copied wont the column)
14.86	16.64
15.67	17.55
	49.26           (fromula =SUM(B1:B3)
Looks like the answer should be 49.25 so we are out by 1 cent/penny
But the actual stored values are
13.45	15.0640
14.86	16.6432
15.67	17.5504
	49.2576

Solution: use =ROUND(A1*(1+12%),2) to round the stored values before 
addition
Or use =SUM(ROUND(B1:B3,2)) to get 49.25
This is an array formula that need to be committed with CTRL+SHIFT+ENTER
There is another way: to use the option "use values as displayed" but it has 
draw backs

best wishes
-- 
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Mels" <Mels@discussions.microsoft.com> wrote in message 
news:14A6E226-8EDA-4CAA-83BA-567E542E0C35@microsoft.com...
> I am computing this simple computation but when i am on the summation i 
> got
> to have a difference in the decimal places. i compare my computation with 
> a
> calculator and excel..How can i solve this simple problem. the situation 
> is
> this.
>
> I formated the cell into currency and a two decimal places only.
>
> Can you help me figure this problem?
>
> Thank you
>
>
>
> 

0
bliengme (657)
8/25/2009 2:11:56 AM
Reply:

Similar Artilces:

Can't open different version
I am having problems opening a different version of a publisher doc. I have Office 2000 and the doc. I'm trying to open is from (I believe) Publisher 2. Can anyone help? Details, Dougie, details. "Doug" <anonymous@discussions.microsoft.com> wrote in message news:019a01c3c2a4$e7b1a530$a501280a@phx.gbl... > I am having problems opening a different version of a > publisher doc. I have Office 2000 and the doc. I'm trying > to open is from (I believe) Publisher 2. > Can anyone help? >-----Original Message----- >Details, Dougie, details. > > ...

Summation
Hi, I have a sheets, in which Col A has supplier names, Col B has part supplied by the supplier and Col C has qty supplied. Supplier Part qty AAAAA XXXX 2222 AAAAA 1111 232 AAAAA wwas 334 BBBBB ****** 345 BBBBB ++++ 675 BBBBB #### 7876 Now i want to calculate the total qty for one supplier and then in another col get the ratio of a qty for part by the total number of qty for that supplier. With the above example: it should calculate, first the total# of qty for Supp: AAAAA and then...

Pulling data daily from a differently named report everyday
Hello, I have a spreadsheet where I use formulas to pull data from multiple reports on a shared server everyday. What I do is to save those reports under a certain name everyday since the formulas have to have the static report name in them to pull data. (For example, A1 may pull data from c:\reportfolder\reportA, B1 may pull from c: \reportfolder2\reportB, etc). In these report folders our IT group runs a new report in it everyday but of course they change the name of the report every day (to reflect the date) . . so in the reportfolder there will be "reportA-3-12-2009", and "...

Different margins on different pages
I am setting up a template for letters at work. The first page of our letterhead has one ste of margins and all subsequent pages will have a different sent of margins. I know that section breaks can be used, but if staff copy and paste text from existing letters into this new template, the section break moves. Is there any way of locking the section break to the page? so the pasted text goes over the top of it? Hi Lilly80, You could setup your document with a 'different first page' layout. That allows the first page to have a different set of margins to the rest o...

Difference between dates
I have an inventory system database. invoices are ordered and have a date associated with them (date1). When the invoices come in, they are scanned in and another date is associated with them (date2) THe dates are assoicated by an invoice number. First I would like to run a query on the difference of the dates that displays the invoice number and the difference between date1 and date2 if the difference is more than x Secondly, I would like to run a query that callaculates the difference of the dates and how many occured for the year 192 5 days 102 4 days 148 3 days 300 2 days etc. T...

Different Results for the Same Macro
Hello: At the end of this posting, I have VBA code for a macro that I created. This macro was created in Excel 2007 macro. What's puzzling me is that this macro gives different results everytime that it is used. It is run against the same set of data, so I do not understand why it is producing different data in the spreadsheet. The attached macro code "runs against data" in a spreadsheet that is exported from SmartList. Different results are given every time the macro runs. That's not good. The results need to be consistent. Is there anything in t...

Summation
I would like to do a Summation with lower bound of summation 0, and n is the upper bound of summation. Could i used it in a queryBuilder? Use the query designer. In a total query, add an appropriate WHERE condition (get the word WHERE in the GROUP line of the grid). A WHERE condition eliminates records BEFORE the summation occur. The produced SQL code may look like: SELECT SUM(amount) FROM myTable WHERE itemNumber BETWEEN 0 AND [n] as example. Hoping it may help Vanderghast, Access MVP "fm3c" <fm3c@gmail.com> wrote in message news:C6626560-A03D-4F38-BABD-0A489...

Converting from time format to decimal and figuring the difference
I am creating a dialy time record for reporting to an online time card system. I need to convert from excel's time format to decimal format and figure the difference from start time and finish time in 1/10th hour increments and have it displayed it as such. Hi Steve, I expect the first problem you will run into is converting an Excel time to hours. After you have the difference or sums of Excel times and you want to convert to hours multiply by 24 and format as a number representing hours and decimal hours. Time and dates are measured in days after 1899 or other reference point....

how to import a different background on different pages?
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel How do I put a different background image (that I originally created in Illustrator and exported as a MicroSoft png file) on different Word pages. For example: Page 1 has a graphic A, page 2 has graphic B, page 3 has graphic C, etc. These are full size background images. I imported the images directly in and the file size was huge. Is there another way to do this? My png files are really small. <br><br>thanks - Britt Hi Britt: I would be inclined to use PDF or EPS format out of Illustrator, not ...

c1041724 restoring on different server
Hello I am trying to restore the exchange server to another box, ive tried replaying the logs and even eseutil /p /i or just /p but the same error, i am running exch 2000 enterprise, an upgrade from sbs. the store wont mount. I have support for chinese and other languages on both the production server and the restore server. I tried restoring AD on this machine but exchange complains about not been the schema master. The ad on this machine has no accounts, i figure after reparing the database i can map the mailboxes but now luck for a few days. Any recommendations apreciated. thanks I got it...

alternate sort on different columns
I try to sort a spreadsheet (Excel 2003) alternatively in two different ways, independent of each other. It means, if a certain condition is met, the data should be sorted on column A, if the other condition is met, it should be sorted on column D. Right now I have the problem that Excel remembers the previous sort (that is, if I first sorted on column D, the next sort on column A keeps the previous sort on D - which I don't want. How can I undo the previous sort? Aurora ...

graph to show correlation btwn two data sets of different ranges.
I have variables a,b,&c for items 1-5. The range for these variables is 100-1000. There is a fourth varibale (d) that ranges from 1-12. I want to show in 1 graph that changes in variables a,b,&c are directly correlated to variable d. Here's what I am thinking: 1. Create a column graph for variables a,b,&c with the y axis label listed on the left. 2. Add a line graph of variable d with the y axis label on the right. Problem: - I have no idea how to add the line graph over the bar graphs. - I can't for the life of me figure out how to move the y axis label to the right side...

Output To for different workbooks in a spreadsheet?
Hi All, Access2000. I've got a query I have running in conjunction with an OUTPUT TO statement that pops the data over to an Excel spreadsheet. However, I've got several to output...and they all need to be "TO" the same spreadsheet, but different workbooks in that same spreadsheet. It's working fine to individual spreadsheet files, but then I have to have the operator copy/paste into the one that has separate worksheets. A little bit cumbersome for something that'll be done regularly. Is this possible? I'm completely unfamiliar with Excel and would ra...

List Differences
Hello All, I have a column with hundreds of entries consisting o approx twenty different number values ( very random locations ) ie. 00 or 343 or 552 etc - how can I produce a column that will list only on of each of the values but in an ascending order? any help would b appreciated -- spar ----------------------------------------------------------------------- sparx's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1678 View this thread: http://www.excelforum.com/showthread.php?threadid=53180 http://www.cpearson.com/excel/deleting.htm#DeleteDuplicateRows -- ...

Focus on different windows
Hi everybody... I need to do some test on url addresses... I have to open a page and see read the url in the address bar... then I have to do the test with the url that I gave at the beginning... I make an example.... if I write http://www.virgilio.it the url changes in http://www.virgilio.it/home/index.html... then I have to do the test and see if they are the same... so... for opening internet no problem... I use this piece of code... [...] CString address; address = "c:\\progra~1\\intern~1\\iexplore.exe http://www.virgilio.it"; system(address); [...] until here it i...

How do you save a created template in a different location?
I want to save a template into a public drive so that more than more user can access it at the same time. Currently when I save a template if one user has it open no one else can access it. That's the way it's supposed to work. One user at a time. -- JoAnn Paules MVP Microsoft [Publisher] ~~~~~ How to ask a question http://support.microsoft.com/KB/555375 "cteacher" <cteacher@discussions.microsoft.com> wrote in message news:5ACB1D18-623E-40A4-B077-67A7C7DED0CC@microsoft.com... >I want to save a template into a public drive so that more than more user >ca...

Publisher cannot open files from a different version (again!) #2
browsing the discussions on that subject, I couldn't find a solution for it. I have received Publisher files by mail that have been stored in the 2000 format. I have Publisher 2000 SR-1 on Win XP Home, as well as Office 2003 Small Business Edition. As I hardly use Publisher, there is no need to upgrade to Version 2003. Is there any possiblity to open the files or at least extract the graphics and text portions? I appreciate any help on this. Rainer Rainer_K <rkitza@web.de> was very recently heard to utter: > I have received Publisher files by mail that have been stored in t...

Designate a different System Administrator
We are running Exchange server 2003 .We are getting inundated with Sys Admin notifications. I want to change the recipient of these messages to the internal email account postmaster@xxxxx.com. I want to be able to run a rule against them deleting them. Any help would be appreciated. Here is the link on how to set the NDR recipient. http://support.microsoft.com/default.aspx?scid=kb;en-us;294757 "Vulcan2k04" wrote: > We are running Exchange server 2003 .We are getting inundated with Sys Admin > notifications. I want to change the recipient of these messages to the >...

Summate by Month
Hi All I know this is simple, but cannot see wood for trees at present ! I have a big spreadsheet that has weekly values of hours worked by activity for staff members [staff names running down the page, weekly values running across]. I can produce a simple bar chart with a 'time-scale' x-axis using total hours booked per week, but I want to summate the weekly values into a monthly total and plot that. I can change the timescale to months rather than weeks OK, but the bars are 'stacked' behind each other, rather than a single overall total per month. What am I missing ...

Workbooks for different versions of excel
I have an excel form to be accessed from our intranet that was designed in Excel 2000. When I test it with older versions of excel I get lots of run time errors yet it works fine on my own machine. I have saved the file using the Excel 97-2000 option (This covers all of the versions being used) but I am still getting run time errrors when using it with older versions. Can anyone help? Do you have any references to libraries that are version specific? You can see the references in your workbook via the VBE, tools|references. If you have references to, say, Word2000 (Word 9.0), you may want t...

Summation Help
Hello, To perform a summation I have used the code: Application.CommandBars("Standard").Controls ("&AutoSum").Execute However, now that I have switched to MS Office 2003 there is a dropdown menu in the Summation toolbar object. This does not allow the summation to complete. Does anyone know how I can get my auto summation to work again. Thank You, Here's a post from Jim Rech via Tom Ogilvy: http://groups.google.com/groups?threadm=%23IwNwtLECHA.2040%40tkmsftngp05 John wrote: > > Hello, > > To perform a summation I have used the code: > &g...

Excel 2007
Hi, I am using Excel 2007 on Vista Ultimate. Importing a text file (txt and/or CSV) using "Data\Get external data\From text" and changing decimal separator to point (from comma) in advanced settings gives me an issue, namely that the applications keeps changing the separator back to comma (being my system setting). This is only an issue that I have experienced with Excel 2007. I have tried making makro using (VBA) DecimalSeparator and that only solves the issue for txt files NOT for CSV files. Apparently Excel handles the files differently depending on extension, even though...

Different charts for different salesmen
I have a spreadsheet which shows, in rows, the type of business brought in by different salesmen. It also has a column which has each salesman's initials in it. So the seller in each row can be identified. The relevant part of the spreadsheet looks like this Initials Date Product Initials Date Product Initials Date Product Initials Date Product I want to produce a chart of the business types sold by each salesman. Obviously I can do it for the whole firm but how do I produce a chart for each salesman? Thanks Rob Graham You need to prep the data. You could simply filter the dat...

Account List Balance different from the Register End Balance of the account-Money 2003
Help? In microsoft.public.money, Andrej wrote: >Help? > Let's start with the easy one. Open Money with Start->Run and entering "msmoney -s" without the quotes into the box. Hope it fixes it. ...

Difference between "Unique Index" and "Primery key Index"?
I want to make indexes using sql server management studio 2008. By table design form- "Indexes/Keys" pannel, I reaches "General" section having two fields: -Is Unique -Type I can make an index with "Tyep = Index" & "Is Unique = Yes" and I make aother index with "Tyep = Primery Key" & "Is Unique = Yes"(can not be changed) What is the difference between these two indexes? Please note that I am not asking about differences between Primary Key and Unique Index. I am just focusing on these two indexes only. T...