SUMing small numbers

For simplicty say I have two cells with values 0.0000004499190595611849428288 
and 0.0000004499190595611849428288. If I have a formula that adds the two 
cells (=A1 + A2) the value seems to be accurate. But if I use the 'SUM' 
function (=SUM(A1:A2)) I get a result of zero, exactly. I have tried 
formatting the result of the SUM with scientific notation just to see if the 
value is just too small but it is really zero. I should see the sum to be 
something like .0000009... but it is zero. This came up when I tried to SUM 
about 31,000 data values (all small like this) and the result came up to be 
zero. When I try to compute standard deviation or average I get a divide by 
zero error, presumably because the SUM is zero. Any suggestions?

Thank you.

Kevin

0
5/30/2008 2:51:00 PM
excel 39880 articles. 2 followers. Follow

6 Replies
308 Views

Similar Articles

[PageSpeed] 52

15 digit max
-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"Kevin Burton" <KevinBurton@discussions.microsoft.com> wrote in message 
news:50A06B25-C38F-41E5-82B6-7494022F4661@microsoft.com...
> For simplicty say I have two cells with values 
> 0.0000004499190595611849428288
> and 0.0000004499190595611849428288. If I have a formula that adds the two
> cells (=A1 + A2) the value seems to be accurate. But if I use the 'SUM'
> function (=SUM(A1:A2)) I get a result of zero, exactly. I have tried
> formatting the result of the SUM with scientific notation just to see if 
> the
> value is just too small but it is really zero. I should see the sum to be
> something like .0000009... but it is zero. This came up when I tried to 
> SUM
> about 31,000 data values (all small like this) and the result came up to 
> be
> zero. When I try to compute standard deviation or average I get a divide 
> by
> zero error, presumably because the SUM is zero. Any suggestions?
>
> Thank you.
>
> Kevin
> 

0
dguillett1 (2488)
5/30/2008 3:01:57 PM
15 digits NOT 15 significant figures?

Anyway, even with 15 digits I should see the sum of .000000449919060 and 
..000000449919060  which would not be zero. There is something else wrong. 
Again if I simply add the cells it seems accurate. Using SUM or any of the 
functions that rely on SUM (STDEV, VAR, etc.) result in bad results.

"Don Guillett" wrote:

> 
> 15 digit max
> -- 
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguillett1@austin.rr.com
> "Kevin Burton" <KevinBurton@discussions.microsoft.com> wrote in message 
> news:50A06B25-C38F-41E5-82B6-7494022F4661@microsoft.com...
> > For simplicty say I have two cells with values 
> > 0.0000004499190595611849428288
> > and 0.0000004499190595611849428288. If I have a formula that adds the two
> > cells (=A1 + A2) the value seems to be accurate. But if I use the 'SUM'
> > function (=SUM(A1:A2)) I get a result of zero, exactly. I have tried
> > formatting the result of the SUM with scientific notation just to see if 
> > the
> > value is just too small but it is really zero. I should see the sum to be
> > something like .0000009... but it is zero. This came up when I tried to 
> > SUM
> > about 31,000 data values (all small like this) and the result came up to 
> > be
> > zero. When I try to compute standard deviation or average I get a divide 
> > by
> > zero error, presumably because the SUM is zero. Any suggestions?
> >
> > Thank you.
> >
> > Kevin
> > 
> 
> 
0
5/30/2008 3:25:00 PM
Kevin,
Adding the two numbers with or without "Sum" gave the same result for me...
approx 0.0000009
I did format the data and formulas cells with a number format to 21 places 
before starting.
You could be the victim of a cell formatted as "text".  
The Sum function will ignore numbers formatted as text in cell references.

Also, regarding summing very small numbers, there is a floating point
truncation error that can cause the result to vary depending on the order 
in which the numbers are totaled.
-- 
Jim Cone
Portland, Oregon  USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)





