Is this function for significant digits robust?
Tushar Mehta
the 'n' most significant digits. I define the first significant digit
as the first non-zero digit. So, the number 123456 rounded to 3
significant digits is 123000 whereas the number 0.000123456 rounded to 3
significant digits is 0.000123.
I wrote the function that follows and I have two questions. First, is
there an obvious bug in it? Second, is there a better way to accomplish
the goal?
Function SignificantDigits(Nbr As Variant, SigDigits As Variant) _
As Variant
Dim PowerOf10 As Long, tempRslt As Double
PowerOf10 = Int(Log(Nbr) / Log(10#))
tempRslt = Application.WorksheetFunction.Round( _
Nbr * 10 ^ (-PowerOf10 + SigDigits - 1), 0)
'using WorksheetFunction.Round 'cause VB round returns _
'zero for 0.5 rather than 1
SignificantDigits = tempRslt / 10 ^ (-PowerOf10 + SigDigits - 1)
End Function 'SignificantDigits
Tests on the function for 1 to 5 significant digits used the numbers:
0.15345
123.45
123456
0.01
0.0012345
1.2345E-06
1.1
and that worked fine.
--
Regards,
Tushar Mehta
www.tushar-mehta.com
--
Harald Staff
This one from "my ancient file archive" is a bit shorter, to my humble
knowledge it works correct. I'm old fashioned when it comes to rounding,
I do it by dividing,integer,multiplying. So it does not really need
worksheet functions to run:
Function SigDigits(number As Double, digits As Long) As Double
Application.Volatile
Dim divider As Double
divider = 10 ^ (Int(Log(number) / Log(10#)) + 1 - digits)
SigDigits = Int(number / divider) * divider
End Function
The worksheet formula from which this was made, reads, with number in A1
and digit number in E1:
=INT(A1/10^(INT(LOG10(A1))-$E$1+1))*10^(INT(LOG10(A1))-$E$1+1)
Best wishes Harald
Dana DeLouis
something similar to...
SigDigits = WorksheetFunction.Round(n, (d - 1) -
Int(WorksheetFunction.Log10(n)))
Here is something I use. Please let me know if there are any 'special
cases' that do not work correctly. Dana
Function SigDigits(n, d As Integer)
' = = = = = = = = = = = = = = = = = = = =
' Rounds a number(n) to (d) significant figures
' By: Dana DeLouis, da...@msn.com
' = = = = = = = = = = = = = = = = = = = =
SigDigits = Val(Format(n, String(d, "#") & "E+00"))
End Function
"Tushar Mehta" <ng_p...@bigfoot.com> wrote in message
news:MPG.12f4de588...@msnews.microsoft.com...
> For some time there's been a need for a function that rounds a number to
Harald Staff
Comparing the three, I can also see that my "good old solution" is
rounding down. So I copied this one :-)
Harald
David J. Braden
See the discussion weeks back on this. Your routine, as well as Dana's and
Harald's, share with ROUND a bias that motivated what Stephen Bullen informs me
is called "banker's rounding". Suppose you add 115 to 125, rounded to n=2 in
your code. Your approoach would lead to a sum of 250, as does Dana's. Harald's
leads to 230. Perhaps you would prefer 240? If so, the convention is to take
115-> 120, and 125-> 120; ie, if the last digit to be included is followed by a
5, then round up if it is odd, round down if it is odd. A DejaNews powersearch
in *.worksheets with Bullen's name should uncover it. It occured after August,
before Christmas.
Regards,
Dave Braden
Dana DeLouis
little hard to figure out.
I thought this might be an issue. However, in Excel 2000, I entered
=115+125 to get a sum of 240.
With 2 significant digits, I get a result of 240? I am probably not doing
it correctly.?
I have noticed that Excel 2000's Format() function seems to be much better
behaved. I noticed that I am able to go back and take more advantage of the
Format command. Maybe something changed in Excel 2000. I do not know. I
would like to get more feedback on this. Thanks.
Anyway, while I was looking at this, I got to thinking about my custom VB
function for Log10 () vs. the method Tushar used. I did a loop with 3
different ways to get Log10 of a number in VB. (x was something like 1001)
TimerStart
For j = 1 To 10000
log10 = <function>
Next
TimerEnd
The 3 functions were..
log10 = Log(x) / Log(10#)
log10 = Evaluate(Replace("Log10(n)", "n", x))
log10 = WorksheetFunction.log10(x)
I had my Money on the second choice, with the first one last. The three
times, in order, were...
====================
Execution Time: 31 ms (~0.00 Min)
Execution Time: 6,860 ms (~0.11 Min)
Execution Time: 794 ms (~0.01 Min)
====================
Obviously, I changed my Custom Log10 Function to use the first one. :-)
Cheers. Dana
"David J. Braden" <t...@fiastl.net> wrote in message
news:388B7DDC...@fiastl.net...
Tushar Mehta
and a copy was sent to Dana DeLouis <da...@email.msn.com>.]
Another creative solution from Dana [DeLouis]. I have no idea how
someone would even come up with the val(format(...)...) idea but it is
inspirational and in the limited testing I did it worked just fine. But
then, I didn't expect it to break!
From a speed perspective (and relying on instinct rather than any
formal speed test), I am more inclined to go with the function that
"people posted in these newsgroups," i.e.,
SigDigits = WorksheetFunction.Round(n, (d - 1) -
Int(WorksheetFunction.Log10(n)))
with a modification of log(n)/log(10#) instead of .log10(n). Again, I
won't use the VBA Round because it rounds 0.5 to zero rather than 1.
Prompted by Dana's post, I looked up ROUNDUP and ROUNDDOWN and between
them and ROUND I should have what I want.
Keeping speed in mind, an alternative might be Harald [Staff]'s
suggestion since it avoids any call to the Excel object. An additional
benefit would be its usability in other VB/VBA environments. Based on
Harald's own follow-up it might need fixing to round up under the
correct circumstances.
A little more thought on my part and a little less action (programming)
would have so much smarter. But then, I would have missed out on the
postings in this thread.
--
Regards,
Tushar Mehta
www.tushar-mehta.com
--
In <eMDtV1cZ$GA....@cppssbbsa02.microsoft.com>, Dana DeLouis
<da...@email.msn.com> wrote
> Hello. People have posted in these newsgroups in the past a formula that is
> something similar to...
>
> SigDigits = WorksheetFunction.Round(n, (d - 1) -
> Int(WorksheetFunction.Log10(n)))
>
> Here is something I use. Please let me know if there are any 'special
> cases' that do not work correctly. Dana
>
> Function SigDigits(n, d As Integer)
> ' = = = = = = = = = = = = = = = = = = = =
> ' Rounds a number(n) to (d) significant figures
> ' By: Dana DeLouis, da...@msn.com
> ' = = = = = = = = = = = = = = = = = = = =
> SigDigits = Val(Format(n, String(d, "#") & "E+00"))
> End Function
>
>
> "Tushar Mehta" <ng_p...@bigfoot.com> wrote in message
> news:MPG.12f4de588...@msnews.microsoft.com...
David J. Braden
In XL98, and evidently in XL97, it's an issue, hence the thread that got
started. I don't have XL 2000 up and running. But I found your function, as
TM's, always rounds up, leading to bias (statistically).
Thanks for the timings. FWIW, I've found that those calls to XL from VBA are
pretty expensive.
Dave Braden
George Simms
The formula by Stephen Bullen is in David Hager's news letter 11 . I knew
I had seen it somewhere.
<snip>
POWER FORMULA TECHNIQUE
by Stephen Bullen
This formula perform 'bankers rounding' for a number (Num) to a given
number
(Plc) of significant digits.
=MROUND(Num,IF(VALUE(RIGHT(Num/10^(INT(LOG(ABS(Num)))-Plc+1),2))=0.5,2,1)*
SIGN(Num)*10^(INT(LOG(ABS(Num)))-Plc+1))
If you define 'Fact' as =10^(INT(LOG(ABS(Num)))-Plc+1), this reduces to:
=MROUND(Num,IF(VALUE(RIGHT(Num/Fact,2))=0.5,2,1)*SIGN(Num)*Fact)
<snip>
All the Best
George
Newcastle upon Tyne
England.
David J. Braden <t...@fiastl.net> wrote in message
news:388BDACF...@fiastl.net...
David J. Braden
Thanks. I got this just before I started a search for the thread. Yup, this is
the one the thread I had in mind evolved to.
For others, David's excellent collection is archived, courtesy of John
Walkenbach, at
http://www.j-walk.com/ss/excel/eee/index.htm
It also reminded me of why SIGN is in there: earlier in that thread I noted that
the approaches similar to Tushar's and Harald's fail when x < 0. Back to the
drawing board.
Regards,
Dave Braden