Exponential best fit function difference between Excel and Calc

Microsoft Excel 2007 (Microsoft Office 12) and The Document Foundation LibreOffice 3.3.2 generate mathematically equivalent but different functions for the exponential fit. In addition, the power function in LibreOffice automatically excludes x=0, while in Excel the user must delete the x=0 data point.

The exponential fit data derived from an exercise involving a bouncing ball. A high bounce ball was dropped from 100 centimeters and the height of the nth bounce was recorded.

Bounce Height
0 100
1 75
2 63
3 40
4 37
5 26
6 19
7 12
8 10
9 8
10 5

For the bouncing ball problem, the height of each subsequent bounce is a percentage of the previous bounce. Thus the function is a multiplication of the percentage bounce or f(x) = (percentage)^x. LibreOffice.org Calc does a nice job of finding this function for the data.

In the above chart the ball is bouncing to a height of 74% of the previous bounce.When one attempts to create the same chart in Microsoft Excel 12 (Excel 2007), the result is a mathematically equivalent but less scientifically satisfactory exponential decay using the base e.

While mathematically equivalent, a base e function does not directly reveal the percentage value. One has to calculate e^(-0.30). Thus e^-0.30 ≈ 0.74, at least to within the rounding error.

Another difference between the Calc and Excel is that LibreOffice.org Calc ran a vbest fit using a power function on the original data. LibreOffice.org automatically discarded the mathematically problematic x = 0 value.

Microsoft Excel balked. A new table had to be constructed without the x = 0 value for Excel to proceed. One could argue that this is a preferred behavior: Excel is making no mathematical decisions without being explicitly told to do so. I, however, prefer the Calc approach. If I want a power function then I ought to already know that 0^-n ≠ 100, that the function is undefined at x = 0.

Popular posts from this blog

Box and whisker plots in Google Sheets

Traditional food dishes of Micronesia

Creating histograms with Google Sheets