Significant Figures Function for Excel

[bltseattle]

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!

 

[bltseattle]

A correction to the previous post, the formula I need help coding should read:

=ROUND(number,-(INT(LOG(ABS(number))+1-num_digits)))

Thanks again!

 

[CajunCenturion]

You could try the following:

Public Function Sigfig(number, num_digits) As Double
   
   Sigfig = Round(number, -(Int(Log(Abs(number)) + 1 - num_digits)))

End Function

 

[bltseattle]

Thanks Cajun,
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

 

[joerd]

see my comments in thread770-39236

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[Bize]

How about?
Function SigDigit(number, num_digits)
SigDigit = Application.Evaluate("ROUND(" & number & ",-(INT(LOG(ABS(" & number & "))+1-" & num_digits & ")))")
End Function

 

[cummings54]

Here's another kick at the can
~~~~~~~~~~~~
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