sinh returns wrong answer for small arguments.

[Dr. Ronald S. Davis]

For sinh(1e-17), which shows up as

=SINH(0.00000000000000001)

, Excel in all versions I have tried returns the value 0. The correct value
is about 1e-17. Is there a fix for this problem?

 

[ker_01]

Excel has trouble with that many significant digits. I believe the following
apply:

http://support.microsoft.com/kb/158071

http://support.microsoft.com/kb/78113

I haven't been affected by this problem in my own use of Excel, so I haven't
explored the possible use of UDFs to get around this limitation- but if the
"precision as displayed" option in the second link isn't sufficient, that
might be the next place to explore.

HTH,
Keith

 

[Dr. Ronald S. Davis]

The cited "kb" articles explain clearly that Excel works to about 16
significant figures, i.e. that the relative error is about 1E-16. However,
here is a comparison between sinh as Excel 2007 gives it and the value using
the first 10 non-zero terms of the Taylor expansion about 0, which should be
good to that precision in the tabulated range:

x "sinh x" according to Excel Taylor approximation
Relative error
1.E+00 1.17520119364380E+00 1.17520119364380E+00 1.89E-16
1.E-01 1.00166750019844E-01 1.00166750019844E-01 6.93E-16
1.E-02 1.00001666674999E-02 1.00001666675000E-02 8.15E-15
1.E-03 1.00000016666668E-03 1.00000016666667E-03 6.51E-15
1.E-04 1.00000000166689E-04 1.00000000166667E-04 2.23E-13
1.E-05 1.00000000001210E-05 1.00000000001667E-05 4.56E-12
1.E-06 9.99999999973245E-07 1.00000000000017E-06 2.69E-11
1.E-07 9.99999999473644E-08 1.00000000000000E-07 5.26E-10
1.E-08 9.99999993922529E-09 1.00000000000000E-08 6.08E-09
1.E-09 1.00000002722922E-09 1.00000000000000E-09 2.72E-08
1.E-10 1.00000008274037E-10 1.00000000000000E-10 8.27E-08
1.E-11 1.00000008274037E-11 1.00000000000000E-11 8.27E-08
1.E-12 1.00003338943111E-12 1.00000000000000E-12 3.34E-05
1.E-13 9.99755833674953E-14 1.00000000000000E-13 2.44E-04
1.E-14 9.99200722162641E-15 1.00000000000000E-14 8.00E-04
1.E-15 1.05471187339390E-15 1.00000000000000E-15 5.19E-02
1.E-16 5.55111512312578E-17 1.00000000000000E-16 8.01E-01
1.E-17 0.00000000000000E+00 1.00000000000000E-17 #DIV/0!

In contrast to the cited "kb" items, the relative error is above 1E-16
throughout the tabulated range. The result is seriously wrong even for an
argument of 0.01, and where I discontinued the tabulation the answer is not
even an attempt at the right answer.

 

[ker_01]

I'm sorry I can't be of more help- I'm just another random newsgroup
contributor, not a statistical expert. If I had to guess, I'd think that
Excel's issue with precision affects all of the subcalculations leading to
the overall sinh value, not just the overall sinh value- but it sounds like
you are much more knowledgable about how sinh should be calculated, and
whether the precision would throw off any of the component calculations.

Best regards,
Keith