## 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 ... http://www.eng-tips.com/faqs.cfm?pid=731&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.