"Kevin Burton" 
<KevinBurton@discussions.microsoft.com> 
wrote in message 
For simplicty say I have two cells with values 0.0000004499190595611849428288 
and 0.0000004499190595611849428288. If I have a formula that adds the two 
cells (=A1 + A2) the value seems to be accurate. But if I use the 'SUM' 
function (=SUM(A1:A2)) I get a result of zero, exactly. I have tried 
formatting the result of the SUM with scientific notation just to see if the 
value is just too small but it is really zero. I should see the sum to be 
something like .0000009... but it is zero. This came up when I tried to SUM 
about 31,000 data values (all small like this) and the result came up to be 
zero. When I try to compute standard deviation or average I get a divide by 
zero error, presumably because the SUM is zero. Any suggestions?
Thank you.
Kevin

0
5/30/2008 3:43:18 PM
How did you specify the number format as 21 places? I formatted the whole 
range with 0.0000E+00 and still got a zero sum. Thank you.

Kevin

"Jim Cone" wrote:

> Kevin,
> Adding the two numbers with or without "Sum" gave the same result for me...
> approx 0.0000009
> I did format the data and formulas cells with a number format to 21 places 
> before starting.
> You could be the victim of a cell formatted as "text".  
> The Sum function will ignore numbers formatted as text in cell references.
> 
> Also, regarding summing very small numbers, there is a floating point
> truncation error that can cause the result to vary depending on the order 
> in which the numbers are totaled.
> -- 
> Jim Cone
> Portland, Oregon  USA
> http://www.realezsites.com/bus/primitivesoftware
> (Excel Add-ins / Excel Programming)
> 
> 
> 
> 
> 
> "Kevin Burton" 
> <KevinBurton@discussions.microsoft.com> 
> wrote in message 
> For simplicty say I have two cells with values 0.0000004499190595611849428288 
> and 0.0000004499190595611849428288. If I have a formula that adds the two 
> cells (=A1 + A2) the value seems to be accurate. But if I use the 'SUM' 
> function (=SUM(A1:A2)) I get a result of zero, exactly. I have tried 
> formatting the result of the SUM with scientific notation just to see if the 
> value is just too small but it is really zero. I should see the sum to be 
> something like .0000009... but it is zero. This came up when I tried to SUM 
> about 31,000 data values (all small like this) and the result came up to be 
> zero. When I try to compute standard deviation or average I get a divide by 
> zero error, presumably because the SUM is zero. Any suggestions?
> Thank you.
> Kevin
> 
> 
0
5/30/2008 9:31:02 PM
The formatting should be done before entering the numbers.
Go to Format (menu) | Cells | Number (tab)
Choose number in the "category" box.
Change the value in the "decimal places" box.

A quick, easy way to make sure numbers are treated as numbers (not text) is to...
Enter 1 in a blank cell.
Copy the cell.
Select your numbers.
Go to Edit | Paste Special 
Checkmark "multiply" and click OK.
-- 
Jim Cone
Portland, Oregon  USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"Kevin Burton" 
<KevinBurton@discussions.microsoft.com> 
wrote in message 
How did you specify the number format as 21 places? 
I formatted the whole range with 0.0000E+00 and still got a zero sum. 
Thank you.
Kevin


