Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How to calculate/properly display significant figures ending in 0
To report numbers in significant figures, Excel Help gives the following
formula and states that it is valid for numbers above zero: =ROUND(B18,2-LEN(INT(B18))), where B18 is the number to round, and 2 is the the number of significant figures desired. In fact, it only works for numbers above 1, and if the number format is set to 'general' The formula works, but here's the problem: If the result ends in zero (e.g. 2.0), Excel only displays the last non-zero integer (e.g. 2). Since the last zero is significant, this is a problem. |
#2
|
|||
|
|||
How about this
=TEXT(B18,"##0"&IF(2-INTO(B18)0,"."&REPT("0",2-INT(B18)),"")) -- HTH RP (remove nothere from the email address if mailing direct) "A Zaffiro" <A wrote in message ... To report numbers in significant figures, Excel Help gives the following formula and states that it is valid for numbers above zero: =ROUND(B18,2-LEN(INT(B18))), where B18 is the number to round, and 2 is the the number of significant figures desired. In fact, it only works for numbers above 1, and if the number format is set to 'general' The formula works, but here's the problem: If the result ends in zero (e.g. 2.0), Excel only displays the last non-zero integer (e.g. 2). Since the last zero is significant, this is a problem. |
#3
|
|||
|
|||
If you format the cell with the formula as Number, you can set the number of decimal places to display. If you set it to 1, your listed formula will show 2.0 for a value of 2.015 (and will show 2000.0 for a value of 2015.15) HTH Bruce -- swatsp0p ------------------------------------------------------------------------ swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101 View this thread: http://www.excelforum.com/showthread...hreadid=382599 |
#4
|
|||
|
|||
Thanks. Inventive solution. That seems to work for numbers between 1 and
100. FYI: for numbers less than 1, I came up with: =ROUND(B3,LEN(INT(1/B3))+1), where B3 is the decimal number to round, and '1' is one less than the number of significant figures desired. This formula suffers from the same zero problem. I will attemp to resolve once I better understand the technique that you used to solve the zero problem for numbers greater than 1. "Bob Phillips" wrote: How about this =TEXT(B18,"##0"&IF(2-INTO(B18)0,"."&REPT("0",2-INT(B18)),"")) -- HTH RP (remove nothere from the email address if mailing direct) "A Zaffiro" <A wrote in message ... To report numbers in significant figures, Excel Help gives the following formula and states that it is valid for numbers above zero: =ROUND(B18,2-LEN(INT(B18))), where B18 is the number to round, and 2 is the the number of significant figures desired. In fact, it only works for numbers above 1, and if the number format is set to 'general' The formula works, but here's the problem: If the result ends in zero (e.g. 2.0), Excel only displays the last non-zero integer (e.g. 2). Since the last zero is significant, this is a problem. |
#5
|
|||
|
|||
A more general formula for rounding to a specified number of significant
figures is given in http://groups-beta.google.com/group/...244c8f41e91025 but that does not address the question of general display of trailing zeros. Jerry A Zaffiro wrote: To report numbers in significant figures, Excel Help gives the following formula and states that it is valid for numbers above zero: =ROUND(B18,2-LEN(INT(B18))), where B18 is the number to round, and 2 is the the number of significant figures desired. In fact, it only works for numbers above 1, and if the number format is set to 'general' The formula works, but here's the problem: If the result ends in zero (e.g. 2.0), Excel only displays the last non-zero integer (e.g. 2). Since the last zero is significant, this is a problem. |
#6
|
|||
|
|||
Thanks Jerry. I used that plus some of Bob Phillip's ideas to create a
formula that solves the trailing zero display problem for 2 significant figures and works for any number above zero including decimals. It gets rather involved to write a general fomula that allows the number of sig fig's to vary beyond 2. I'll share the formula after I have a chance to polish and test thoroughly. Alan Zaffiro "Jerry W. Lewis" wrote: A more general formula for rounding to a specified number of significant figures is given in http://groups-beta.google.com/group/...244c8f41e91025 but that does not address the question of general display of trailing zeros. Jerry A Zaffiro wrote: To report numbers in significant figures, Excel Help gives the following formula and states that it is valid for numbers above zero: =ROUND(B18,2-LEN(INT(B18))), where B18 is the number to round, and 2 is the the number of significant figures desired. In fact, it only works for numbers above 1, and if the number format is set to 'general' The formula works, but here's the problem: If the result ends in zero (e.g. 2.0), Excel only displays the last non-zero integer (e.g. 2). Since the last zero is significant, this is a problem. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
large text amount in cell will not display in the cell | Excel Discussion (Misc queries) | |||
Need Workaround for Cell Display Limitation in Excel 2000 | Excel Discussion (Misc queries) | |||
Significant figures (not decimal places) | Excel Worksheet Functions | |||
Conditional display of a .jpeg file? | Excel Discussion (Misc queries) | |||
Combine & Display “Fixed” & “Automatically Updated” Date Parts | Excel Worksheet Functions |