5

I'm trying to get the log of a number but it looks like the Worksheet function gives one answer, and VBA another.

In VB, using Log(Range("A2")) where A2 is 7, I get 1.94591014905531, which is the correct answer. But, if in the worksheet, I use =Log(A2,10) or =Log10(a2), I get 0.84509804. What's going on here?

BruceWayne
  • 22,923
  • 15
  • 65
  • 110
  • Possible duplicate of [Simple recursive function in VBA with Excel not returning expected result](http://stackoverflow.com/questions/9071559/simple-recursive-function-in-vba-with-excel-not-returning-expected-result) – GSerg May 12 '16 at 07:14

2 Answers2

20

VBA's Log function is the natural log. If you want log base ten you will have to use the logarithmic identity for converting bases. Like so: Log(x)/Log(10).

Michael S Priz
  • 1,116
  • 7
  • 17
  • 2
    Also, that you refer to 1.94593... as the *correct answer* implies that it is indeed the natural log you are looking for? To do this in a formula, use =LOG(A2,EXP(1)). Here, EXP(1) gives us e^1 = e = 2.72...., the base of the natural log. – Mark Butler Jul 28 '15 at 15:42
  • You can also use Excel's version with `Application.WorksheetFunction.Log(x)`. – Jacktose Apr 11 '19 at 23:52
0
Function roundit1(nn As Variant, sd As Variant)
    nn = Val(nn)
    If Not (nn = 0) Then
        xx = (1 + Int(Log(Abs(nn)) / Log(10)))
    Else
        xx = 0
    End If
    ' significant digits
    roundit = sd - xx
    If roundit < 0 Then roundit = 0
   roundit1 = Round(nn, roundit)
End Function
fnome
  • 1
  • While this may answer the question, it is better to explain the essential parts of the answer and possibly what was the problem with OPs code. – pirho Dec 15 '17 at 21:36