Exponentiel "fill" option decimals??? 3

[nfpk]

Hi!
Anyone knows why these decimals comes when I start an exponentiel serie with 1 and 2
Choose from the alternative
fill serie
trend

This is what I get:

0,999999999999999
2,000000000000000
4,000000000000000
8,000000000000010
16,000000000000000
32,000000000000100
64,000000000000100
128,000000000000000
256,000000000001000
512,000000000002000

Why does excel work like this?

 

[bandit600]

nfpk,

It’s to do with computers storing everything in binary.

In very simple terms, everything we see on screen is only a representation. To work on numbers, or anything else, the computer has to convert everything to binary, then do its calculations and then convert back to the representation that we want to see.

For better explanations than I can provide, do a web search on floating point inaccuracy.

bandit600

 

[PepeLeMokko]

If you want toget rid of the problem, use the round function at each step.
So a=rnd(a(n-1),2)exp2

 

[KenWright]

A nice comprehensive answer posted to the Excel newsgroups by Chip Pearson:-

As you have noticed Excel handles only 15 digits of precision. The reason is that Excel, like many other computer programs, uses the IEEE (Institute of Electrical and Electronic Engineers) Double Precision Floating Point number format as the most accurate representation of a number. You can read more about this at

http://www.cpearson.com/excel/rounding.htm

, but in an oversimplified form, it stores numbers as

N = Integer + X*(1/2) + X*(1/4) + X*(1/8) + X*(1/16) +.....+
X*(1/2^51)

where each X is either 1 or 0. In binary format, there are 51 digits to the right of the decimal point. In decimal form, 2^51 is about equal to 10^15, which is why you get approximately 15 digits of precision.

Unless a fractional number can be expressed *exactly* as the sum of 1/2 + 1/4 + 1/8 + ... + 1/(2^51) it will be stored as an approximation. This is not unique to computers. Using a finite number of decimal places, you cannot accurately store the number 1/3. You can store it as an approximation, like 0.3 or 0.33 or 0.33333333333333 but at some point you're rounding the true value 1/3, and 0.33333333333...+0.33333333333...+0.33333333333... does
NOT equal 1. It equal 0.999999999999...... which is decidedly not 1.

This is a fact of life in computers and in the real world, and in the realm in which the two coincide.

> But what about the rest of the decimal places, and how, > if at all, can I achieve more precision?

You can *display* a number to as many decimal places as you want, but anything past 15 is no man's land. Within Excel there is no way to achieve additional precision. Errors in rounding can compound, so that rounding error in one formula is compounded when the rounded error is used by other formulas, which themselves round.

Some computer programs use other representations of numbers, but these programs trade performance and compatibility for precision. Additional precision comes at the cost of performance and compatibility with other programs. For example, a program that stored numbers to 100 digits of precision would use a different encoding scheme, and its data would not be compatible with the
majority of computer programs. The IEEE Double Precision standard provides a universal format that is "good enough" for the vast majority of uses. Not all, but most. For good reason, MS chose years ago to use IEEE Doubles for Excel.

> Can you recommend a non-Excel app that offers higher
> precision?

Dedicated mathematical programs like Matlab and Mathamatica can provide much greater precision, but those results aren't compatible with most other computer programs.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC

http://www.cpearson.com

chip@cpearson.com


------------------------------------------------------

For a calculator that will support more than 15 digits, Jerry W. Lewis has given the following info and link:-


The decimal data type gives 28 figure if you don't need exponents and don't mind VBA programing. I think the Windows calculator uses the same data type.

A free quad precision (64 digit) calculator can be downloaded from

http://www.crbond.com/applications.htm

unless it has been updated, it does not support cut/copy/paste.

I think some extended precision routines using VBA and strings have been published for Excel - search the Google archives.

In Maple, Mathematica, Matlab, Rexx, etc., you can specify the number of output figures you want.


Jerry W. Lewis

--------------------------------------------------------

Hope this helps.

Regards
Ken..........

 

[nfpk]

Ken
Thank for this useful information.
nfpk

 

[KenWright]

My Pleasure

Regards
Ken.........