-1

Fix function in vba is returning 2 when 3 is passed:

Debug.Print (Fix(Log(1000) / Log(10)))

--> This prints 2 instead of 3

Why is that?

BoNDoK
  • 135
  • 2
  • 4
  • 12
  • Hmm - plugged that in to [Wolfram Alpha](http://www.wolframalpha.com/input/?i=fix%28log%281000%29%2Flog%2810%29%29), and the result is "3", but look at the numberline visual, it's "2"...I wonder if that is what Excel's getting too? – BruceWayne Sep 02 '15 at 17:30
  • I don't understand your request to look at the numberline visual. – BoNDoK Sep 02 '15 at 17:33
  • It probably doesn't help get to an answer, but if you click the link, do you not see a numberline? Also, `int(log(1000)/log(10))` is giving `2` as well. Odd. Why do you want to use `Fix()`? – BruceWayne Sep 02 '15 at 17:35

5 Answers5

2

Log(1000)/Log(10) or 6.90775527898214 ÷ 2.30258509299405 should be equal to 3 but it appears that the 15 digit floating point precision is generating a 15 digit precision floating point error and Fix is trucating off all of the decimal. If you Round to 15 decimal places to remove the infinitesimally small error, you will receive 3 as your answer.

?round(log(1000)/log(10), 15)
2
Debug.Print Log(1000)   '6.90775527898214
Debug.Print Log(10)     '2.30258509299405

6.90775527898214
   /
2.30258509299405
=2.999999999999996      'VBA is convering this to '3#'

Debug.Print Fix(3#)     '2
Debug.Print Round(Fix(Log(1000) / Log(10)))     '2
Debug.Print Round(Round(Fix(Log(1000)) / Round(Log(10))))   '3 (This is what you want)  
Debug.Print (Round(Fix(Log(1000)) / Round(Log(10))))        '3 (Or may be only this)
ManishChristian
  • 3,759
  • 3
  • 22
  • 50
1

Doing a debug on the Log function:

Public Sub Test()
    Dim number As Double
    number = Log(1000)
    Debug.Print number
End Sub

prints: 6.90775527898214

And

Public Sub Test()
    Dim number As Double
    number = Log(10)
    Debug.Print number
End Sub

prints: 2.30258509299405

when you divide those you get: 2.(a lot of 9's) and calling Fix on that number will give you 2 not 3.

jameseg
  • 730
  • 1
  • 4
  • 19
0

Hm - I think this is how VB handles Log vs how the worksheet function handles it. Using this, I get 3:

Debug.Print fix(worksheetfunction.log10(1000)/worksheetfunction.log10(10))

I remembered I had a similar issue with Logs, and asked about it on SO, so perhaps it's a related issue here.

Community
  • 1
  • 1
BruceWayne
  • 22,923
  • 15
  • 65
  • 110
0

When you work with floating-point numbers (Single Data Type (Visual Basic) and Double Data Type (Visual Basic)), remember that they are stored as binary fractions. This means they cannot hold an exact representation of any quantity that is not a binary fraction (of the form k / (2 ^ n) where k and n are integers). For example, 0.5 (= 1/2) and 0.3125 (= 5/16) can be held as precise values, whereas 0.2 (= 1/5) and 0.3 (= 3/10) can be only approximations.

https://msdn.microsoft.com/en-us/library/ae382yt8.aspx

So what I did is created another fix function which looks like this: 'fix has issues with

Public Function FixModified(ByVal x As Double, ByVal threshold As Double)
    If Fix(x) = x Then
        FixModified = x
    ElseIf Fix(x) + 1 - x < threshold Then
        FixModified = Fix(x) + 1
    Else
        FixModified = Fix(x)
    End If
End Function

The threshold I am passing now is 0.000000000001 and it is working as expected.

BoNDoK
  • 135
  • 2
  • 4
  • 12