"Jim Cone" wrote:
> Kevin,
> Adding the two numbers with or without "Sum" gave the same result for me...
> approx 0.0000009
> I did format the data and formulas cells with a number format to 21 places 
> before starting.
> You could be the victim of a cell formatted as "text".  
> The Sum function will ignore numbers formatted as text in cell references.
> 
> Also, regarding summing very small numbers, there is a floating point
> truncation error that can cause the result to vary depending on the order 
> in which the numbers are totaled.
> -- 
> Jim Cone
> Portland, Oregon  USA
> http://www.realezsites.com/bus/primitivesoftware
> (Excel Add-ins / Excel Programming)
> 
> 
> 
> 
> 
> "Kevin Burton" 
> <KevinBurton@discussions.microsoft.com> 
> wrote in message 
> For simplicty say I have two cells with values 0.0000004499190595611849428288 
> and 0.0000004499190595611849428288. If I have a formula that adds the two 
> cells (=A1 + A2) the value seems to be accurate. But if I use the 'SUM' 
> function (=SUM(A1:A2)) I get a result of zero, exactly. I have tried 
> formatting the result of the SUM with scientific notation just to see if the 
> value is just too small but it is really zero. I should see the sum to be 
> something like .0000009... but it is zero. This came up when I tried to SUM 
> about 31,000 data values (all small like this) and the result came up to be 
> zero. When I try to compute standard deviation or average I get a divide by 
> zero error, presumably because the SUM is zero. Any suggestions?
> Thank you.
> Kevin
> 
>
0
5/30/2008 10:04:13 PM
On Fri, 30 May 2008 07:51:00 -0700, Kevin Burton
<KevinBurton@discussions.microsoft.com> wrote:

>For simplicty say I have two cells with values 0.0000004499190595611849428288 
>and 0.0000004499190595611849428288. If I have a formula that adds the two 
>cells (=A1 + A2) the value seems to be accurate. But if I use the 'SUM' 
>function (=SUM(A1:A2)) I get a result of zero, exactly. I have tried 
>formatting the result of the SUM with scientific notation just to see if the 
>value is just too small but it is really zero. I should see the sum to be 
>something like .0000009... but it is zero. This came up when I tried to SUM 
>about 31,000 data values (all small like this) and the result came up to be 
>zero. When I try to compute standard deviation or average I get a divide by 
>zero error, presumably because the SUM is zero. Any suggestions?
>
>Thank you.
>
>Kevin

If your cells are showing the values you have posted, then the values must have
been entered as text and not as numbers, as Excel is limited to 15 digit
precision, in accordance with the IEEE standards to which it, and many other
spreadsheet programs, adhere.

The SUM function ignores text in cells, so gives you a result of '0'

However, when you use the addition operator, Excel first tries to convert the
text strings to a number, which, in this case, is a value of
0.000000449919059561184 or 4.49919059561184E-07.

Of interest is that SUM (at least in Excel 2007) does not ignore TEXT if it is
entered directly as an argument.  So the formula:

=SUM("0.0000004499190595611849428288","0.0000004499190595611849428288")

gives a result of approximately 9E-07.
--ron
0
ronrosenfeld (3123)
5/31/2008 12:39:44 AM
Reply:

Similar Artilces:

web page is small
I have built a .vsd file with flow chart & text of a few lines. then saved it as .htm file. The problem is that when I open it as a web page I see the whole text very small. I can modify it through Pan & Zoom, but I want to be produced larger in the first place. how do I do it? With the web pages produced by Visio 2003, if you're hosting the web pages on a web server, you can pass arguments to the page to set the initial zoom. For instance: http://servername/blah.htm?zoom=200 -- Andy Microsoft Corporation This posting is provided "AS IS" with no warranties, and conf...

Line and row numbers
Line and row numbers ......... I took these out some time ago so that my sheet does not display them, how do i get them back in again thanks Tools-->Options, View tab. Check Row & Column Headings. ************ Anne Troy www.OfficeArticles.com "Scudo" <me@nospam.fictional> wrote in message news:OX8bf.43372$7x2.32069@fe1.news.blueyonder.co.uk... > Line and row numbers ......... I took these out some time ago so that my > sheet does not display them, how do i get them back in again > > thanks > > > On the menu, select Tools > Options. C...

testing while suming
I have two columns of data as follows 1 teama 2 teamb 3 teamc 4 teama 5 teamb 6 teamc The number of rows will not change. I'm trying to sum the value that is associated with each team name. Can anyone point me in the right direction? Try this: =SUMIF(B1:B6,"teama",A1:A6) If you want to list your teams in a range somewhere, eg D1:D3, then you can put this variation in E1 and copy into E2 and E3: =SUMIF(B$1B$6,D1,A$1:A$6) Hope this helps. Pete On Aug 14, 4:30 pm, angryelvis <angryel...@discussions.microsoft.com> wrote: > I hav...

Setup a public calendar for a small office.
I have a client with Exchange 2000 running on a Windows 2000 server with TCP/IP protocol. They would like to have a public calendar that everyone has access to view, but can not edit it in anyway. Only one person, the office administrator, will have the ability to create and edit entrees. They use this as a way to inform every one of scheduled vacations and other tasks like staff meeting, etc. I would like to know the best way to accomplish this task. Should I create a new user called calendar or can I just setup a calendar item under another user and make that available to all. T...

Small Company SPF Issue
We are a very small company with a central office and 18 District Offices. We put our SPF record in place on Tuesday but removed it on Wednesday. The reason we removed it was our 18 District Offices were seeing their e-mail messages rejected by various servers. The District Offices are on DSL lines through various DSL vendors (i.e. Sprint, Verizon, etc.) They are setup for outgoing e-mail to be our company @xxxx.com domain name and they pickup their e-mail from our company exchange server. The SPF issue is our @xxx.com SPF IP does not contain the IP's for the DSL carrier...

Current year and current week number
I can obtain the current year with YEAR(TODAY()) and the current week number with WEEKNUM, but I want to display the results in an ?array. By this I mean; row 1 contains the week nos 1 -52; column A contains the years 2010,2009,2008. I want the result to look like: 2010 N N N -----N until week 52 2009 Y Y Y ------ Y until current week, then N N N until week 52 2008 Y Y Y ------ Y until week 52 My current attempt fails, which is =IF(AND(Current_Year>=$A2,Current_Week>=$B$1),"Y","N") The purpose is to automatically populate a chart (graph) with curren...

Outlook 2007 'Contacts' list font is too small
Cannot find out how to increase size. At the bottom right on the status bar is a slider that controls the zoom. Try sliding it to the right (or click the plus sign in the circle). -- -Ben- Ben M. Schorr, MVP Roland Schorr & Tower http://www.rolandschorr.com http://www.officeforlawyers.com/outlook.html Author: The Lawyer's Guide to Microsoft Outlook 2007: http://tinyurl.com/ol4law-amazon "Peter" <lysdexic@hotmail.com> wrote in message news:uRhaafJJKHA.1380@TK2MSFTNGP02.phx.gbl... > Cannot find out how to increase size. > __________ Information from ESET...

Adding up ranges of numbers...
Help! :confused: I have a spreadsheet of numbers... sales figures for a number o stores.. with each row being a store, and each column being the sale for one day. So for each month, I end up with 31 columns of numbers i 10 rows (10 stores) 2 times a week, on tuesdays and fridays, I need to add up the month t date sales. This means from the 1st of the month to the first tuesday first friday, second tuesday, second friday.. etc etc... The tota number of days in this formula varies each month since the month start of a different day each month, and makes updating the spreadsheets pain since ...

Extracting numbers from text
Howdy all Ive got the following in a cell... Scratched: 4-Bardon Fella, 6-Del's Boy, 10-Country View, 11-Tornado Storm, 12-Tudor Inn. And I need to extract the numbers 4 6 10 11 12 each into its own cell... what would be the best way of extracting the numbers..... One way, by brute force: This is for 5 horses. Need more, just add more "Finds". =MID(A1,FIND("-",A1)-2,2) =MID(A1,FIND("-",A1,FIND("-",A1)+1)-2,2) =MID(A1,FIND("-",A1,FIND("-",A1,FIND("-",A1)+1)+1)-...

SUMing small numbers
For simplicty say I have two cells with values 0.0000004499190595611849428288 and 0.0000004499190595611849428288. If I have a formula that adds the two cells (=A1 + A2) the value seems to be accurate. But if I use the 'SUM' function (=SUM(A1:A2)) I get a result of zero, exactly. I have tried formatting the result of the SUM with scientific notation just to see if the value is just too small but it is really zero. I should see the sum to be something like .0000009... but it is zero. This came up when I tried to SUM about 31,000 data values (all small like this) and the result cam...

Trouble formatting page numbers
I have a document that uses a 2 page master, both pages have a page number centered in the footer. When I switch from the Master to the publication pages, the page number moves over to the outer margin of the page...I hae tried deleting the footer and re-insterting, copying from a file that the footer is centered correctly, etc... nothing seems to work for long. Any ideas? Thank you! Tanya You can move the page number text box anywhere on the Master Page, format it right, left or center. It behaves the same as any text box. -- Mary Sauer http://msauer.mvps.org/ "Tanya" ...

How do I create very small graphics in ONE cell?
I have data like following and I want to creat a line or 3 bars to reflect the numbers in ONE cell so that people can quicky scan through the trend. 3,225 2,356 1,087 I tried wizard but it doesn't look good when you minimize it. I tried autoshape but couldn't let the ahotopshap connect to the numbers. My boss said he has seen people do this before. Does anyone know how to do it? Thanks! There's a couple of ways to go. The first is to adapt the idea behind Using Worksheet Cells to simulate a graph http://www.tushar- mehta.com/excel/newsgroups/worksheet_as_chart...

Saving custom number format
1) I am trying to save a custom number format for e.g. ###,###,000.000 But when I open a new file this format is not available. Why doesn't it gets saved? (excel 2002) 2) My home computer has recently been upgraded to the latest version of excel. But I used to hit Alt + F and then C to close a file in earlier versions. This no longer works! I have to hit Enter key after pressing the "C". Is it a bug? or am I missing something? Shantanu Oak Custom formats are saved with the workbook. To have these available for all new workbooks, save a blank workbook with the custom formatti...

Formatting cells for phone numbers
Hey, im looking to format a number of cells into a phone number format I have a rather large excel spreadsheet of around 900 phone numbers they are currently in different formats such as: 0870 8888888 +44 0800 7777777 +800 (900) 666666 0980 888888 These are obviously just example numbers and not real ones, as you ca see they are all over the place and in the wrong format that i nee them in. Changing them all manually would take me forever so ive trie to create a custom formatting rule to have them represented like this: +44 (800) 888888 So basically they must all begin with +44 then the n...

Checking cell for Number
I have about 200 numbers in column A. I would like to enter any number in Column B. In column C I would like, it to show if the number is used or free. can this be done with a if statement? Thanks Try... =IF(COUNTIF($A$1:$A$200,B1),"Used","Free") If you have a list of numbers in Column B that you'd like to check, enter the above formula in C1, and copy and paste the formula to your other cells in Column C. Hope this helps! In article <E5CA9196-6FCF-492C-B488-1B7E431720CF@microsoft.com>, Lee <Lee@discussions.microsoft.com> wrote: > I have abou...

Recommendations on number of items in PST and OST
I need to know whether if there is a recommendation on number of ITEMS in a PST and OST? I am currently running MS Outlook 2003. Thanks I know that there is a limit to the number of items in a single folder in a pst file...it was around 16,000...actually had a user run into it once, and that folder was no longer accessible... -- Susan Conkey [MVP] "Hasher" <Hasher@discussions.microsoft.com> wrote in message news:2864AF80-FD7A-4578-8A80-AB2A32E75993@microsoft.com... > I need to know whether if there is a recommendation on number of ITEMS in a > PST and OST? > &g...

Change positive number to negative number
Hi, I've searched this board and have tried a few things, no luck yet. I have about 100 rows, in columns A and B there is descriptive text. In column C it is either blank or it contains the text "CR". In column D there is a value (number with 2 decimal places). Column E is blank and available to use for the formula. If there is "CR" in column C, then the value in Column D must be displayed as a negative number. If "CR" doesn't exist then the number should be left positive. How can I do this? I've been working with the ABS and I...

Number of contacts?
What is the max. number of contacts that can be in a folder. I have outlook 2003. -- jsanders jsanders <jsanders@discussions.microsoft.com> wrote: > What is the max. number of contacts that can be in a folder. I have > outlook 2003. 64K -- Brian Tillman ...

