Automatic formatting of significant figures 6

[ColourfulFigsnDiags]

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

 

[kris44]

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

 

[ColourfulFigsnDiags]

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&fid=376

 

[bltseattle]

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!

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

 

[ab123456]

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.

 

[Denial]

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.

 

[ColourfulFigsnDiags]

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

 

[ColourfulFigsnDiags]

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

Read the Eng-Tips Site Policies at FAQ731-376

 

[Denial]

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 ------
[tt]
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
[/tt]
------ Code ends ------

 

[barbershopper]

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.