×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • 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.

Students Click Here

Automatic formatting of significant figures
6

Automatic formatting of significant figures

Automatic formatting of significant figures

(OP)
Hi all,

I have a table of numbers, ranging from about 0.552 to about 821. All numbers have exactly 3 significant figures.

Is there any way to automatically format ALL the numbers in this table to show exactly 3 significant figures only, without having to resort to exponent notation?

Thanks in advance!

Read the FAQ ... http://www.eng-tips.com/faqs.cfm?pid=731&fid=376

RE: Automatic formatting of significant figures


If your numbers range between 0-1000 then use this macro;

Click the button whenever new data is entered.
The Range ("a1:d4") will need to be changed to the range of cells you want to adjust the numbers for.
Also need to change "sheet1" to the approriate sheet


Private Sub CommandButton1_click()
For Each i In Worksheets("sheet1").Range("a1:d4")
    If i.Value > 0 Then
        If i.Value < 1 Then
            i.Value = Round(i.Value, 3)
            i.Select
            Selection.NumberFormat = "0.000"
        ElseIf i.Value < 10 Then
            i.Value = Round(i.Value, 2)
            i.Select
            Selection.NumberFormat = "0.00"
        ElseIf i.Value < 100 Then
            i.Value = Round(i.Value, 1)
            i.Select
            Selection.NumberFormat = "0.0"
        ElseIf i.Value < 1000 Then
            i.Value = Round(i.Value, 0)
            i.Select
            Selection.NumberFormat = "0"
        End If
    End If

Next
End Sub

RE: Automatic formatting of significant figures

(OP)
Seems like a difficult way to do a very mundane and typical thing. I'm amazed this functionality is not inbuilt!

Thanks anyway. Fortunately this table was not all that big, so I just did the formatting manually. I might change your code slightly though, put it in my user functions add-in and make it a bit more general for next time!

Thanks!

Read the FAQ ... http://www.eng-tips.com/faqs.cfm?pid=731&amp;fid=376

RE: Automatic formatting of significant figures

I too am perplexed at how this is not built-in functionality.  I think it reflects Microsofts lack of support for engineers using Excel.  There are many other situations where I have to develop work-around sloppy solutions to deal with Excel's limitations.

That said, I developed a user-defined function for sig figs a while back that I am happy to share!

CODE

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: Automatic formatting of significant figures

For those who arent familiar with VB you can use

=IF(A1>1,ROUND(A1,A2-(LEN(INT(A1))),ROUND(A1,A2))

Where the number is in A1 and A2 contains the number of significant digits you want to round to.

RE: Automatic formatting of significant figures

2
Let's bear in mind that the original poster asked about how to FORMAT the numbers for presentation.  There was no mention of ROUNDING them, which could have the potential to introduce errors in any subsequent calculations.  Maybe he is happy to use rounding as a means of achieving his required format, maybe not.

RE: Automatic formatting of significant figures

(OP)
Very good point Denial, and you are correct. The formatting is simply for display purposes.

In this particular case, the numbers were not subsequently used in other calculations- but had they have been, I wouldn't want to be rounding them. Therefore kris44's solution is the only one I would have been able to use.

Read the Eng-Tips Site Policies at FAQ731-376  

RE: Automatic formatting of significant figures

(OP)
Actually I would have to use kris44's method without the rounding.

Read the Eng-Tips Site Policies at FAQ731-376  

RE: Automatic formatting of significant figures

Excel's inability to present numbers to a given precision has gnawed at me for years, as has its inability to utilise "engineering format".  So, inspired by this post, I have written myself a VBA function that does both of these things simultaneously (with the "engineering format" feature being optional).

For what it's worth, I present it below.  Note that it presents its result as a string rather than as a number, and that if you subsequently convert this string back into a number you will not get the number you first started with.

The usual "denial" of all responsibility applies of course.

------  Code begins  ------

Public Function SigFig(Number As Double, Sig As Long, Optional Eng As Boolean = False)
'
'  Function to present a number to a specified number of significant figures.
'
'  Optionally the function will also present the number in "engineering"
'  notation (ie in exponential notation but with the exponent being a multiple
'  of 3).
'
'  Input arguments:
'  • Number – the number to be presented;
'  • Sig    – the number of significant digits to be used in the presentation;
'  • Eng    – "True" if the number is to be presented in engineering notation,
'             "False if the number is to be presented in non-exponential form,
'             with the default being "false".
'
'  Note that the result of this function is a STRING.
'
Dim Sign As String, WorkNumber As Double, Magnitude As Long
Dim DecPlace As Long, NumForm As String, ExpString As String
Dim Exp As Long
Dim Result As String
Const ConvLog As Double = 2.302585092994        ' LN(10)
Const roundoff As Double = 1.00000000001        ' See below
'
'  Some preliminary checks.
'
If (Not IsNumeric(Number)) Or (Not IsNumeric(Sig)) Then GoTo ErrorReturn
If Eng Then
    If Sig < 3 Then GoTo ErrorReturn
Else
    If Sig < 1 Then GoTo ErrorReturn
End If
'
'  Extract sign and magnitude of the number.
'
'  Note the use of the number "roundoff" when the log will be negative.
'  This is a crude workaround for a difficulty that arises when the
'  magnitude of the number is very close to 0.1 or 0.01 or 0.001 or ...
'
If Number < 0 Then
    Sign = "-"
    WorkNumber = -Number
Else
    Sign = ""
    WorkNumber = Number
End If
If WorkNumber = 0 Then
    Magnitude = 0
ElseIf WorkNumber > 0.5 Then
    Magnitude = Int(Log(WorkNumber) / ConvLog)
Else
    Magnitude = Int(Log(WorkNumber * roundoff) / ConvLog)
End If
'
'  Round the number as required.
'  (Must use the WorksheetFunction because VBA's ROUND function cannot
'  accommodate negative values in its second argument.)
'
'  Then recalculate the magnitude in case it was changed by the rounding.
'
WorkNumber = WorksheetFunction.Round(WorkNumber, Sig - Magnitude - 1)
If WorkNumber = 0 Then
    Magnitude = 0
ElseIf WorkNumber > 0.5 Then
    Magnitude = Int(Log(WorkNumber) / ConvLog)
Else
    Magnitude = Int(Log(WorkNumber * roundoff) / ConvLog)
End If
'
'  For "engineering format", determine the required exponent and change
'  the mantissa accordingly.
'
Exp = 0
ExpString = ""
If Eng Then
    Select Case Magnitude
        Case Is < -1
            Exp = -3 * Int((1 - Magnitude) / 3)
        Case Is > Sig - 1
            Exp = 3 * Int((Magnitude + 1) / 3)
    End Select
    If Exp <> 0 Then
        ExpString = "E" & Format(Exp, "00")
        WorkNumber = WorkNumber / 10 ^ Exp
        Magnitude = Magnitude - Exp
    End If
End If
'
'  Now form the non-exponential part of the required result.
'
NumForm = "0"
DecPlace = Sig - Magnitude - 1
If DecPlace > 0 Then NumForm = NumForm & "." & String(DecPlace, "0")
Result = Format(WorkNumber, NumForm)
'
'  Assemble the final answer.
'
Result = Sign & Result & ExpString
SigFig = Result
Exit Function
'
ErrorReturn:
SigFig = CVErr(xlErrNA)
End Function

------  Code ends  ------

RE: Automatic formatting of significant figures

Although it does not directly respond to the question.  For those that want to format numbers in engineering notation, try using this format\cells\number\custom format:

##0.0E+0;[red]-##0.0E+0

The numer of places after the decimal point can be adjusted as usual from the toolbar at anytime.

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.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close