Error in Excel Built-in Functions (perhaps OT)



#2

This pertains more generally to my growing amateur interest in numerical analysis and approximation rather than HP calculators per se. Since such matters are of huge importance to some who program HP calculators I hope the group will indulge me one more dalliance.

In my efforts to learn more about the practical aspects of minimax approximation I have been experimenting with the Solver applet in Excel. Now I know it is not the most sophisticated optimization tool out there, but it is fair enough for my low level messing about at this juncture.

What I have discovered in passing is that whereas some of Excel's built-in functions (like the trig, log, and exponential operations) have no problem rendering full 15-digit precision if that is what you want, some other special functions, like ERF(), ERFC(), and certain of the statistical distributions I have experimented with, don't seem to offer such high precision--pretty good for some arguments but clearly approximations and not to 15 digits. Therefore, if I want to generate approximations based on these functions I need to import full precision data points from some other software. Fair enough. If that is what is necessary there are ways I can do that.

But my question is this: Does Microsoft or anyone else publish for the more mathematically sophisticated end-user any specifications or data on such functions, the algorithms used to approximate them, and estimated error, be it relative or absolute? I must admit that I really put trust in the precision of the Excel functions until I had trouble replicating fits done elsewhere, and then noticed that my supposed full precision comparator was not so precise at all.

I hope this query generates thoughtful replies. I am learning that serious HP users seem to have a broader interest in software, programming, and numerical analysis, so this admittedly general question shouldn't seem to out of place here, I hope!

grateful and eager, as always,

Les


#3

Les said:

But my question is this: Does Microsoft or anyone else publish for the more mathematically sophisticated end-user any specifications or data on such functions, the algorithms used to approximate them, and estimated error, be it relative or absolute?

You should go ask this question on the newsgroup sci.math.num-analysis.

Also, a web search using the search phrase "accuracy of excel" turned up the following (and much more):

http://www.stat.uni-muenchen.de/~knuesel/elv/excelacc.pdf

http://faculty.smu.edu/ngh/stat6304_05/class_excel.pdf

http://www.informs-sim.org/wsc04papers/198.pdf


#4

Quote:
You should go ask this question on the newsgroup sci.math.num-analysis.

I know you are right, but I posted here because I like the largely benevolent climate and the hp-user/programmer focus.

Those links are very helpful. I have Excel 2000 (I just cannot stomach adding to Bill Gates's billions to upgrade), and I am concluding that for any serious computation the application is, in a word, fecal material, especially if you really expect full double precision.

Les

#5

Professor Kahan has also had a look at Excel in How Futile are Mindless Assessments of Roundoff in Floating-Point Computation ? (the paper also deals with MATLAB and compilers).

The particular problem here is Excel trying to cover up the fact that it uses binary arithmetic by cosmetically rounding everything the user sees to 15 digits. That doesn't always work.

#6

The best stats and report plotting language I have found is 'R' it's a GNU version of 'S' ( which means it's free - and available on almost every platform ) http://cran.us.r-project.org/.
There is also a very active user group that will help with any problems.

It handles data.frame structures which are are selectable data structures similar to excel cells, but far more flexible. see http://cran.r-project.org/doc/contrib/usingR.pdf

The NORMT3 package contains the erf commands your looking at http://cran.r-project.org/doc/packages/NORMT3.pdf

Converting you data to R is easy If you export your excel data as .csv you can read it in using the read.csv command. ( see http://cran.r-project.org/doc/manuals/R-data.html )

Once you start handling your data programatically you will never want to use Excel again.


#7

R is extremely impressive from what I can tell. It is command line oriented so it will take me some time to learn--but then again so is Maple and I got used to that in time.

Thank you so much for the reference. I had never heard of it before.

Les


Possibly Related Threads…
Thread Author Replies Views Last Post
  MS advert shows spreadsheet with obvious error BruceH 3 2,255 11-14-2013, 09:50 AM
Last Post: Bill (Smithville, NJ)
  [41CL] New Extra Functions version Monte Dalrymple 0 1,162 11-08-2013, 04:32 PM
Last Post: Monte Dalrymple
  HP Prime: in need of help with defining functions Alberto Candel 14 4,520 10-27-2013, 10:48 AM
Last Post: Alberto Candel
  HP Prime: Rounding error in determinant Stephan Matthys 3 1,706 10-25-2013, 09:29 PM
Last Post: Walter B
  Prime Error or Mine? toml_12953 12 3,810 10-22-2013, 10:35 AM
Last Post: toml_12953
  Explaination on How to Reset Caculator in Users guie: error Harold A Climer 5 2,653 10-15-2013, 02:11 AM
Last Post: cyrille de Brébisson
  Repair of HP-34C - Error 0 and Error 9 Jeff Kearns 3 1,755 10-11-2013, 12:29 PM
Last Post: Randy
  HP Prime spreadsheet functions SanS 0 1,948 10-04-2013, 04:23 AM
Last Post: SanS
  Stats functions on the HP34S Nicholas van Stigt 5 2,105 09-24-2013, 02:45 AM
Last Post: Nick_S
  Trig Functions Howard Owen 11 3,717 09-16-2013, 02:53 PM
Last Post: Fred Lusk

Forum Jump: