Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is this rounding error?
In the below routine, are the instances where i=0 due to not being able to
express certain values exactly in binary? And can it be reliably solved for any 'r' by ROUNDing to 15 places? Or is there something else I'm missing? Thanks. =================== Sub foo() Dim r Dim i For r = 2 To 16 i = Fix(Log(r) / Log(r)) Debug.Print ("For r = " & r & ", i = " & i) Next r End Sub ================ --ron |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is this rounding error?
It is a little more complex than just rounding error. Try
this: Public Sub foo() Dim r Dim i For r = 2 To 16 i = Fix(Log(r) / Log(r)) Debug.Print "r:" & r & " i:" & i _ & " Log(r):" & Log(r) & " Log(r)/Log(r):" _ & Log(r) / Log(r) & " Fix:" & Fix(Log(r) / Log(r)) Next r End Sub You will see that when the results of the formula Fix(Log (r) / Log(r)) are printed directly it gives the result 1 but when it is assigned to the variable i it gives the result 0! This seems to occur even if I explicitly define r and i to be either long integers or a double precision floating point variables! -----Original Message----- In the below routine, are the instances where i=0 due to not being able to express certain values exactly in binary? And can it be reliably solved for any 'r' by ROUNDing to 15 places? Or is there something else I'm missing? Thanks. =================== Sub foo() Dim r Dim i For r = 2 To 16 i = Fix(Log(r) / Log(r)) Debug.Print ("For r = " & r & ", i = " & i) Next r End Sub ================ --ron . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is this rounding error?
Just a guess, but it is probably due to differences in the way extended
precision is used in evaluating VBA formulas and in the VBA Immediate window. I assume that Log(r) always evaluates to the same value (probably by a call to the Intel Math Coprocessor) for a given r and that x/x evaluates to 1 for a given x; else there would be all kinds of math discrepancies that we have not seen. Under those assumptions, Fix(Log(r)/Log(r)) can only evaluate to zero if the numerator and denominator represent different levels of precision. Math in the Coprocessor is done as 10 byte extended precision, but stored in variables as 8 byte double precision. If the numerator is rounded down to double precision or the denominator is rounded up to double precision, with the other term held in extended precision, then the ratio will be marginally less than 1. The additional mantissa bits between for the extended precision representations of exact values of Log(r) a r extended bits i 2 11110111000 1 3 00111011100 0 4 11110111000 1 5 10000010100 1 6 10110001111 1 7 11111101101 1 8 10111001010 1 9 00111011100 0 10 11110001010 0 11 10000001110 0 12 00100010010 0 13 00101011100 0 14 01001011011 1 15 11010110111 1 16 00000111001 1 These patterns do not seem entirely consistent with my hypothesis. I do not recall seeing any double precision discrepancies in Log(), but it may not be exact in extended precision, which might result in consistency. Jerry Ron Rosenfeld wrote: On Fri, 10 Sep 2004 08:37:39 -0700, "K Dales" wrote: It is a little more complex than just rounding error. Try this: Public Sub foo() Dim r Dim i For r = 2 To 16 i = Fix(Log(r) / Log(r)) Debug.Print "r:" & r & " i:" & i _ & " Log(r):" & Log(r) & " Log(r)/Log(r):" _ & Log(r) / Log(r) & " Fix:" & Fix(Log(r) / Log(r)) Next r End Sub You will see that when the results of the formula Fix(Log (r) / Log(r)) are printed directly it gives the result 1 but when it is assigned to the variable i it gives the result 0! This seems to occur even if I explicitly define r and i to be either long integers or a double precision floating point variables! I was aware of the differences you mentioned, depending on the assignment. Also, if you use the Decimal data type, the results seem to work out OK. For example: ========================== Sub foo() Dim r Dim i For r = 2 To 10^10 i = CDec(Fix(Log(r) / Log(r))) If i < 1 Then Debug.Print ("For r = " & r & ", i = " & i) Next r End Sub ======================= seems to always result in a '1' --ron |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is this rounding error?
On Mon, 13 Sep 2004 12:16:11 GMT, "Jerry W. Lewis"
wrote: If the numerator is rounded down to double precision or the denominator is rounded up to double precision, with the other term held in extended precision, then the ratio will be marginally less than 1. Well that may be exactly what is happening! It seems as if the denominator may be being rounded to double precision. For r = 2 To 20 i = Fix((Log(r)) / (Log(r))) If i < 1 Then Debug.Print ("For r = " & r & ", i = " & i) Next r OR For r = 2 To 20 i = Fix((Log(r)) / CDbl(Log(r))) If i < 1 Then Debug.Print ("For r = " & r & ", i = " & i) Next r results in: For r = 3, i = 0 For r = 9, i = 0 For r = 10, i = 0 For r = 11, i = 0 For r = 12, i = 0 For r = 13, i = 0 For r = 17, i = 0 ================== but converting the numerator results in i=1 for all numbers (at least up to 2^10): i = Fix(CDbl(Log(r)) / (Log(r))) Very interesting. So I guess this could be categorized as a rounding error of sorts, although not what we usually run into. I wonder why the designers would choose to use different precision for the numerator and denominator... Thanks for looking at this. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
rounding error | Excel Discussion (Misc queries) | |||
My rounding error in a simple IF | Excel Worksheet Functions | |||
rounding error? | Excel Discussion (Misc queries) | |||
Percentage rounding error in charts | Excel Discussion (Misc queries) | |||
Rounding Error | Excel Programming |