×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Significant Digits3

## Significant Digits

(OP)
Can Excel be formatted to display a set number of significant digits rather than just decimal places?

### RE: Significant Digits

Yes, select the cell (or range of cells), right click, Format Cells, Number tab, change Category to Number, adjust number of decimal places, OK

### RE: Significant Digits

Format - Cells - Number - Scientific allows you to precisely control the significant digits.  But not sure if you like that formatn.

### RE: Significant Digits

enirwin,
Do you just want the same number of digits, no matter where the decimal point is?   It sounds like this approach might work for your requirement.  In this example, Cell B98 contains the raw number, and a 5-digit figure is displayed in the cell with this formula.  Obviously, you can change from 5 digits to any other number you choose, and the range can be extended up to seven nested IF functions.  Hope this helps.

=IF(B98<1,ROUND(B98,5),IF(B98<10,ROUND(B98,4),IF(B98<100,ROUND(B98,3),ROUND(B98,2))))

Thus if B98=0.123456, you get 0.12346
if B98=1.23456, you get 1.2346
if B98=12.3456, you get 12.346
if B98=123.456, you get 123.46, etc.

P. S.  I just remembered that, if all significant figures are to the right of the decimal point, Excel will show a zero to the left of the point, as I did above.  I haven't tried to eliminate that feature.  I hope it is not a problem

### RE: Significant Digits

(OP)
Lcubed,

That is exactly what I was trying to do.  The zero to the left of the decimal is not a problem.

Thanks

### RE: Significant Digits

I have been trying to do this for some time - no idea why this function isn't built in to Excel.  I wanted to do this for numbers over the whole range
(i.e. 1E-99 to 1E+99, and -1E-99 to -1E+99).

The following formula appears to work well.

=ROUND(Number,IF(ABS(number)>1,Sigfig-1-TRUNC(LOG(ABS(number)),0),Sigfig-TRUNC(LOG(ABS(Number)),0)))

Where Number = the number you want displayed to a number of significant figures
sigfig = the number of significant figures you want displayed.

e.g. Number = 1234.5678
sigfig = 1, Result = 1000
sigfig = 2, Result = 1200
sigfig = 3, Result = 1230
sigfig = 4, Result = 1234 etc.

### RE: Significant Digits

I've come up with another Excel function similar to TrevorP's but simpler.

=ROUND(number,-(INT(LOG(number)+1-num_digits)))

Can someone please code this into a user defined VBA function?

Such as Sigfig(number, num_digits)

I've tried but cannot get it to work as a user-defined function, but it seems it should be straightforward.  I have limited VBA skills.

Thanks!

### RE: Significant Digits

As a followup, please note that the approach I just posted will only work for positive values, hence the simpler formula.  TrevorP's approach will work on positive or negative values.

BLT

### RE: Significant Digits

After resolving some idosyncracies of VBA functions that are not the same as Excel functions (e.g. Round and Log), this is the user-defined significant figure function I've come up with, for anyone still following this thread!

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)
sign = my_num / Abs(my_num)
If num_places > 0 Then
sigfig = Round(my_num, num_places) * sign
Else
sigfig = Round(my_num / 10 ^ (-num_places)) * 10 ^ (-num_places) * sign
End If
End If
End Function

### RE: Significant Digits

I would remove all the "sign" stuff, because your function will convert negative values to positive (e.g. sigfig(-0.2345,2) = 0.23
For the rest, it works fine.
By the way, VBA has a Sgn function, no need for my_num/Abs(my_num)

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

Thanks Joerd!

### RE: Significant Digits

I have a question that is along these same lines.  I have a lab that I monitor that calibrates a wide range of equipment.  So therefore We have a wide range of significant figures to account for.  What I am running into is that as per company practice, any spreadsheets that are used by the technicians must be locked, except for the data entry cells.  So now to the question...

If they are recording data, where the significant figures are different.  Is there anyway to have the formulas analyze the data and report values in the correct significant figures.

For example:

The Unit Under Test reads 12.2
The result of the formula using this data should be in the form of X.X.

But this same sheet is going to be used for other equipment so there will be many combinations of this sort.

### RE: Significant Digits

Good question,
I have been playing with the significant figure function as noted above and as revised below.  WHat I've found is that if the cell that contains the user-defined funtion is formatted "general", it will display the value with the correct number of digits.  If you change to a "number" format, it will fix the number of decimals - not what you want!

The function below requires the # of digits as input.  It sounds like you want to calculate the number of digits from what the technician enters, which will likely be very tricky.  How will you handle if the value is 12.00?  That's 4 significant figures, but if you enter it as a number Excel will treat it as 2 characters...  If you enter '12.00 it will come in as text, and count as 5 significant digits because the point counts as well as the numerals.  My point is, it will be much easier to input the correct number of digits than to compute it from the technician's input test value.  Then the function can display the value rounded to the correct number of sig digits.  If this approach is unacceptable, you may be forced to use text input values and then have some hidden text operations to compute the correct number of digits.

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

### RE: Significant Digits

This is a good solution to significant figures. Thanks for posting it. Now if I could just get excel to align on the decimal and strip off trailing zeros that are not significant. Any suggestions???

### RE: Significant Digits

I take that back!!! This truely is a glorious day!

Use the sigfig formula above with a custom format (e.g. ???.???) with enough digits to accomodate the numbers in your list.

Wooohooo!!!!!!!

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Close Box

# Join Eng-Tips® Today!

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 Eng-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!