Filter for 225 or 233 numbers #2
Hi Julie and Frank I have just tried your formulas and they dont seem to work I the no. stored as text due to a 0 being at the front of them when i convert to a no. Julie your formula only dispays false and Frank your formula doesnt display anythign in the cell next to it it blank any ideas what am i doing wrong -- hogan ----------------------------------------------------------------------- hoganc's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1498 View this thread: http://www.excelforum.com/showthread.php?threadid=26686 Hi should work if you leave t...

limit on number of characters?
I am using Excel 2003, SP2. I just exported the results of an Access query to Excel and found that the data in one of the fields is being truncated after 255 characters. I can type more than 255 characters in a cell. Does anyone know how I can export more than 255 characters into Excel? Thank you, Judy Ward I don't speak the Access, but Debra Dalgleish once posted this: Or, transfer the data with code. Use the TransferSpreadsheet method to send the data, and specify one of the later Excel versions: '====================== Sub SendTableToExcel() DoCmd.TransferSpreadsheet a...

Small Database issue.
Hi, I am using a Dialog based application connected to MS-Access database via ODBC connection. When i delete one record from my dialog how do i refresh the dialog so that it does not show the deleted record anymore. You have not mentioned in which control you are listing your records. Better way is reload the records again to the dialog . Jibesh. M2 Software. -- ------------------------------------------------------------------------- FIGHT BACK AGAINST SPAM! Download Spam Inspector, the Award Winning Anti-Spam Filter http://mail.giantcompany.com "Sanjeev"...

Printing
Hi, I have purchased small card stock (5.5x8.5). I can't seem to set up the page correctly to print a card design on this size paper. My printer is HP PSC 2510. Any help would be appreciated. Angela OS: Windows XP Media Center Publisher 2003 First set the paper size in the printer settings. Then it will setup in the page size. -- Don Vancouver, USA "Angela" <Angela@discussions.microsoft.com> wrote in message news:24425095-0386-404B-AF91-10DED83ACE9F@microsoft.com... > Hi, > > I have purchased small card stock (5.5x8.5). I can't seem to set up ...

'Text' formatted cells displaying numbers in scientific format
I use Excel to view statement information downloaded from my bank in CSV format. As some fields have long numeric strings that Excel displays in scientific notation (4988243043081440 displays as 4.98842E+15), I use Copy > Paste Special > Values to copy the data to a new sheet which is correctly formatted in order to view the data correctly as text. Despite this, some cells still display the numeric string in scientific notation. Selecting the cell shows that it's already correctly formatted. Applying the change again or changing it then changing back makes no difference, ho...

Can you round numbers to display a specific set of numbers, for e.
I am working on a timesheet, and have a question. Due to a request by a user to be able to only enter time with a signle keystroke, for example, 8:00, only enter 8. Due to that, I have changed my cell formatting from time to numbers. Now, I need a formula that will round the number to a set of predetermined minutes. Example: If a user enters 8.25 for a time in, we would like a formula to change the number to 8.30. I need the formula to do this for the following entries: 0-14: 0 15-29: 15 30-44:30 45-59: 45 Any ideas, or am I very far off base? Try this =ROUNDDOWN(TIME(INT(A11)...

Hardware Requirements for Small Scale CRM Demo
Hi guys, I want to install SQL Server 2005 and CRM 3.0 on the following hardware: Dell 1400SC, 933Mhz PIII, 640MB RAM, 18GB U160 SCSI Q: Is this hardware going to be sufficient for a small scale demo or is it going to crawl? Robert you might get by if you could somehow get the RAM up to 1Gb or more. I think she might crawl, but worth a try as-is. If you get it all working and it's still slow, spend some $ on RAM. Dave "MS" <robert@upstateis.com> wrote in message news:%23Zg8aM8NGHA.208@tk2msftngp13.phx.gbl... > Hi guys, > > I want to install SQL Ser...