Don't trust MS Excel when it comes to accuracy

[DA74254]

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!).

[Massimo Gnerucci]

Try with GAMMALN.PRECISE

[DA74254]

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).

[Massimo Gnerucci]

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.

[DA74254]

(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.

[J-F Garnier]

(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

[Maximilian Hohmann]

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

[J-F Garnier]

(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

[Massimo Gnerucci]

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!

[ijabbott]

So if you save an Excel spreadsheet containing localized function names, will it open and work in an environment that has different locale settings?

[Massimo Gnerucci]

(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.

[JoJo1973]

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.

[DA74254]

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.

[J-F Garnier]

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