Strange Log base 10 results

[Chip Hankley]

I've written a function in VBA that requires the Base-10 Log of a
number to be rounded DOWN to the closest integer. E.G. 10.1 would be 1.
10.9 would be 1, etc.

What I'm finding in VBA (whether I run this from Excel, Access, etc) is
that when x (below) is a power of 10, the result is wrong. In this
snippet, z SHOULD evaluate to 1 (i.e., log-base10(10)=1, int(1) = 1).
But it's not working that way. The last line shows why. The variable y
is evaluating to a number *just below* - 1-y is 1.11022E-16.

Anyone ever seen this, or know a good workaround. The function really
depends on this working correctly.

TIA!

Sub test()
x = 10
'Return Log base 10 of x,
' 1 in this case
y = Log(x) / Log(10)
'Round y DOWN to the nearest
' integer. Should be ONE
' in this case
z = Int(y)
Debug.Print z
Debug.Print Format(1 - y, "#.#####E-###")
End Sub

 

[Doug Glancy]

Chip,

I'm pretty sure this won't work in all instances, and I don't know why it
works here (aren't I helpful?) but declaring your variables as below makes
it work in this case:

Dim x As Double, y As Integer, z As Double

hth,

Doug Glancy

 

[Harlan Grove]

Chip Hankley wrote...
....

Anyone ever seen this, or know a good workaround. The function really
depends on this working correctly. ....
Sub test()
x = 10
'Return Log base 10 of x,
' 1 in this case
y = Log(x) / Log(10)
'Round y DOWN to the nearest
' integer. Should be ONE
' in this case
z = Int(y)
Debug.Print z
Debug.Print Format(1 - y, "#.#####E-###")
End Sub

Ah, yes. Crappy Microsoft library math functions. Due to the Microsoft
programmers 'cleansing' the return values from the FPU.

FWIW, using gawk 3.1.3 from a zsh for Windows prompt,

% gawk 'BEGIN {x=10;y=log(x)/log(10);printf("%g %g\n",int(y),1-y)}'
1 0

and Perl 5.8 from the same zsh prompt,

% perl -e '$x=10;$y=log($x)/log(10);printf("%g %g\n",int($y),1-$y);'
1 0

and using J 5.03a,

x =: 10
y =: (^.x) % ^.10
<.y, 1 - y
1 0

and using R 2.0.0,

x <- 10; y <- log(x) / log(10); c(trunc(y), 1 - y)

[1] 1 0

and, FTHOI, Excel

A1: 10
A2: =INT(LN(A1)/LN(10)) returns 1
A3: =LN(A1)/LN(10)-1 returns 0

Moral: don't trust Microsoft's library math functions in any of its
language implementations.

So what to do? Add a bias value and round the result.

Const BIAS As Double = 5E-16
':
y = Log(x) / Log(10) + Sgn(Log(x)) * BIAS

Pathetic, isn't it?

 

[Harlan Grove]

Doug Glancy wrote...

I'm pretty sure this won't work in all instances, and I don't know why it
works here (aren't I helpful?) but declaring your variables as below makes
it work in this case:

Dim x As Double, y As Integer, z As Double

....

You're cheating by declaring y as an integer. The question would then
become why VBA doesn't return the same thing for the expressions

CInt(Log(x) / Log(10)) and Int(Log(x) / Log(10))

and the answer would be that CInt rounds rather than truncates. Which
means declaring y as an integer would, e.g., make x = 4, y = Log(x) /
Log(10) set y to 1, which is clearly an error since Log10(4) =
0.602059991327962, which should round DOWN to zero.

 

[Harlan Grove]

Harlan Grove wrote...
....

So what to do? Add a bias value and round the result.

Const BIAS As Double = 5E-16
':
y = Log(x) / Log(10) + Sgn(Log(x)) * BIAS

....

I was being too nasty. While VBA shouldn't fail like this for x = 10,
all the other languages I tried failed for some x = 10^n, integer n >
1. This is just something that suffers a lot from floating point
rounding error. Adding a bias value is about all you can do.

 

[Ron Rosenfeld]

I've written a function in VBA that requires the Base-10 Log of a
number to be rounded DOWN to the closest integer. E.G. 10.1 would be 1.
10.9 would be 1, etc.

What I'm finding in VBA (whether I run this from Excel, Access, etc) is
that when x (below) is a power of 10, the result is wrong. In this
snippet, z SHOULD evaluate to 1 (i.e., log-base10(10)=1, int(1) = 1).
But it's not working that way. The last line shows why. The variable y
is evaluating to a number *just below* - 1-y is 1.11022E-16.

Anyone ever seen this, or know a good workaround. The function really
depends on this working correctly.

TIA!

Sub test()
x = 10
'Return Log base 10 of x,
' 1 in this case
y = Log(x) / Log(10)
'Round y DOWN to the nearest
' integer. Should be ONE
' in this case
z = Int(y)
Debug.Print z
Debug.Print Format(1 - y, "#.#####E-###")
End Sub

I'm not sure if this will work in all instances, or if your range of values is
compatible, but using the CDec type conversion function seems to result in
acceptable results:

y = CDec(Log(x) / Log(10))


--ron