Round Function (Significant Figures) for Large Numbers

[obliteratu]

Hi

In a previous thread* I have found the formula to round a number to a
set degree of significant figures (not decimal places... woot!),
however, I would like the equivalent code for VBA so that I can write
my own function, rather than having to remmeber the formula every time.

*
http://groups.google.co.uk/group/mi..._frm/thread/1c9c2dcda3a48283/182b0ee70b4a2af6

Formula: (Thanks to J.E. McGimpsey for this stroke of genius)
For N significant figures, use:
=ROUND(A1, N - 1 - INT(LOG10(ABS(A1))))

The code I have written (note that Function statements have been
commented out so I can debug and a msgbox function added to check the
result):

Sub breakitdown()

'Function SigFig(x, SigFigs)

'chuck in a number for debugging
x = 654321

'set defult sigfigs if none provided
If SigFigs = "" Then SigFigs = 3

SigFig = Round(x, ((SigFigs - 1) - Int(Log10(Abs(x)))))

'check result
MsgBox SigFig

'End Function

End Sub

This works fine for small numbers (rounding decimal places), but,
unlike the worksheet function, VBA will not accept a negative integer
for the number of decimal places in the Round function. (I found this
out by using the altered step-by-step formula below to find the exact
point the code was falling over)

temp1 = Abs(mydata)
temp2 = Log10(temp1)
temp3 = Int(temp2)
temp4 = SigFigs - 1
temp5 = temp4 - temp3
SigFig = Round(mydata, temp5)

Does anyone know of a function in VBA that will round large numbers to
a set number of significant figures? I.e. 654321 becomes 654000

Thanks in advance

Rob

 

[Jerry W. Lewis]

You can use WorksheetFunction.Round to call the Excel round function from VBA.

I have posted code to handle the full range of inputs in the VBA Round
function
http://groups.google.com/group/microsoft.public.excel.charting/msg/107fce6145b70d69
Note that the VBA round function handles exact ties differently from the
Excel round function.
It also is less buffered against undisplayable differences due to binary
approximations (also addressed in my code).

The earliest reference I know of to this method of sigfig rounding is
http://groups.google.com/group/microsoft.public.excel.programming/msg/2b244c8f41e91025
though I would be very interested evidence of earlier independent discoveries.

Jerry

 

[Bob Phillips]

Sub breakitdown()

'Function SigFig(x, SigFigs)

'chuck in a number for debugging
X = 654321

'set defult sigfigs if none provided
If SigFigs = "" Then SigFigs = 3

sigfig = Application.Round(X, (SigFigs - 1 - Int(Log(Abs(X)) /
Log(10))))

'check result
MsgBox sigfig

'End Function

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[bplumhoff]

Hi Rob,

First my formula suggestion: =--TEXT(A1,"0."&REPT("0",A2-1)&"E+000")
In A1 you have your original number, in A2 the count of significant
digits you want.

My UDF:
Function dbl2nsig(d As Double, _
Optional n As Long = 3) _
As String
'Returns string presentation of d with
'n significant digits. PB V0.01
Dim i As Long, j As Long
Dim s As String, sr As String
s = Format(d, "0." & String(n - 1, "0") _
& "E+000")
i = Right(s, 4)
Select Case i
Case Is > n - 2
sr = Left(s, 1)
If n > 1 Then sr = sr & Mid(s, 3, n - 1)
sr = sr & String(i - n + 1, "0")
Case 0
sr = Left(s, n + 1)
Case Is < 0
sr = "0." & String(-1 - i, "0") & Left(s, 1) _
& Mid(s, 3, n - 1)
Case Else
s = Left(s, 1) & Mid(s, 3, n - 1)
sr = Left(s, i + 1) & "." & _
Right(s, n - i - 1)
End Select
dbl2nsig = sr
End Function

HTH,
Bernd