Incorrect data returned from LN function 2

[mrbud1972]

Hi, any idea if anything can be done? It's just an example of the problem.
If I use the calculation LN(1.00001)in excel i get the result 9.99995000039884E-06
The correct value is 9.99995000033333E-06 and if I use a calculator (even the one in Windows XP) I get that value.
I cant round it down to decimal places as it's critical i get the exact value.
I have referred to the document

http://support.microsoft.com/kb/78113/en-us

but it doesnt really make sense with the problem I have.
Thanks in advance
Martin.

 

[xlhelp]

These may not help, but please take a look at these:

http://support.microsoft.com/kb/214118/en-us

http://support.microsoft.com/kb/69333/en-us

Member- AAAA Association Against Acronym Abusers

 

[Rofeu]

Hi,

I don't see a solution to this discrepancy, it has to do with the number of decimals Excel can handle. I would like to point out, that the natural logarithm (LN) makes use of constant e, which is a transcendental, hence you will never get an exact value. Furthermore, taking the conventions (rules) for sigfigs, both results should be the same for your purposes.

Cheers,

Roel

 

[SkipVought]

Give me a break. Back in the "old days", my Picket & Eckel gave me the correct answer to 3 places, maybe. Beyond that, you went to a table of mantissas. What are you doing beyond 5 places anyhow?



Skip,
[sub]
[red][/red]
[/sub]

 

[xlhelp]

Back in the "old days", my Picket & Eckel gave me the correct answer to 3 places

Now that's going back a while. The good ol' days of slide rules. I still have mine.

Member- AAAA Association Against Acronym Abusers

 

[SkipVought]

Leather holster and all?

Every once in a while I get out the ol' aluminum slip stik and try a few multiple operations, adjust the slider hairline. Can't find my old 6" circular, though.

Skip,
[sub]
[red][/red]
[/sub]

 

[xlhelp]

Yup, leather holster. You reminded me. I have the circular one too. I preferred the circular over the linear, most times. You suppose I could sell them as antiques? LOL.

Member- AAAA Association Against Acronym Abusers

 

[SkipVought]

There apparently are collectors.

Yup, the 6" circular is "longer" that the 10" linear.

Skip,
[sub]
[red][/red]
[/sub]

 

[bluedragon2]

Which came first, you two or dirt?

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[SkipVought]

I came into this world two months after the attack on Pearl Harbor.

Lots of dirt around. ;-)

Not too many earlier than that got comfortable with the age of personal computing. I have a cousin, fellow engineer, that graduated from Lehigh 4 years before I did, and never got beyond the PC as a glorified typewriter.

Skip,
[sub]
[red][/red]
[/sub]

 

[combo]

In case of the initial problem (LN function), in the specific case the back to origins could be helpful. As LN(1+x)=x-x^2/2+x^3/3-..., for x close to 0, a pencil, sheet of paper and spreadsheet could be helpful to get digits after 33333... .

combo

 

[xlhelp]

combo

I guess Excel is limited in resolving the ln function.

I thought using the the Mercator series would still give false results, but it actually works.

Salute to u

Member- AAAA Association Against Acronym Abusers

 

[MeGustaXL]

Well done combo for reminding us of the basics: have a [

http://www.tek-tips.com/images/star.gif

] !

Chris

I visited the offices of the RSPCA today. It's so tiny you couldn't swing a cat in there.
- Tommy Cooper

 

[combo]

Thanks . 11 digits of accurancy of an algorithm with double precision 15 digits output is not a bad result, as long as you do not want to deduct 1.
Aside of digital representation/calculation in excel, there is no exact value the result in decimal representation, whatever number of digits are used.

combo

 

[mrbud1972]

Thanks for your comments. THis is where I try to throw a spanner into the works of precission. Try this formula for size !
=LN(1+1/1000000000)
Within excel, the result is 1.000000082240370E-09
now, try it on your humble calculator. The result is
9.9999999950000000033333333308333e-10

 

[TonyJollans]

There are all sorts of funnies: consider LN(1.00001) vs (LN(1.00002-0.00001) for example. Part of this stems from what appears to be an inaccuracy in the value of e (enter =EXP(1) and format to 30 d.p.)

Bottom line here is that Excel is *not* the tool to use if this level of accuracy is required.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association