How To Change The Number Of Significant Figures In Excel

Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a afterwards version (Excel 2007 or later), this tip may non work for you. For a version of this tip written specifically for later versions of Excel, click here: Thoughts and Ideas on Pregnant Digits in Excel.

Thoughts and Ideas on Significant Digits in Excel

Steve Aprahamian contributed some slap-up observations and thoughts on the topic of significant digits and working in Excel. Steve works with numbers and their statistical variability as a large role of his profession. (He is a Ph.D. chemist.) His thoughts are presented in this tip and should be helpful to many readers.

Information technology does not matter how many significant figures you utilize in displaying values in Excel, every bit the program will always use xv significant digits in its calculation. You can ROUND the values (changing the displayed decimal places does not change the value used in the adding, Circular does), but the accepted practice has e'er been to include all the figures you lot accept for all your calculations and ROUND at the end to the advisable number of meaning digits. This really improves the precision of your calculations.

The easiest thing to practice is to ignore significant digits and only brandish all final calculations with ii or three decimal places. Fewer does not provide enough information, while more gives people the impression that you lot are anal-retentive, pedantic, or ignorant in the significant of pregnant figures! (I once had a higher instructor who deducted points if you lot did not use significant digits. He hated "reckoner diarrhea." If the problem, as stated, resulted in an implied 1.23 result, answering 1.2345678901—the result displayed past the calculator—was considered wrong and points were deducted.)

If you want to go along rail of significant digits, the easiest way is to keep a value of significant digits for each number yous have. With a value and the number of significant digits, Excel tin can summate the number of decimal places and you can display your final value with this information. Pregnant digits and the number of decimal places are not independent; the decimal places come from the significant digits and the value.

Given a value (X) and the number of significant digits (SD), the number of decimal places is given past the post-obit formula:

=SD - INT(LOG10(X)) - 1        

You can then convert the appropriate value to text by using:

=Stock-still(X, SD - INT(LOG10(X)) - 1)        

Circular will not piece of work, since you must also alter the number of fixed decimal places in the display. The VALUE part can be used with the "FIXED text" to convert the text to a numeric value for calculations.

Keeping rail of meaning digits is probably going to be a real pain, however. It is important to be aware of the following of a couple of things. Outset, when you multiply or divide you lot must pay attention to significant digits. The number of pregnant digits in the effect is the same as the minimum number of significant digits for each of the operands. For instance, consider the following equation:

iii.1 * three.45678        

The answer to this equation would only accept two significant digits, considering one operand has two and the other has six, and the result volition have the minimum of those ii. Thus, the answer to the equation would exist 11, which has two significant digits. Notation, every bit well, that even though both operands had at to the lowest degree i decimal place, the result does not, as that would requite the result too many meaning digits. Decimal places are not considered in the consequence.

2nd, when y'all add or subtract, you lot must pay attention to decimal places, non to pregnant digits. The number of decimal places in the upshot is the same as the minimum number of decimal places for each of the operands. For case, consider the following equation:

23.1 + 103.789120        

The reply to this equation would accept 1 decimal identify, because one operand has one decimal identify and the other has six, and the result will have the minimum of those two. Thus, the reply to the equation would be 127.ix, which has a single decimal place. The number of significant digits in the operands is non considered in the case of addition and subtraction.

Information technology can be noted that an even better way to reach the command of significance is to use propagation of error techniques. You can keep track of the variability (variance, standard deviation, or some other conclusion of "error") and actually decide the variability of the final result. You lot would report a value like one.23 +/- 0.05 or i,234 +/- 45. This method gives a ameliorate determination of what the actual significant digits are. This arroyo, of course, is all-time left to a separate discussion.

Without an explicit variability, significant digits, for the most part, only imply the variability in the value. The implication is that the number is +/- half of the last pregnant digit. For case, a result of 1.23 suggests (with no other information) an actual value betwixt i.225 and 1.235, while a issue of 12 suggests an actual between xi.5 and 12.5.

Some additional comments on meaning digits:

• All digits one-9 are significant. (12 has two significant digits, 1.234 has 4 meaning digits, and one,234.v has five significant digits.)
• The digit 0 is significant when it is betwixt two significant digits. (102 has three pregnant digits and 1.0204 has five significant digits.)
• The digit 0 actualization on the left of a number is only a placeholder and is not significant. (0.0123 has three significant digits and 0.0000000000000000005 has simply one significant digit.)
• The digit 0 appearing on the right of a number may or may not exist a pregnant digit and must be defined by the user. (one.2300 has five significant digits; 1.20 has iii significant digits; and 100 could have one, ii, or 3 meaning digits—the number alone is not enough to make a conclusion.)
• Constants have an space number of meaning digits and should never determine the significance of the final result. For instance, if you double something you lot are multiplying it by two.00000000000...(space); you lot are not multiplying information technology by ii, which would limit the outcome to 1 significant digit. Also, when you apply pi (a constant) in a calculation, y'all use more than pregnant digits for pi than the significant digits used in other operands in the equation.

ExcelTips is your source for toll-effective Microsoft Excel training. This tip (1983) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can notice a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Thoughts and Ideas on Significant Digits in Excel.

Author Bio

With more l not-fiction books and numerous mag manufactures to his credit, Allen Wyatt is an internationally recognized writer. He is president of Sharon Parq Assembly, a reckoner and publishing services company. Larn more almost Allen...

More than FROM ALLEN

Marking Multiple Documents

Later using Word for a while, it is easy to accumulate quite a few documents. At some point you lot may want to make a change ...

Discover More

Shortcut for Selecting a Information Range

Want to select all the data in a contiguous section of a worksheet? The shortcut discussed in this tip makes it very like shooting fish in a barrel.

Observe More

Converting Tables to Text

Need to convert all the tables in your certificate into plain text? This tip provides a macro that can brand quick work of a ...

Discover More than

Source: https://excel.tips.net/T001983_Thoughts_and_Ideas_on_Significant_Digits_in_Excel.html

Posted by: johnsonnoned1998.blogspot.com

Share this post