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!
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 ... ht





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
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 ... ht tp://www.e ng-tips.co m/faqs.cfm ?pid=731&a mp;fid=376
RE: Automatic formatting of significant figures
That said, I developed a user-defined function for sig figs a while back that I am happy to share!
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: Automatic formatting of significant figures
=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
RE: Automatic formatting of significant figures
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
Read the Eng-Tips Site Policies at FAQ731-376
RE: Automatic formatting of significant figures
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
##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.