Don't trust MS Excel when it comes to accuracy

06042023, 04:42 PM
Post: #1




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:
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 MK52 & MK61 

06042023, 05:06 PM
Post: #2




RE: Don't trust MS Excel when it comes to accuracy
Try with GAMMALN.PRECISE
Greetings, Massimo +×÷ ↔ left is right and right is wrong 

06042023, 05:57 PM
Post: #3




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 MK52 & MK61 

06042023, 06:19 PM
(This post was last modified: 06042023 06:21 PM by Massimo Gnerucci.)
Post: #4




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 

06042023, 06:31 PM
Post: #5




RE: Don't trust MS Excel when it comes to accuracy
(06042023 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 MK52 & MK61 

06052023, 12:53 PM
Post: #6




RE: Don't trust MS Excel when it comes to accuracy
(06042023 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. 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. JF 

06052023, 01:51 PM
Post: #7




RE: Don't trust MS Excel when it comes to accuracy
Hello,
(06052023 12:53 PM)JF 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 

06052023, 02:01 PM
(This post was last modified: 06052023 02:04 PM by JF Garnier.)
Post: #8




RE: Don't trust MS Excel when it comes to accuracy
(06052023 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 Frenchlocalized Excel, they are LNGAMMA and LNGAMMA.PRECIS. Note that "LN" and "GAMMA" are swapped in French, I had to find out that too. JF 

06052023, 02:10 PM
(This post was last modified: 06052023 02:12 PM by Massimo Gnerucci.)
Post: #9




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 

06052023, 08:29 PM
Post: #10




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 

06052023, 08:38 PM
(This post was last modified: 06052023 09:13 PM by Massimo Gnerucci.)
Post: #11




RE: Don't trust MS Excel when it comes to accuracy
(06052023 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 

06052023, 09:59 PM
Post: #12




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. 

06062023, 04:59 PM
(This post was last modified: 06062023 05:00 PM by DA74254.)
Post: #13




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 MK52 & MK61 

06062023, 06:29 PM
Post: #14




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 nonenglish 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. JF 

« Next Oldest  Next Newest »

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