Significant Figures Function for Excel
Significant Figures Function for Excel
(OP)
I've come up with an Excel formula that will round a number to a user specified number of significant digits, as follows:
=ROUND(number,-(INT(LOG(number)+1-num_digits)))
Can someone please help me to code this into a user defined VBA function?
Such as Sigfig(number, num_digits)
I've tried but cannot get it to work as a user-defined function, but it seems it should be straightforward. I have limited VBA skills.
Thanks!
=ROUND(number,-(INT(LOG(number)+1-num_digits)))
Can someone please help me to code this into a user defined VBA function?
Such as Sigfig(number, num_digits)
I've tried but cannot get it to work as a user-defined function, but it seems it should be straightforward. I have limited VBA skills.
Thanks!





RE: Significant Figures Function for Excel
=ROUND(number,-(INT(LOG(ABS(number))+1-num_digits)))
Thanks again!
RE: Significant Figures Function for Excel
Public Function Sigfig(number, num_digits) As Double
Sigfig = Round(number, -(Int(Log(Abs(number)) + 1 - num_digits)))
End Function
RE: Significant Figures Function for Excel
Unfortunately the approach generates a #VALUE error. But I think I've figured this out, LOG in VBA is a natural logarithm but in Excel is the Base10 logarithm. Also, ROUND in Excel with a negative # of places works, but doesn't work in VBA. After addressing these issues, this is what I've ended up with:
VBA code for a significant figure function in Excel:
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)
sign = my_num / Abs(my_num)
If num_places > 0 Then
sigfig = Round(my_num, num_places) * sign
Else
sigfig = Round(my_num / 10 ^ (-num_places)) * 10 ^ (-num_places) * sign
End If
End If
End Function
RE: Significant Figures Function for Excel
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: Significant Figures Function for Excel
Function SigDigit(number, num_digits)
SigDigit = Application.Evaluate("ROUND(" & number & ",-(INT(LOG(ABS(" & number & "))+1-" & num_digits & ")))")
End Function
RE: Significant Figures Function for Excel
~~~~~~~~~~~~
Function SDRound(ByVal dblInValue As Double, intSD As Integer)
Dim intSign As Integer
Const INVLOG10 As Double = 0.434294481903252
intSD = intSD - 1
Select Case Sgn(dblInValue)
Case -1
intSD = intSD - Int(Log(-dblInValue) * INVLOG10)
SDRound = Application.Round(dblInValue, intSD)
Case 0
SDRound = 0
Case 1
intSD = intSD - Int(Log(dblInValue) * INVLOG10)
SDRound = Application.Round(dblInValue, intSD)
End Select
End Function