Don't trust MS Excel when it comes to accuracy
06-04-2023, 04:42 PM
Post: #1
 DA74254 Member Posts: 174 Joined: Sep 2017
Don't trust MS Excel when it comes to accuracy
So, I found out that MS Excel has the GammaLN function.
In the WP43 manual, there is an example on how to use the GammaLN to calculate really large Factorials (X!).

Both Excel and Libreoffice Calc claims to have 15 digits accuracy. Not remotely as accurate as SM/WP calcs, of course.

Anyway, just to test the routine, I dialled in GammaLN(51) in order to calculate 50!.
While the corrct answer, confirmed with both Free/Plus42 and WP43, is about 3,041409320171338x10^64, Excel comes up with 3,0044...x10^64.
Libreoffice comes up with: 3,04140932017131x10^64.
Notice that the last digit (1, instead of 3) is incorrect. Excel cr*pped out at the second digit..

Code used for the routine:
GammaLN(51)/LN(10)
FP
10^FP

Or, to put it in RPN routine:
Code:
 LBL 'BigFac' 1 + lnGamma 10 ln / Enter IP x><Y FP 10^x RTN END
Note that this code works on WP43 or C47
The "1+" is so that you insert the number you would factorial (Gamma+1=X!).

Esben
28s, 35s, 49G+, 50G, Prime G2 HW D, SwissMicros DM42, WP43 Pilot
Elektronika MK-52 & MK-61
06-04-2023, 05:06 PM
Post: #2
 Massimo Gnerucci Senior Member Posts: 2,620 Joined: Dec 2013
RE: Don't trust MS Excel when it comes to accuracy
Try with GAMMALN.PRECISE

Greetings,
Massimo

-+×÷ ↔ left is right and right is wrong
06-04-2023, 05:57 PM
Post: #3
 DA74254 Member Posts: 174 Joined: Sep 2017
RE: Don't trust MS Excel when it comes to accuracy
Hrmpff!
What's the point in having 2 slightly different functions that do the same, only to differ between 14 and 16 digits precision? Must be an MS thing. Yes, Libreoffice also have the GammaLN.Precise, but I just assume it is for compatibility as the GammaLN function in LO Calc gets it precise in the first place.

I would assume that people using GammaLN functions are a tad more demanding than the common "let's use a calculator to split the bill" type of people and thus expects highest precision by default. (I know I do).

Esben
28s, 35s, 49G+, 50G, Prime G2 HW D, SwissMicros DM42, WP43 Pilot
Elektronika MK-52 & MK-61
06-04-2023, 06:19 PM (This post was last modified: 06-04-2023 06:21 PM by Massimo Gnerucci.)
Post: #4
 Massimo Gnerucci Senior Member Posts: 2,620 Joined: Dec 2013
RE: Don't trust MS Excel when it comes to accuracy
Don't ask me why, I can only speculate.

It appeared in Excel 2010, probably to increase precision, and they left also the old one for compatibility with previous results...

But who knows? Not me.

Greetings,
Massimo

-+×÷ ↔ left is right and right is wrong
06-04-2023, 06:31 PM
Post: #5
 DA74254 Member Posts: 174 Joined: Sep 2017
RE: Don't trust MS Excel when it comes to accuracy
(06-04-2023 06:19 PM)Massimo Gnerucci Wrote:  Don't ask me why, I can only speculate.

Heh, I didn't really "demand" you answer the MS thinking behind this. It was just a general rant.
Glad I never paid for MS Office. Privately I'm using LibreOffice but my work uses MS Office.

Esben
28s, 35s, 49G+, 50G, Prime G2 HW D, SwissMicros DM42, WP43 Pilot
Elektronika MK-52 & MK-61
06-05-2023, 12:53 PM
Post: #6
 J-F Garnier Senior Member Posts: 845 Joined: Dec 2013
RE: Don't trust MS Excel when it comes to accuracy
(06-04-2023 04:42 PM)DA74254 Wrote:  Both Excel and Libreoffice Calc claims to have 15 digits accuracy. Not remotely as accurate as SM/WP calcs, of course.

Anyway, just to test the routine, I dialled in GammaLN(51) in order to calculate 50!.
While the corrct answer, confirmed with both Free/Plus42 and WP43, is about 3,041409320171338x10^64, Excel comes up with 3,0044...x10^64.
Libreoffice comes up with: 3,04140932017131x10^64.
Notice that the last digit (1, instead of 3) is incorrect. Excel cr*pped out at the second digit..

Looks like basic MS bashing...
I can't reproduce your "so bad result" on my old Excel 2010
Both GAMMALN(51) and GAMMALN.PRECISE(51) give 148.477766951773
and EXP(GAMMALN(51)) = 3.0414093201713 e+64
Where is the bug? :-(

Don't trust without checking.

J-F
06-05-2023, 01:51 PM
Post: #7
 Maximilian Hohmann Senior Member Posts: 1,147 Joined: Dec 2013
RE: Don't trust MS Excel when it comes to accuracy
Hello,

(06-05-2023 12:53 PM)J-F Garnier Wrote:  I can't reproduce your "so bad result" on my old Excel 2010

Me neither. I have Excel 2011 for the Macintosh and get your exactly same results, not different with or without the ".PRECISE" attribute. However it took me a while to find out that I need to enter "GAMMALN.GENAU" into my german localised Excel...

Regards
Max
06-05-2023, 02:01 PM (This post was last modified: 06-05-2023 02:04 PM by J-F Garnier.)
Post: #8
 J-F Garnier Senior Member Posts: 845 Joined: Dec 2013
RE: Don't trust MS Excel when it comes to accuracy
(06-05-2023 01:51 PM)Maximilian Hohmann Wrote:  However it took me a while to find out that I need to enter "GAMMALN.GENAU" into my german localised Excel...

On my French-localized Excel, they are LNGAMMA and LNGAMMA.PRECIS.
Note that "LN" and "GAMMA" are swapped in French, I had to find out that too.

J-F
06-05-2023, 02:10 PM (This post was last modified: 06-05-2023 02:12 PM by Massimo Gnerucci.)
Post: #9
 Massimo Gnerucci Senior Member Posts: 2,620 Joined: Dec 2013
RE: Don't trust MS Excel when it comes to accuracy
Me too, of course, with LN.GAMMA.PRECISA() and LN.GAMMA()
I also have a dot between ln and gamma...

Translating formulas in Excel is one of the stupidest things!

Greetings,
Massimo

-+×÷ ↔ left is right and right is wrong
06-05-2023, 08:29 PM
Post: #10
 ijabbott Senior Member Posts: 1,262 Joined: Jul 2015
RE: Don't trust MS Excel when it comes to accuracy
So if you save an Excel spreadsheet containing localized function names, will it open and work in an environment that has different locale settings?

— Ian Abbott
06-05-2023, 08:38 PM (This post was last modified: 06-05-2023 09:13 PM by Massimo Gnerucci.)
Post: #11
 Massimo Gnerucci Senior Member Posts: 2,620 Joined: Dec 2013
RE: Don't trust MS Excel when it comes to accuracy
(06-05-2023 08:29 PM)ijabbott Wrote:  So if you save an Excel spreadsheet containing localized function names, will it open and work in an environment that has different locale settings?

Yes, they are internally stored the same and, once inspected, show up with the localized name.

Greetings,
Massimo

-+×÷ ↔ left is right and right is wrong
06-05-2023, 09:59 PM
Post: #12
 JoJo1973 Member Posts: 106 Joined: Apr 2016
RE: Don't trust MS Excel when it comes to accuracy
If you know the english name of the function and you nedd a quick way to translate in your locale, just use VBA:

Code:
[a1].formula="=gammaln.precise(51)"

Now you can inspect cell A1 to know the localized name of your favourite function.
06-06-2023, 04:59 PM (This post was last modified: 06-06-2023 05:00 PM by DA74254.)
Post: #13
 DA74254 Member Posts: 174 Joined: Sep 2017
RE: Don't trust MS Excel when it comes to accuracy
I have to apologize to you all and to MS.
The error was a classic PEBKAC coupled up with clumsy fingers and a bit too quick on the shortcuts in the formulaes.

The error was produced by me and myself by taking the FP of the GammaLN value and *NOT* the FP of the GammaLN/LN value.

Esben
28s, 35s, 49G+, 50G, Prime G2 HW D, SwissMicros DM42, WP43 Pilot
Elektronika MK-52 & MK-61
06-06-2023, 06:29 PM
Post: #14
 J-F Garnier Senior Member Posts: 845 Joined: Dec 2013
RE: Don't trust MS Excel when it comes to accuracy
Yeah no problem :-)

MS and Excel are not free of defaults (ah, that translation of function names, for us non-english users) but the bug was just too surprising, too big, and was worth a double check.

And by a curious coincidence, FP(GammaLN(51))=0.477... and FP(GammaLN(51)/LN(10))=0.483... are close so a confusion was easy.

J-F
 « Next Oldest | Next Newest »

User(s) browsing this thread: 1 Guest(s)