Some time ago, I read the interesting blog post „Function Improvements in Excel 2010” on the Microsoft Excel Team Blog. They report about the changed formulas in Excel 2010 and about the improvements made to the calculation algorithms for these formulas. I’m not a statistician and I can’t really use all those statistical formulas, but I’m curious and I would like to examine how the results returned by the affected formulas differ in the different Excel Versions. For doing this, I have choosen the GAMMALN() function because this formula just only needs one argument. You can download a sample workbook at the end of this article.
In a first step I have created a simple list of values in Excel 2010, as shown here in the following figure:
Each value in the list increases by a variable step. The steps can be 0.000001, 0.00001, 0.0001, 0.001, 0.01, 0.1 and 1. Then I calculated the result of GAMMALN() for each value, as you can see in the figure above. I repeated the calculations in Excel 2003 and Excel 2007 and then copied the results to the Excel 2010 sheet. And I formatted the results with a custom format showing 30 decimal places. Then, it was easy to calculate and visualize the differences between the results from Excel 2010, 2007 and 2003 as shows the following screenshot:
As you can see and as expected, the results do not differ between Excel 2007 and Excel 2003. But they differ between Excel 2010 and the older version. The following chart visualizes the results for Excel 2010 and Excel 2007. Please note, that I have added a constant value to the results from Excel 2010 on order to prevent that the curves lie on.
As you can see, the curve progression is quite similar; this can be explained by the very small differences by the results from the different Excel versions. Following a few more variations of the curves, each with different ranges.
In order to visualize the differences between the calculation results from Excel 2010 and previous versions, I also created a chart for these values. And I created a supplementary curve representing the values calculated by (1 – Value_From_2007 / Value_From_2010). However this only works if the result from the GAMMALN(x) functions is not null; this is (correctly) the case for x = 1 and x = 2. In this case, I used 1 / 10 ^ 30 as result. The following figure shows the chart for a step of 0.001:
The two peaks for the orange curve are there corresponding to the results of GAMMALN(1) and GAMMALN(2). And also nice to see is that the difference between the values initially decreases, then increases again negative. Following some versions of the chart with different step sizes.
You can find on the website „“ an online calculator for the GAMMALN() function. I tried out some values in the calculator, including 1 and 2. In all cases, I got the same values in Excel 2010 in relation to its accuracy.
As mentionned before, I am not a statistician, and this article should also not meet higher science requirements, but for me, it is clear that significant changes have been made to the GAMMALN() function; that was what I wanted to find out. Please click on the following link to download the sample worksheet.
This article has also been published in German and in English on the following sites: