asked on

VB / Excel : LOG / LN functions

If I do the same calculation between VB and Excel using the Log and LN functions, I seem to get the same result. However, I get different final answers between the cell in Excel and the variable in VB.
Formulae is as follows :
EXCEL FORMAT "=-0.08*LN(1-25/100)*1000000/365"
VB FORMAT "=-0.08*LOG(1-25/100)*1000000/365"

Try running this in VB and Excel and there is a different answer that results (Excel reports 65 whereas VB reports 63).
PS. Excel cell is formatted for no decimals, and the VB result is stored in an integer...

Which one is correct ?

deighton

65 is wrong, the correct answer is 63.05.. This should never have rounded to 65. Check your formulae carefully in excel. Perhaps some of the 'numbers' in your excel formulae may be derived from cells that contain numbers e.g 25 rounded to no dp's. A possibility here is that the number in the cell could actually be 25.45 for example, but you would see 25, whereas Excel would use the full value unless you explicitly round of the decimals first using a rounding formula.

deighton

BTW

-0.08*LN(1-25/100)*1000000/365

gives me the same answer in Excel as -0.08*LOG(1-25/100)*1000000/365 in VB

I'm convinced the difference is somewhere else in your formula. Possibly rounded values.

caraf_g

Yep, the correct result (rounded to an integer) is 63.

I looked at the formula, and nowhere can I find anything that, rounded, would explain the result of 65.

deighton must therefore be right. It could be explained if the value displayed as 25 is really approx. 25.66

ASKER CERTIFIED SOLUTION

deighton

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

staffiidba

ASKER

Adjusted points to 150

staffiidba

ASKER

Very well done.
Thanks for the prompt response.
The 0.08 is actually 0.08xxxxxxxx which explains the difference.
Thanks Deighton. Well done.

caraf_g

:-) Yes, even Micro$oft couldn't have f***ed up *this* bad. <g>