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 ------
