|
|
| Line 1: |
Line 1: |
| As with other spreadsheets, [[Microsoft Excel]] works only to limited accuracy because it retains only a certain number of figures to describe numbers (it has limited [[Arithmetic precision|precision]]). Excel nominally works with [[byte|8-byte]] numbers by default, a modified 1985 version of the [[IEEE 754-2008|IEEE 754 specification]]<ref name=microsoft_spec>
| | You need to read a newspaper regularly, you will see the how professional write their essay. 2- Does racial discrimination be claimed as a legal sin. There are many starters that miss the kind of ability it s essential submit good essays that could represent their own undertaking impressively and convincingly to trainers. It is a journey that can begin only after a person realizes the need to use time more efficiently. s students are taught that how to write an effective essay but the problem is this that when they reach to the final stage I mean to say that when they are up to the age of writing an essay then the problem comes that they are not enough capable to write it more perfectly as they want to get the perfect marks. <br><br>Try to get hold of the latest information which will enhance the quality of your essay by incorporating the modern development(s) on the subject. Students who receive free essay writing for the company always send requests for other services in custom writing that the company offers. As a result many consider obtaining the assistance of an essay writing service to get them through some of the assignment work load. Now, those thoughts are very important, but also a bit generic and can be applied to any admissions essay. overloaded with so much work that he cannot get time to write any academic writing. <br><br>Think about how you are going to complete the task whilst simultaneously maximising the available marks in each section. Reliable Legitimate Philosophy Essays Writing philosophy essays requires someone who is trained and has experience in the topic that has been selected. Essay writing in the university will enable students to develop research techniques. Students can think of taking help from their friends or some family member, to assist them in their writing assignments. To assist you in your work, this brief article will show you how to write an essay. <br><br>Our custom analytical essay will provide you with enough time to attend to other duties. The followings describe what topics you are ready to obtain when you're looking to buy essay paper. If you cherished this posting and you would like to get extra information relating to [http://www.lvlywallpapers.com/profile/emoua do my essay free] kindly check out our web-site. Keep a tab on your sources of information so you can access it later. The clients feedback ensures that the writers conducting the academic custom essay writing have eliminated all the errors. Selection of catchy and precise title will definitely improve the quality of essay. <br><br>And a great essay might just be what turns a 'maybe' into an 'admit. Dear customers, whatever we provide are authentic and this is our guarantee that each and every single paper is an original. Expository essay- An expository essay is an explanatory essay that consists of a description of some subject. Soon after writing the plan, you need to head to the library to find the books from the reading list or search for journal articles on the internet. University essay writing is essential to all students pursuing different courses in universities. |
| | |
| {{cite web |url=http://support.microsoft.com/kb/78113/en-us |title=Floating-point arithmetic may give inaccurate results in Excel |publisher=Microsoft support |work=Revision 8.2 ; article ID: 78113 |date=June 30, 2010 |accessdate=2010-07-02}}
| |
| | |
| </ref> (Besides numbers, Excel uses a few other data types.<ref name=Dalton>
| |
| | |
| {{cite book |title=Financial Applications Using Excel Add-in Development in C/C++
| |
| |author=Steve Dalton |chapter=Table 2.3: Worksheet data types and limits |pages=13–14 |isbn=0-470-02797-5 |edition=2nd |publisher=Wiley |year=2007 |url=http://books.google.com/books?id=ABUSU9PWUuIC&pg=PA13}}
| |
| | |
| </ref>) Although Excel can display 30 decimal points, its precision for a specified number is confined to 15 [[significant figures]], and calculations may have an accuracy that is even less due to three issues: [[round-off error|round off]],<ref name=roundoff>
| |
| | |
| Round-off is the loss of accuracy when numbers that differ by small amounts are subtracted. Because each number has only fifteen significant digits, their difference is inaccurate when there aren't enough significant digits to express the difference.
| |
| | |
| </ref> [[truncation]], and [[Binary numeral system|binary storage]].
| |
| | |
| ==Accuracy and binary storage==
| |
| [[File:Excel fifteen figure.PNG|thumb|390px|Excel maintains 15 figures in its numbers, but they are not always accurate: the bottom line should be the same as the top line.]]
| |
| [[File:Excel errors.PNG|thumb|390px|Of course, 1 + x − 1 = x. The discrepancy indicates the error. All errors but the last are beyond the 15-th decimal.]]
| |
| In the top figure the fraction 1/9000 in Excel is displayed. Although this number has a decimal representation that is an infinite string of ones, Excel displays only the leading 15 figures. In the second line, the number one is added to the fraction, and again Excel displays only 15 figures. In the third line, one is subtracted from the sum using Excel. Because the sum has only eleven 1's after the decimal, the true difference when ‘1’ is subtracted is three 0's followed by a string of eleven 1's. However, the difference reported by Excel is three 0's followed by a 15-digit string of ''thirteen'' 1's and two extra erroneous digits. Thus, the numbers Excel calculates with are ''not'' the numbers that it displays. Moreover, the error in Excel's answer is not just round-off error. How was this answer obtained?
| |
| | |
| The inaccuracy in Excel calculations is more complicated than errors due to a precision of 15 significant figures. Excel's storage of numbers in binary format also affects its accuracy.<ref name=deLevie>
| |
| | |
| {{cite book |title=Advanced Excel for scientific data analysis |publisher=Oxford University Press |author=Robert de Levie |year=2004 |isbn=0-19-515275-1 |page=44 |chapter=Algorithmic accuracy |url=http://www.amazon.com/Advanced-Excel-Scientific-Data-Analysis/dp/0195152751/ref=sr_1_1?ie=UTF8&s=books&qid=1270770876&sr=1-1#reader_0195152751}}
| |
| | |
| </ref> To illustrate, the lower figure tabulates the simple addition {{nowrap|1 + ''x'' − 1}} for several values of ''x''. All the values of ''x'' begin at the 15-th decimal, so Excel must take them into account. Before calculating the sum 1 + ''x'', Excel first approximates ''x'' as a binary number. If this binary version of ''x'' is a simple power of 2, the 15-digit decimal approximation to ''x'' is stored in the sum, and the top two examples of the figure indicate recovery of ''x'' without error. In the third example, ''x'' is a more complicated binary number, ''x'' = 1.110111⋯111 × 2<sup>−49</sup> (15 bits altogether). Here ''x'' is approximated by the 4-bit binary 1.111 × 2<sup>−49</sup> (some insight into this approximation can be found using [[geometric progression]]: ''x'' = 1.11 × 2<sup>−49</sup> + 2<sup>−52</sup> × (1 − 2<sup>−11</sup>) ≈ 1.11 × 2<sup>−49</sup> + 2<sup>−52</sup> = 1.111 × 2<sup>−49</sup> ) and the decimal equivalent of this crude 4-bit approximation is used. In the fourth example, ''x'' is a ''decimal'' number not equivalent to a simple binary (although it agrees with the binary of the third example to the precision displayed). The decimal input is approximated by a binary and then ''that'' decimal is used. These two middle examples in the figure show that some error is introduced. | |
| | |
| The last two examples illustrate what happens if ''x'' is a rather small number. In the second from last example, ''x'' = 1.110111⋯111 × 2<sup>−50</sup>; 15 bits altogether. the binary is replaced very crudely by a single power of 2 (in this example, 2<sup>−49</sup>) and its decimal equivalent is used. In the bottom example, a decimal identical with the binary above to the precision shown, is nonetheless approximated differently than the binary, and is eliminated by truncation to 15 significant figures, making no contribution to {{nowrap|1 + ''x'' − 1}}, leading to ''x'' = 0.<ref name=decimal_input>
| |
| | |
| To input a number as binary, the number is submitted as a string of powers of 2: 2^(−50)*(2^0 + 2^−1 + ⋯). To input a number as decimal, the decimal number is typed in directly.
| |
| | |
| </ref>
| |
| | |
| For ''x''′s that are not simple powers of 2, a noticeable error in {{nowrap|1 + ''x'' − 1}} can occur even when ''x'' is quite large. For example, if ''x'' = 1/1000, then {{nowrap|1 + ''x'' − 1}} = 9.99999999999'''''89''''' × 10<sup>−4</sup>, an error in the 13 significant figure. In this case, if Excel simply added and subtracted the decimal numbers, avoiding the conversion to binary and back again to decimal, no round-off error would occur and accuracy actually would be better. Excel has the option to "Set precision as displayed".<ref name= discuss>This option is found on the "Excel options/Advanced" tab. See [http://support.microsoft.com/kb/214118 How to correct rounding errors: Method 2]
| |
| | |
| </ref> With this option, depending upon circumstance, accuracy may turn out to be better or worse, but you will know exactly what Excel is doing. (It should be noted, however, that only the selected precision is retained, and one cannot recover extra digits by reversing this option.) Some similar examples can be found at this link.<ref name =arithmetic>
| |
| | |
| [http://news.office-watch.com/t/n.aspx?a=612&z=9 Excel addition strangeness]
| |
| | |
| </ref> | |
| | |
| In short, a variety of accuracy behavior is introduced by the combination of representing a number with a limited number of binary digits, along with [[Truncation error|truncating]] numbers beyond the fifteenth significant figure.<ref name=deLevie3>
| |
| | |
| {{cite book |title=cited work |author=Robert de Levie |year=2004 |isbn=0-19-515275-1 |pages=45–46 |url=http://www.amazon.com/Advanced-Excel-Scientific-Data-Analysis/dp/0195152751/ref=sr_1_1?ie=UTF8&s=books&qid=1270770876&sr=1-1#reader_0195152751}}
| |
| | |
| </ref><!-- These figures are simply screen shots of the listed arithmetic using Excel 2007 --> Excel's treatment of numbers beyond 15 significant figures sometimes contributes better accuracy to the final few significant figures of a computation than working directly with only 15 significant figures, and sometimes not.
| |
| | |
| For the reasoning behind the conversion to binary representation and back to decimal, and for more detail about accuracy in Excel and VBA consult these links.<ref name=accuracy_links>
| |
| Accuracy in Excel:
| |
| *[http://support.microsoft.com/kb/78113/en-us Floating point arithmetic may give inaccurate results]: A detailed explanation with examples of the binary/15 sig fig storage consequences.
| |
| *[http://blogs.msdn.com/excel/archive/2008/04/10/understanding-floating-point-precision-aka-why-does-excel-give-me-seemingly-wrong-answers.aspx Why does Excel seem to give wrong answers?]: Another detailed discussion with examples and some fixes.
| |
| *[http://docs.sun.com/source/806-3568/ncg_goldberg.html What every computer scientist should know about floating point] Focuses upon examples of floating point representations of numbers.
| |
| *[http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q279/7/55.ASP&NoWebContent=1 Visual basic and arithmetic precision]: Oriented toward VBA, which does things a bit differently.
| |
| *{{cite book |title=A guide to Microsoft Excel 2007 for scientists and engineers |author=Bernard V. Liengme |chapter=Mathematical limitations of Excel |page=31 ''ff'' |url=http://books.google.com/books?id=0qDm7uuDmv0C&pg=PA31 |isbn=0-12-374623-X |year=2008 |publisher=Academic Press}}
| |
| | |
| </ref>
| |
| | |
| ==Examples where precision is no indicator of accuracy==
| |
| {{Expand section|date=April 2010}}
| |
| | |
| ===Statistical functions===
| |
| [[File:Excel Std Dev Error.PNG|thumb|450px|Error in Excel 2007 calculation of standard deviation. All four columns have the same deviation of 0.5]]
| |
| | |
| Accuracy in Excel-provided functions can be an issue. [[Micah Altman]] ''et al.'' provide this example:<ref name=Altman>
| |
| | |
| {{cite book |title=Numerical issues in statistical computing for the social scientist |author= Micah Altman, Jeff Gill, Michael McDonald |year=2004 |publisher=Wiley-IEEE |isbn=0-471-23633-0 |url=http://books.google.com/books?id=j_KevqVO3zAC&pg=PA12 |chapter=§2.1.1 Revealing example: Computing the coefficient standard deviation |page=12}}
| |
| | |
| </ref> The population standard deviation given by:
| |
| | |
| :<math> \sqrt{ \frac{ \Sigma (x - \bar{x})^2 }{n} } = \sqrt{ \frac{ \Sigma \left[ x - \left( \Sigma x \right) /n \right] ^2}{n} } \ , </math>
| |
| | |
| is mathematically equivalent to:
| |
| | |
| :<math>\sqrt{ \frac{ n\Sigma x^2 - \left( \Sigma x \right) ^2 }{n^2} } \ . </math>
| |
| | |
| However, the first form keeps better numerical accuracy for large values of ''x'', because squares of differences between ''x'' and ''x''<sub>av</sub> leads to less round-off than the differences between the much larger numbers Σx<sup>2</sup> and (Σx)<sup>2</sup>. The built-in Excel function STDEVP(), however, uses the less accurate formulation because it is faster computationally.<ref name=Levie>
| |
| | |
| {{cite book |title=Advanced Excel for scientific data analysis |author=Robert de Levie |publisher=Oxford University Press |year=2004 |isbn=0-19-515275-1 |url=http://books.google.com/books?id=IAnO-2qVazsC&printsec=frontcove|pages=45–46}}
| |
| | |
| </ref>
| |
| | |
| Both the "compatibility" function STDEVP and the "consistency" function STDEV.P in Excel 2010 return the 0.5 population standard deviation for the given set of values. However, numerical accuracy can still be shown using this example by extending the existing figure to include 10+15, where the standard deviation will equal zero in Excel 2010.
| |
| | |
| ===Subtraction of Subtraction Results===
| |
| Doing simple subtractions may lead to errors as two cells may display the same numeric value while storing two separate values.
| |
| An example of this occurs in an sheet where the following cells are set to the following numeric values:
| |
| :<math>A1:= 28.552</math>
| |
| :<math>A2:= 27.399</math>
| |
| :<math>A3:= 26.246</math>
| |
| and the following cells contain the following formulas
| |
| :<math>B1: = A1 - A2</math>
| |
| :<math>B2: = A2 - A3</math>
| |
| Both cells <math>B1</math> and <math>B2</math> display <math>1.1530</math>.
| |
| However, if cell <math>C1</math> contains the formula <math>B1-B2</math>
| |
| then <math>C1</math> does not display <math>0</math> as would be expected,
| |
| but displays <math>-3.55271E-15</math> instead.
| |
| | |
| ===Round-off error===
| |
| User computations must be carefully organized to ensure round-off error does not become an issue. An example occurs in solving a [[quadratic equation]]:
| |
| :<math>ax^2 + b x +c = 0 \ .</math>
| |
| The solutions (the roots) of this equation are exactly determined by the [[quadratic formula]]: | |
| | |
| :<math>x= \frac{-b \pm \sqrt{b^2-4ac} }{2a}. </math>
| |
| | |
| When one of these roots is very large compared to the other, that is, when the square root is close to the value ''b'', the evaluation of the root corresponding to subtraction of the two terms becomes very inaccurate due to round-off.
| |
| | |
| It is possible to determine the round-off error by using the [[Taylor series]] formula for the square root:<ref name=Ryzhik>
| |
| | |
| {{cite book |chapter=§1.112 Power series |title=Table of integrals, series and products |author=IS Gradshteyn & IM Ryzhik |edition =7th |publisher=Academic Press |year=2007 |isbn=0-12-373637-4 |page = 25 |url=http://books.google.com/books?id=aBgFYxKHUjsC&pg=PA25}}
| |
| | |
| </ref>
| |
| :<math>\sqrt{b^2-4ac} = b \ \sqrt{1-\frac{4ac}{b^2}} \approx b \left( 1 -\frac{2ac}{b^2} + \frac{2 a^2 c^2 }{b^4} + \cdots \right ). </math>
| |
| | |
| Consequently,
| |
| :<math> b - \sqrt{b^2-4ac} \approx b \left ( \frac{2ac}{b^2} - \frac{2 a^2 c^2 }{b^4} + \cdots \right ), </math>
| |
| indicating that, as ''b'' becomes larger, the first surviving term, say ε:
| |
| | |
| :<math> \varepsilon = \frac{2ac}{b}, </math>
| |
| | |
| becomes smaller and smaller. The numbers for ''b'' and the square root become nearly the same, and the difference becomes small:
| |
| | |
| :<math>b - \sqrt{b^2-4ac} \approx b - b + \varepsilon. </math>
| |
| | |
| Under these circumstances, all the significant figures go into expressing ''b''. For example, if the precision is 15 figures, and these two numbers, ''b'' and the square root, are the same to 15 figures, the difference will be zero instead of the difference ε.
| |
| | |
| A better accuracy can be obtained from a different approach, outlined below.<ref name=Step_response>
| |
| | |
| This approximate method is used often in the design of feedback amplifiers, where the two roots represent the response times of the system. See the article on [[step response]].
| |
| | |
| </ref> If we denote the two roots by ''r'' <sub>1</sub> and ''r'' <sub>2</sub>, the quadratic equation can be written:
| |
| | |
| :<math>\left(x - r_1\right) \left( x - r_2 \right) = x^2 - \left( r_1 + r_2 \right) x + r_1 \ r_2 = 0. </math>
| |
| | |
| When the root ''r'' <sub>1</sub> >> ''r'' <sub>2</sub>, the sum (''r'' <sub>1</sub> + ''r'' <sub>2</sub> ) ≈ ''r'' <sub>1</sub> and comparison of the two forms shows approximately:
| |
| :<math> r_1 \approx -\frac{b}{a}, </math>
| |
| while
| |
| :<math> r_1 \ r_2 = \frac{c}{a}. </math>
| |
| Thus, we find the approximate form:
| |
| :<math>r_2 = \frac {c}{a \ r_1} \approx -\frac {c}{b}. </math>
| |
| These results are not subject to round-off error, but they are not accurate unless ''b'' <sup>2</sup> is large compared to ''ac''.
| |
| | |
| [[File:Excel quadratic error.PNG|thumb |350px| Excel graph of the difference between two evaluations of the smallest root of a quadratic: direct evaluation using the quadratic formula (accurate at smaller ''b'') and an approximation for widely spaced roots (accurate for larger ''b''). The difference reaches a minimum at the large dots, and round-off causes squiggles in the curves beyond this minimum.]]
| |
| | |
| The bottom line is that in doing this calculation using Excel, as the roots become farther apart in value, the method of calculation will have to switch from direct evaluation of the quadratic formula to some other method so as to limit round-off error. The point to switch methods varies according to the size of coefficients ''a'' and ''b''.
| |
| | |
| In the figure, Excel is used to find the smallest root of the quadratic equation ''x''<sup>2</sup> + ''bx'' + ''c'' = 0 for ''c'' = 4 and ''c'' = 4 × 10<sup>5</sup>. The difference between direct evaluation using the quadratic formula and the approximation described above for widely spaced roots is plotted ''vs.'' ''b''. Initially the difference between the methods declines because the widely spaced root method becomes more accurate at larger ''b''-values. However, beyond some ''b''-value the difference increases because the quadratic formula (good for smaller ''b''-values) becomes worse due to round-off, while the widely spaced root method (good for large ''b''-values) continues to improve. The point to switch methods is indicated by large dots, and is larger for larger ''c'' -values. At large ''b''-values, the upward sloping curve is Excel's round-off error in the quadratic formula, whose erratic behavior causes the curves to squiggle.
| |
| | |
| A different field where accuracy is an issue is the area of [[Numerical integration|numerical computing of integrals]] and the [[Numerical ordinary differential equations|solution of differential equations]]. Examples are [[Simpson's rule]], the [[Runge–Kutta method]], and the Numerov algorithm for the [[Schrödinger equation]].<ref name=Blom>
| |
| | |
| [http://www.teorfys.lu.se/personal/Anders.Blom/useful/scr.pdf Anders Blom] ''Computer algorithms for solving the Schrödinger and Poisson equations'', Department of Physics, Lund University, 2002.
| |
| | |
| </ref> Using Visual Basic for Applications, any of these methods can be implemented in Excel. Numerical methods use a grid where functions are evaluated. The functions may be interpolated between grid points or extrapolated to locate adjacent grid points. These formulas involve comparisons of adjacent values. If the grid is spaced very finely, round-off error will occur, and the less the precision used, the worse the round-off error. If spaced widely, accuracy will suffer. If the numerical procedure is thought of as a [[Negative feedback amplifier|feedback system]], this calculation noise may be viewed as a signal that is applied to the system, which will lead to instability unless the system is carefully designed.<ref name=Hamming>
| |
| | |
| {{cite book |author=[[Richard Hamming|R. W. Hamming]] |title=Numerical Methods for Scientists and Engineers |year= 1986 |isbn=0-486-65241-6 |url=http://books.google.com/books?id=Y3YSCmWBVwoC&printsec=frontcover |publisher=Courier Dover Publications |edition=2nd}} This book discusses round-off, truncation and stability extensively. For example, see Chapter 21: [http://books.google.com/books?id=Y3YSCmWBVwoC&pg=PA357 Indefinite integrals – feedback], page 357.
| |
| | |
| </ref>
| |
| | |
| ===Accuracy within VBA===
| |
| Although Excel nominally works with [[byte|8-byte]] numbers by default, [[Visual basic for applications|VBA]] has a variety of data types. The ''Double'' data type is 8 bytes, the ''Integer'' data type is 2 bytes, and the general purpose 16 byte ''Variant'' data type can be converted to a 12 byte ''Decimal'' data type using the VBA conversion function ''CDec''.<ref name=John_Walkenbach>
| |
| | |
| {{cite book |title=Excel 2010 Power Programming with VBA |chapter=Defining data types |pages=198 ''ff'' and Table 8-1|isbn=0-470-47535-8 |author=John Walkenbach |year=2010 |publisher=Wiley |url=http://books.google.com/books?id=dtSdrjjVXrwC&pg=PA198}}
| |
| | |
| </ref> Choice of variable types in a VBA calculation involves consideration of storage requirements, accuracy and speed.
| |
| | |
| ==References==
| |
| <references/>
| |
| | |
| [[Category:Microsoft software]]
| |
| [[Category:Numerical analysis]]
| |
| [[Category:Spreadsheet software]]
| |