Significant Digits

[WillisV]

Is there a way to have excel only display a certain number of significant digits?

 

[handleman]

What do you mean by significant digits? Excel can't necessarily calculate significant digits. The number of significant digits is based on the accuracy/precision of your measured numbers. If you want to show 1,250,000 instead of 1,253,597 then try scientific notation.

-handleman, CSWP (The new, easy test)

 

[WillisV]

I mean is there a way that I can say - please display only 4 significant digits - so that 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).

 

[WillisV]

eerrr..prev. examples for 3 sigs =)

 

[handleman]

Scientific notation:

3.14e0
1.00e2

As far as I know it's the only way.

-handleman, CSWP (The new, easy test)

 

[TomBarsh]

Maybe I'm way off base here from what you want, but you can control the display of the number of decimals in a cell. Use the cell format command.

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)".

 

[handleman]

Tom,

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)

 

[cvg]

use the round function

 

[MintJulep]

Cell formatting affects the way a number is DISPLAYED on your screen or on prints. Formatting DOES NOT CHANGE the underlying data.

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

 

[jmw]

My first thought was the format number then round but neither actually does what I think is required.
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

http://www.ViscoAnalyser.com

 

[cowski]

I agree with handleman, I think scientific notation is your best bet.

 

[jmw]

I only know IF functions, or so it seems, so I extended this to handle larger numbers:
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

http://www.ViscoAnalyser.com

 

[bltseattle]

Here is a VBA function I put together for this purpose because I didn't like the built-in options. Open the VBA editor and paste this into a module. Then you can use the formula in your worksheet:

=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!

Public Function sigfig(my_num, digits) As Double
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

 

[Denial]

The short answer to the original question is a resounding NO. As in so many areas, Mr Gates's software writers pander to accountants, not engineers.

 

[kmpillai]

Hi,
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

 

[jmw]

kmpillai,
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

http://www.ViscoAnalyser.com

 

[sdz]

You can do it this way.
=round(Var,SigFig-int(log(Var))-1)

e.g to show 3 significant figures of the value in A1
=round(A1,3-int(log(A1))-1)

This does not affect the number of digits displayed.

You could also write this as a custom function if you want.

 

[IDS]

sdz - that works when entered in the spreadsheet, but not as a custom function, because the VBA round function will not accept a negative number of decimal places. The bltseattle custom function works.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[handleman]

You can use Excel's ROUND worksheet function inside VBA. I believe it's Application.WorksheetFunction.Round(....)

-handleman, CSWP (The new, easy test)

 

[IDS]

"You can use Excel's ROUND worksheet function inside VBA. I believe it's Application.WorksheetFunction.Round(....)"

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/