Significant digits

This may be in a FAQ somewhere, but I'm not even sure how to search for 
it. It's not really about significant digits, but the subject line's 
got to say something. If anyone's interested, see if you can reproduce 
the following results in Excel. I'm using Excel 2003 SP3.

1. In cell A1, type: 
    	=1.1-0.9
I should get a 0.2, and I do.

2. In cell A2, type:
    	0.2
(It doesn't really matter if you type the leading zero; it'll get 
added. The two cells should look identical, right?)

3. In cell B1, type:
    	=IF(A1=A2,"Equal","No")
This should come back saying "Equal" and it does.

4. In cell B2, type:
    	=IF(A1-A2=0,"Equal","No")
Since A1 and A2 are equal, subtracting them should get me zero. Yet 
this comes back with a "No" in my Excel 2003 SP3.

Weird, huh? What gives?

I tried formatting the cells to 30 decimal places to see if there was 
anything lurking in the insignificant digits. Nothing.

But, wait! Try this step:

5. In cell B3, type
    	=A1-0.3
That gives me -0.10, just as I'd expect. But format THAT cell to 30 
decimal places and ... I get -0.099999999999999900000000000000. (You 
can also see this by copying the cell and pasting the value only into 
another cell. Then it will still say -0.10, but the formula bar will 
tell you a different story.

As I say, what gives? This happens on all three of my computers that 
have Excel 2003 SP3. I don't have any other versions to test. None of 
the computers is brand new, but none are old Pentium 4s or anything. 
The one I discovered the error on is less than two years old--a Dell 
Optiplex GX745.

-- 
Opus the Penguin
The best darn penguin in all of Usenet
0
Opus
1/26/2010 3:05:39 PM
excel 39879 articles. 2 followers. Follow

4 Replies
822 Views

Similar Articles

[PageSpeed] 46

Hello,

See point 8 of my Excel Don'ts, please:
http://sulprobil.com/html/excel_don_ts.html

Regards,
Bernd
0
Bernd
1/26/2010 3:18:34 PM
Bernd P (bplumhoff@gmail.com) wrote:

> Hello,
> 
> See point 8 of my Excel Don'ts, please:
> http://sulprobil.com/html/excel_don_ts.html
> 
> Regards,
> Bernd

Interesting. The Don't says "Never ever test double precision numbers 
with = for equality. Use a test like ABS(a - b) < 1E-13 instead." But 
in the example I posted, the = will return a true statement that the 
numbers are equal. It's only when I subtract the one from the other 
that Excel erroneously says they are not equal.

Since the numbers ARE equal (out to the zillionth decimal place), is 
there any reason I shouldn't violate your Don't and test for that 
equality with the = sign?

-- 
Opus the Penguin
The best darn penguin in all of Usenet
0
Opus
1/26/2010 3:25:25 PM
"Opus the Penguin" <opusthepenguin+usenet@gmail.com> wrote:
>    =IF(A1=A2,"Equal","No")
> This should come back saying "Equal" and it does.
[....]
>    =IF(A1-A2=0,"Equal","No")
> Since A1 and A2 are equal, subtracting them should get me zero. Yet
> this comes back with a "No" in my Excel 2003 SP3.
>
> Weird, huh? What gives?

For all the gory details, see http://support.microsoft.com/kb/78113, notably 
the section titled "Example When a Value Reaches Zero".  The section is 
mistitled, and the description is technically incorrect and factually 
incomplete.  But it gives a hint as to answer to your question.

The short answer is....  Because of the way that Excel (and most computer 
applications) store numbers and perform arithmetic operations, most numbers 
with decimal fractions (as well as integers with 16 or more digits) cannot 
be stored accurately.

Consequently, for example, IF(10.1-10=0.1,TRUE) is FALSE, and 10.1-10-0.1 is 
not exactly zero.

But Excel has implemented some half-baked heuristics that attempt to correct 
for such anomalies.  They are "half-baked", first, because they are applied 
to only particular circumstances, and second, because they create surprises 
like the one you stumbled across.

Consequently, using your example, =1.1-0.9-0.2 is exactly zero, but 
=(1.1-0.9-0.2) is not.

Rather than try to explain this (although I can, in overwhelming detail), it 
might help if you simply concentrate on the work-arounds.  None is perfect.

Generally, it is wise to explicitly ROUND the results of any computation 
that might result in numbers with decimal fractions, especially 
dollars-and-cents.  This will ensure (subject to Excel defects) that the 
result will have the exact same binary representation as if you had typed 
the constant manually.

So returning to your example, A1 should be =ROUND(1.1-0.9,1) instead of 
simply =1.1-0.9.  Alternatively, =IF(ROUND(A1,1)-ROUND(A2,1)=0,TRUE).  Note: 
You might choose a different precision for ROUND, depending on your needs. 
But it should be close to the number of fractional digits that you expect to 
be "accurate".

Alternatively, you might use the calculation option "Precision as Displayed" 
(PAD, under Tools > Options > Calculation) to perform an implicit round of 
cells that have an explicit numeric format (not General).  But PAD can lead 
to lots of unexpected results, in part because of its exceptionless 
pervasiveness.  Also, once you select PAD, some constants might be changed 
irreversibly.  So be sure to make a copy of your Excel file before you 
experiment with PAD.

Finally, you could use ABS() to ensure that the result of a comparison is 
within a specified precision or difference.  For example, 
=IF(ABS(A1-A2-0.2)<0.05,TRUE).


> I tried formatting the cells to 30 decimal places

It only makes sense to format up to 15 significant digits.  That is the 
maximum number of significant digits that Excel will format.  The number of 
decimal places will depend on the magnitude of the number.  In your case, 
the maximum useful format is 15 decimal places.

But many people (including the writers of MS KB articles ;->) erroneously 
think that means that numbers contain only 15 significant digits and that 
arithmetic operations involve only 15 significant digits.  That is 
incorrect.

For example, 1.1 is 1.10...0 when displayed to 15 significant digits or 
more.  But it is actually stored as exactly 
1.10000000000000,0088817841970012523233890533447265625.  Those extra digits 
are the source of many "mysterious" results.  For example, the result of 
=(1.1-1-0.1) -- note the parentheses -- is about 8.3E-17, or exactly 
0.0000000000000000832667268468867,405317723751068115234375.

Even that exact result might be surprising.  But remember, 0.1 is not 
exactly 0.10...0.  And that is only part of the explanation in some cases.

Hope that helps.  It is only the tip of the iceberg.


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

"Opus the Penguin" <opusthepenguin+usenet@gmail.com> wrote in message 
news:Xns9D0C5C838D598opusthepenguinnettax@188.40.43.245...
> This may be in a FAQ somewhere, but I'm not even sure how to search for
> it. It's not really about significant digits, but the subject line's
> got to say something. If anyone's interested, see if you can reproduce
> the following results in Excel. I'm using Excel 2003 SP3.
>
> 1. In cell A1, type:
>    =1.1-0.9
> I should get a 0.2, and I do.
>
> 2. In cell A2, type:
>    0.2
> (It doesn't really matter if you type the leading zero; it'll get
> added. The two cells should look identical, right?)
>
> 3. In cell B1, type:
>    =IF(A1=A2,"Equal","No")
> This should come back saying "Equal" and it does.
>
> 4. In cell B2, type:
>    =IF(A1-A2=0,"Equal","No")
> Since A1 and A2 are equal, subtracting them should get me zero. Yet
> this comes back with a "No" in my Excel 2003 SP3.
>
> Weird, huh? What gives?
>
> I tried formatting the cells to 30 decimal places to see if there was
> anything lurking in the insignificant digits. Nothing.
>
> But, wait! Try this step:
>
> 5. In cell B3, type
>    =A1-0.3
> That gives me -0.10, just as I'd expect. But format THAT cell to 30
> decimal places and ... I get -0.099999999999999900000000000000. (You
> can also see this by copying the cell and pasting the value only into
> another cell. Then it will still say -0.10, but the formula bar will
> tell you a different story.
>
> As I say, what gives? This happens on all three of my computers that
> have Excel 2003 SP3. I don't have any other versions to test. None of
> the computers is brand new, but none are old Pentium 4s or anything.
> The one I discovered the error on is less than two years old--a Dell
> Optiplex GX745.
>
> -- 
> Opus the Penguin
> The best darn penguin in all of Usenet 

0
Joe
1/26/2010 4:43:50 PM
On Tue, 26 Jan 2010 15:05:39 +0000 (UTC), Opus the Penguin
<opusthepenguin+usenet@gmail.com> wrote:

>This may be in a FAQ somewhere, but I'm not even sure how to search for 
>it. It's not really about significant digits, but the subject line's 
>got to say something. If anyone's interested, see if you can reproduce 
>the following results in Excel. I'm using Excel 2003 SP3.
>

It is in the MSKB under the non-intuitive subject of "rounding errors".  I
forget the URL but you should be able to find it.

Basically it is related to the IEEE standard for double precision arithmetic,
and also to the impossibility of representing certain decimal numbers in a
finite number of binary digits.  (Similar to the impossibility of representing
1/3 exactly in a finite number of decimal digits).

--ron
0
Ron
1/26/2010 4:46:31 PM
Reply:

Similar Artilces:

How to round cells to two significant figures
I've been doing masses of calculations on excel and now i want to round my numbers to two significant figures quickly without having to do them one at a tim Hi Kristy........... Direct from the Help file.......... ROUND Rounds a number to a specified number of digits. Syntax ROUND(number,num_digits) Number is the number you want to round. Num_digits specifies the number of digits to which you want to round number. If num_digits is greater than 0 (zero), then number is rounded to the specified number of decimal places. If num_digits is 0, then number is rounded to the neares...

Significant Figures
How do I format cells to display a specified number of significant figures, such as three significant figures: 120 and 60.0? Thanks. Hi Stacey: Found in Google Search: What about =ROUND(A1, A2 - 1 - INT(LOG10(ABS(A1)))) to round A1 to A2 significant figures? Jerry W. Lewis Best wishes Bernard "Stacy" <Stacy_Nobles@Biogen.com> wrote in message news:1c1c01c372e8$d48f8320$a601280a@phx.gbl... > How do I format cells to display a specified number of > significant figures, such as three significant figures: > 120 and 60.0? Thanks. Thank you, but the problem is...

how do i show 3 significant figures
i need to show numbers to 3 significant figures. in one column, the numbers should show as 5.36, 7.56, 20.8, 47.6, 171, 291, 1070. all to 3 significant figures, but to different numbers of decimal places. thanks. On Thu, 18 Feb 2010 07:52:01 -0800, peter <peter@discussions.microsoft.com> wrote: >i need to show numbers to 3 significant figures. in one column, the numbers >should show as 5.36, 7.56, 20.8, 47.6, 171, 291, 1070. all to 3 significant >figures, but to different numbers of decimal places. thanks. You can use this formula in a "helper column":...

Significant figures
Say I have a number like 11745. Is it possible to have Excel display 12000 but internally remember that the real number is 11745? The ROUND function definitely does NOT work, since it ALWAYS truncates the number. Obviously, this is possible in the case of a number like 0.11745: it is simple to have Excel DISPLAY 0.12, but remember 0.11745. Is it possible to do this with big numbers? That particular number might be possible. Try a custom format of #,"000" the #, says to show in thousands. The "000" means to always add the characters "000" to whatever the form...

Digital ID
Exchange 2003 & Outlook 2003. To encrypt email message Digital ID is required. How to get digital ID from Exchange server instead from external certification authority? "Emyeu" <cmchong20@yahoo.com> wrote: >Exchange 2003 & Outlook 2003. >To encrypt email message Digital ID is required. >How to get digital ID from Exchange server instead from external >certification authority? Windows 2003 can be used as a CA. But that's a Windows Server question, not an Exchange problem. :) PKI isn't easy if you need more tha just server certs. -- Rich Ma...

Converting digits to characters (123 to ABC)
Hello Experts, At my shop I'm printing product labels from an Excel sheet. I'd like to add the cost field to the label but the problem everybody would be able to know my cost for an item unless I encode it, so I thought of converting numbers to characters for example: 0 = Z 1 = A 2 = B 3 = C 4 = D So, if the cost of an Item is 322.04 the field would show CBB.ZD Any thoughts? Thanks in advance Hmm, one problem with encoding is to obscure the value, so guessing the real value isn't easy and nonetheless make it siple for YOU to read the real value. Jus...

How do I convert 9 digit zipcodes to 5 digits?
I have an EXCEL 2003 spreadsheet containing a column with zip codes in this format: 12345-6789 How can I convert these zip codes to a column that just has the first five digits, that is: 12345 ? I will appreciate advice. In the new column use the formula =LEFT(A1,5) "Robert Judge" wrote: > I have an EXCEL 2003 spreadsheet containing a column with zip codes in this > format: > 12345-6789 > > How can I convert these zip codes to a column that just has the first five > digits, that is: > 12345 > > ? > > I will appreciate advice. =left(a1,5)...

Help with significant figure counter
I am trying to create a significant figure calculator, but I need some help. I would like to do it with Built-in functions if possible. I have formatted a column as text. The next is =FIND(".",A1), which will return a number if a decimal was found. The next is =COUNT(B1). Col C will be a Boolean (1 if there is a decimal, 0 if there is not). If there is a decimal, I want to find the first non-zero digit from the LEFT and count all digits from that one to the end (reading left to right). For example, 0.0070 should ignore the 0.00 and count only the 70 on the right (a...

How do I shorten 10-digit # so only 6 digits show up?
I have a list of 10-digit numbers, but I want to apply a formula/function or format the cells so that only the first 6 digits show up. Is this possible? Something like: =INT(a1/10000) swingkittenva wrote: > > I have a list of 10-digit numbers, but I want to apply a formula/function or > format the cells so that only the first 6 digits show up. Is this possible? -- Dave Peterson =left(A1,6) swingkittenva wrote: > I have a list of 10-digit numbers, but I want to apply a formula/function or > format the cells so that only the first 6 digits show up. Is this possible? =...

count digits within cell
I want to create a forumula in B1 that will sum up how many letters I have entered into A1. There will never be more than five digits in A1. I frankly don't know where to begin. example: A1 = MTW -- Thanks for being here Try: =len(a1) "apostate2" wrote: > I want to create a forumula in B1 that will sum up how many letters I have > entered into A1. There will never be more than five digits in A1. I frankly > don't know where to begin. > > example: A1 = MTW > -- > Thanks for being here Thanks! "Gary's Student" wrote: > T...

How to format without decimal point unless significant digits?
Is there a custom format string that will format a number so that the decimal point is displayed *only* if there are non-zero digits to the right? I tried #,##0.####, which works great except for integers. "12345" formats as "12,345." I'd like to get "12,345" But I want to get the decimal point and any non-zero digits to the right of the decimal point. Here are some examples: Before After 0.123456 0.1235 5.34 5.23 25 25 /* no decimal point */ 1,234.56789 1,234.5679 "Jenni...

changinging the color of sign digit
I would like to know how can I change the color of digit for example if I write the number -12.52 the color of the sign will change to blue and the rest will change to red. best barjini There's no way to do this and keep the entry a number. You *could* use an event macro to convert the number to text, then format the text, but the entry would no longer work in math operations. Post back if you want to pursue that option. In article <uEVcAgMVHHA.1208@TK2MSFTNGP03.phx.gbl>, "hassan barjini" <barjini@ikiu.ac.ir> wrote: > I would like to know how can I chan...

Windows 7 Media Center Digital Channel Guide
Working; Windows 7 Media Center w/ Hauppauge 1600 PCI card. Analoge/Digital channel source exclusively from Comcast cable TV. Technical Issue - Comcast Digital Channel Guide - mapping to manually added Comcast Digital Channels. W Media Center works perfectly with Comcast analoge channels. W Media Center "sees", after manually adding the Comcast digital channels in my area...WMC 'auto scan' does not find any Comcast digital channels... hence...WMC doesn't "load" the Comcast Digital Channel guide, since 'auto scan' doesn't find th...

Significant digit causing wrong value?
I have been using a simple IF formula w/o any problem for several weeks until now. The IF statement just calculates the amount of product from a meter every week. This simple formula compares the 1st of the week vs the last of the week readings and even if this meter rolls over this IF statement calculates out the correct amount of product. IF(Z15<Y15,100000000-Y15+Z15,Z15-Y15) Y=1st of week reading & Z=Last of week reading. The meter goes up to 99,999,999 units. Problem developed that caused the IF product results to be backwards. After noticing this I flipped ...

Significant Digits of Precision
1. I need to calculate using more digits of precision than 15. Using the apostrophe is useless for me. 2. I know that XL has 15 digits of precision. 3. I know that there are add-ins that can increase the digits of precision. 4. I have tried Xnumbers, and it does not work. Period. So my questions are: A. Do you know of any other add-ins that can increase the digits of precision that work with XL 2001? B. Do you know of any other PROGRAMS that I could use besides XL that have a higher number of digits of precision? I have considered MATLAB, but would prefer a cheaper alternative. ...

Count digits before decimals and after decimals
Dear expert, Would like to count the digits before decimals and after decimals. For example, 111975.35 should be 6 digits before decimals and 2 digits after decimals 12456.25 should be 5 and 2 2478.24 should be 4 and 2 248.37 should be 3 and 2 Using LEN does not help. Can you help please? Hi, Why doesn't LEN help. try these =LEN(INT(A1)) and for the decimal portion =IF(ISERROR(FIND(".",A1)),0,LEN(A1)-FIND(".",A1)) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while...

Significant digits
This may be in a FAQ somewhere, but I'm not even sure how to search for it. It's not really about significant digits, but the subject line's got to say something. If anyone's interested, see if you can reproduce the following results in Excel. I'm using Excel 2003 SP3. 1. In cell A1, type: =1.1-0.9 I should get a 0.2, and I do. 2. In cell A2, type: 0.2 (It doesn't really matter if you type the leading zero; it'll get added. The two cells should look identical, right?) 3. In cell B1, type: =IF(A1=A2,"Equal","No&quo...

Digital Signatures on referenced mda
I have an Access 2003 mdb that I have digitally signed. For the most part, my users have elected to accept my digital signature to avoid the Office security popup that shows up when you open Access, and this has worked well. I recently created an mda file that I intended to use as an add-in in my application. I couldn't figure out how to set it up so it could be added using the add-in wizard; so I added it to my application as a reference, which is working fine. This mda file is also digitally signed, and opens without the security message when I open it on its own. However, whe...

i can't open email with digital signature!
Hi sir, I cannot open the email with digital signature, and I got "Your digital ID name cannot be found by the underlying security system" error And the methods I have tried are follow: 1. Check the IE setting --does not work 2. Create a new profile--does not work 3. I have updated the 128-Bit Encryption Provider for Outlook 2000 SR-1.--does not work 4. Outlook safe mode--does not work 5. Try to open in OE--does not work, I can open but the body is empty. I am using Outlook 2000, Windows XP, Exchange account ( I am on the client side) Please give me some advise about this iss...

significance of WorkbookBeforeCloseEventHandler??????
hi all, i would like to know what is the exact usage of this method in c#? MICROSOFT.OFFICE.INTEROP.EXCEL.APPEVENTS_WORKBOOKBEFORECLOSEEVENTHANDLE thanks in advance -gop -- vgopinatha ----------------------------------------------------------------------- vgopinathan's Profile: http://www.officehelp.in/member.php?userid=511 View this thread: http://www.officehelp.in/showthread.php?t=126137 Posted from - http://www.officehelp.i To take your particular action before the workbook closes? -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "vg...

Digital ID
How can I obtain a FREE digital ID that works? On Wed, 12 Dec 2007 17:36:21 -0600, "Marc D. Curren" <currend@cox.net> wrote: Use selfcert.exe -Tom. >How can I obtain a FREE digital ID that works? ...

Significant figures #2
How do I get a number to be displayed in say 2 significant figures, all that I find in numbers formating is decimal places. Thanks. -- Turnipboy ------------------------------------------------------------------------ Turnipboy's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24527 View this thread: http://www.excelforum.com/showthread.php?threadid=496623 From an earlier reply by Jerry W. Lewis =ROUND(A1, A2 - 1 - INT(LOG10(ABS(A1)))) note that you may get non significant trailing zeros best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove ...

Significant figures #2
HELP Once again I am driving myself insane. We need to format cells in our worksheet to show significant figures. We need to post lab results using the correct number of significan figures. Ex. 0.1 must be displayed as 0.100 10 needs to be displayed as 10.0 105 as 105 1023 as 1020 10343 as 10300 165435 as 165000 I have written a complicated if then else statement for every cell. The statment works perfectly EXCEPT when a number is an exact number. The problem is that when some of the trailing digits are 0's they ge truncated. We always want numbers less than 1 to show 3 decimal ...

adding a leading 0 to only the numbers with 4 digits
I'm converting a bunch of data and during the import Excel dropped the leading zero from all the lip codes. Since this is about 15,000 records, I'd like a way to add a leading zero to only the zip codes with 4 characters. Any ideas? thanks Swamp, Format/Cells/Special/Zip Code Beege "SwampYankee" <johndillworth@gmail.com> wrote in message news:1147451549.294898.147230@v46g2000cwv.googlegroups.com... > I'm converting a bunch of data and during the import Excel dropped the > leading zero from all the lip codes. Since this is about 15,000 > records, I&...

How do I increase the of significant digits given in the slope i.
Does anyone know how to increase the number of significant digits given for the slope of a trendline in the trendline equation in Excel? Double click the trendline equation; from the Number tab select a format that lets you specify the number of decimal places. Alternatively, use the LINEST function to get the value in the worksheet directly. [In versions prior to 2003, for certain data sets, the chart trendline gave better results than LINEST.] -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In arti...