Significant Digits
Significant Digits
(OP)
Is there a way to have excel only display a certain number of significant digits?
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS Come Join Us!Are you an
Engineering professional? Join EngTips Forums!
*EngTips's functionality depends on members receiving email. By joining you are opting in to receive email. Posting Guidelines 

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.
Here's Why Members Love EngTips Forums:
Register now while it's still free!
Already a member? Close this window and log in.
RE: Significant Digits
handleman, CSWP (The new, easy test)
RE: Significant Digits
RE: Significant Digits
RE: Significant Digits
3.14e0
1.00e2
As far as I know it's the only way.
handleman, CSWP (The new, easy test)
RE: Significant Digits
You can also use some custom formats. For example, format a cell as "0.00xx". Entering the value "3.14" would result in a displayed value of "3.14". But entering the formula "=pi()" would result with displayed value "3.1416".
Sometimes a "hand check" of calculations from Excel would produce different results than if punching in the displayed values in a hand calculator. This can be eliminated by using the "precision as displayed" option in Tools/Options/Calculation. Or have some calculated cells (formulas) use the "round" function as part of the formula.
For example, in lieu of formula "=pi()", try "=round(pi(),4)".
RE: Significant Digits
That works for sig figs after the decimal point. The only way I know of to display 1,253,597 as 1,250,000 is to use scientific notation, as 1.25e6.
handleman, CSWP (The new, easy test)
RE: Significant Digits
RE: Significant Digits
If you want to truncate the number of decimal points, use ROUND(), FLOOR() or CEILING().
Note that round does not "pad" a number by adding significant digits. That is:
ROUND(1.2345,2) = 1.23
ROUND(1,2) = 1
RE: Significant Digits
The question is if the conditional format would work.
Here is a table where I used the iff function to determine rounding:
1.235670 1.236
13.987650 13.99
250.978562 251
3007.196270 3007
where:
=IF(D5<1,ROUND(D5,4),IF(D5<10,ROUND(D5,3),IF(D5<100,ROUND(D5,2),IF(D5<1000,ROUND(D5,1),ROUND(D5,0)))))
JMW
www.ViscoAnalyser.com
RE: Significant Digits
RE: Significant Digits
0.912323 0.912323 0.9123 1 0.9123
9.126535 9.126535 9.127 9 9.127
92.976542 92.976542 92.98 93 92.98
954.761230 954.761230 954.8 955 954.8
9982.175633 9982.175633 9982 9982 9982
98989.127452 9898.912745 98989 98990 98990
979861.132560 9798.611326 979861 979900 979900
Left hand column are the original numbers, middle 3 columns (hide columns) are working functions and the right hand column is the final solution with 4 significant figures.
Second column:
=IF(D5<10000,D5,IF(D5<100000,D5/10,IF(D5<1000000,D5/100,D5/1000)))
Third column:
=IF(D5<1,ROUND(D5,4),IF(D5<10,ROUND(D5,3),IF(D5<100,ROUND(D5,2),IF(D5<1000,ROUND(D5,1),ROUND(D5,0)))))
Fourth Column:
=IF(D5<10000,ROUND(E5,0),IF(D5<100000,ROUND(E5,0)*10,IF(D5<1000000,ROUND(E5,0)*100,ROUND(E5,0)*1000)))
Final column:
=IF(D5<10000,F5,G5)
JMW
www.ViscoAnalyser.com
RE: Significant Digits
=SIGFIG(number_to_be_rounded, Number_sig_digits)
If the format of the cell that this formula is entered is 'number  general', this works well for rounding, but it does not pad 00s when a value is precisely ##.0000.
No guarantees, but this has met my needs in the past and might work for you, too!
CODE
Dim num_places As Integer
' Note that VBA log function returns natural logarithm.
' Dividing VBA log / log (10) converts to the base 10 logarithm.
' Screen out zero values to prevent error in logarithm calculation.
If my_num = 0 Then
sigfig = 0
Else
num_places = Int((Log(Abs(my_num)) / Log(10) + 1)  digits)
If num_places > 0 Then
sigfig = Round(my_num, num_places)
Else
sigfig = Round(my_num / 10 ^ (num_places)) * 10 ^ (num_places)
End If
End If
End Function
RE: Significant Digits
RE: Significant Digits
Type the numbers in the cells and select the cells.
Go to "format cells" option and select the "number". Right side entert the number of decimal places to display (default it will show 2. You can increase or decrease.
Before formating
100.23
100.24567
100.278
100
After formating for 3 decimal places
100.230
100.246
100.278
100.000
KMP
RE: Significant Digits
if you refer to the examples Willisv gives:
"....if I type in 3.1415 it would only show up as 3.14 in a cell. (Note that I can't just say display two decimal places because the next number might be 100.1 and I would want it to just display as 100).
You will see that format number doesn't do what is asked.
100.1 would be 100.10 with 2 decimal places.
JMW
www.ViscoAnalyser.com
RE: Significant Digits
=round(Var,SigFigint(log(Var))1)
e.g to show 3 significant figures of the value in A1
=round(A1,3int(log(A1))1)
This does not affect the number of digits displayed.
You could also write this as a custom function if you want.
RE: Significant Digits
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Significant Digits
handleman, CSWP (The new, easy test)
RE: Significant Digits
True. It's just that using worksheetfunction slows things down so much (especially in XL2007) that I avoid using it.
But it does work as written in VBA if you use worksheetfunction.round(... instead of just round.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Significant Digits
=round(Var,SigFigint(log(abs(Var)))1)
adding the ABS gives you the absoultue value of your number, making the formula work for negative numbers.
RE: Significant Digits
RE: Significant Digits
First, check "Precision as diplayed" in Tools/Options/Calculations
Col A: Using "General" number format, input orginal value
Col B: Set column number format to scientific, with the desired precision, and have the number from column A
Col C: Set column number format to "General" and set it equal to the value in column B
For some examples assuming "decimal places" for scientific format set at 3:
Col A: 156.7811
Col B: 1.568 E 2
Col C: 156.8
Col A: 0.00125668
Col B:  1.257 E 3
Col C: 0.001257
Of course, it may be cleaner if you hide column B (and possibly column A as well) when you are done...
 MechEng2005