CODEWITHSUNDEEP
excelvba
Kabir
Kabir

Reputation: 21

Log function in excel (vba) - not giving me the right answer

In this code snippet below, the value of temp_int2 is 1, makes no sense to me. --> log(1000) = 3, that is log of base 10, the log function is base 'e'.

im not sure if its the "INT" function which problematic but could someone please assist.

enter image description here

temp_int2 = Int(Log(1000) / Log(10)) - 1
    'temp_int2 = Int(Log(cap_dec) / Log(10)) - 1

    MsgBox ("Value of log functuon -->" & CStr(Log(cap_dec) / Log(10)) & "    value after log function " & CStr(temp_int2))

Upvotes: 1

Views: 488

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57693

Instead of the Int function use the cLng function. While Int will cut off decimals, cLng will round to a Long.

Example Int will cut off

Int(99.2) '= 99
Int(99.5) '= 99
Int(99.8) '= 99

but cLng will round

cLng(99.2) '= 99
cLng(99.5) '= 100
cLng(99.8) '= 100

since computers and calculators calculate numeric and not algebraic there is probably a precision issue in calculation and Log(1000) / Log(10) is not exactly an algebraic 3 but a numeric 3 that is something like 2.99999999999998 which Int will cut off to 2 but cLng will round to 3.

Note that Excel is a numeric calculation program and values of type Double are not exact values. The decimals are (as with any standard calculator too) only calculated up to a defined precision. So a Double type 3 is not a 3 but something very close to 3 like 2.99999999999998. So the Log function returns a Double and also a devision Log(1000) / Log(10) returns a Double and this is not exactly 3 but very close to 3.

Note that this is not a bug but in the nature of numeric calculations which are never exact but only precise, while algebraic calculations can be exact.

The same problem occurs when comparing values of type Double:

If DoubleA = DoubleB Then 'might not work

Here you need to use something like

If (DoubleA - DoubleB) ^ 2 < (10^ - Digits)^2

where Digits is the number of digits that need to be the same. Example

DoubleA = 0.9999999999
DoubleB = 1.0000000001

then Digits needs to be <= 9 to consider them as equal.

If you need to do that often then it comes handy to use a function for that:

Option Explicit

Public Function IsDoubleValueTheSame(DoubleA As Double, DoubleB As Double, Optional Digits As Long = 12) As Boolean
    IsDoubleValueTheSame = (DoubleA - DoubleB) ^ 2 < (10 ^ -Digits) ^ 2
End Function

and call it like 

Debug.Print IsDoubleValueTheSame(0.9999999999, 1.0000000001, 9)  'true
Debug.Print IsDoubleValueTheSame(0.9999999999, 1.0000000001, 10) 'false

So to come back to your initial example:

Debug.Print Log(1000) / Log(10) = 3 'false
Debug.Print IsDoubleValueTheSame(Log(1000) / Log(10), 3, 15) 'true
Debug.Print IsDoubleValueTheSame(Log(1000) / Log(10), 3, 16) 'false

which means Log(1000) / Log(10) is actually 3 precise up to 15 digits and the 16ᵗʰ digit is different.

Further information about this:

Upvotes: 2

Related Questions