Significant Digits

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

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)

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

eerrr..prev. examples for 3 sigs =)

Scientific notation:

3.14e0
1.00e2

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

-handleman, CSWP (The new, easy test)

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

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)

use the round function

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

 

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
www.ViscoAnalyser.com
 

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

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
www.ViscoAnalyser.com
 

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!

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.

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
 

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
www.ViscoAnalyser.com
 

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.

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/
 

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

-handleman, CSWP (The new, easy test)

"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/
 

You need to modify sdz formula slighly to make it work for negative numbers:
=round(Var,SigFig-int(log(abs(Var)))-1)

adding the ABS gives you the absoultue value of your number, making the formula work for negative numbers.

Thanks zelgar

If you are willing to use extra columns and use "Precision as displayed" option, it could be done very simply as follows:

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