Subscribe Feed (RSS)Excel Rounding
September 13th, 2009 by Math Tricks | Filed under Bad Math.
Excel Rounding
In my line of work, we generate polynomial curves very often. It is important for us to have software that will generate polynomial equations for the data that we generate – equations for the standard curves that we generate so that we can determine values for our unknown samples.
This is both an example of real life math and bad math. To start, I’ll just say that we used to generate linear curves for our assays. The data for these assays were analyzed in Microsoft Excel, and we were very satisfied with the linear curves generated by Excel.
Last year, we changed our assay such that the standard curve that was generated was no longer linear. The new type of curve was a second-order polynomial (of the form AX2 + BX + C). So, OK, Excel can handle polynomial data, right? Well, we discovered a problem with Excel – Excel rounding of the polynomial coefficients! The problem seems to exist with only large coefficients, but we do deal with polynomials with large coefficients every day.
To illustrate the problem, I generated some data for the equation:
Y = 8386111X2 + 24421000X + 0.058
Here is a table of data generated using this equation:
|
X |
Y |
|
0.00003 |
732.6955475 |
|
0.00033 |
8059.901247 |
|
0.00333 |
81414.98075 |
|
0.03333 |
823268.0256 |
|
0.33333 |
9072023.463 |
|
3.33333 |
174582076.7 |
So far so good. OK, so using Excel (Microsoft Office 2003), I generated this polynomial curve:
As you can see, the equation of the curve generated by Excel is not the same as the equation that we used to generate the data used in our example. There was some Excel rounding of the coefficients A and B:
Y = 8386111X2 + 24421000X + 0.058 equation used to generate data
Y = 8E+06X2 + 2E+07X + 0.058 equation generated by Excel
Close, but no cigar! In our line of work, we require precise and accurate data, ant this Excel rounding problem is unacceptable. We just got in Office 2007, but the problem still exists there too. We are now using GraphPad, which does not round.
Well, as it is getting close to Halloween, I thought it appropriate to write about this example of Microsoft math tricks – not treats. Has anybody else discovered any limitations with Excel? Please feel free to post!
Tags: Bad Math, excel rounding, Math Tricks, Real Life Math
Yes i faced similar problem,i need to coorelate some set of data and i used excel 6th order polynomial equation but its own equation didnt work even taking values from the data set.
but till now i didnt get a solution
Hi Tinsae,
Yea, I am so glad we got GraphPad – it really works great. I’m sure there are other nice graphing programs out there, but we just don’t have the time or money to try others out.
Hi,
it’s just a problem of excel not showing the correct coefficients. Try right-clicking the label with the equation, then choose the option to format it (not using english excel, sry) and change the design to “number” with the desired amount of digits.
The numbers are still off, but way better than 8E+06. Seriously, Excel is bad for scientifical use. Every spreadsheet application should be able to determine 2nd-order polynomials correctly for being considered to use. I never use it but to take a first look, and most of the time is use GNUplot even for that purpose.
Think it’s called office because its for office use, not for mathematical or scientifical use ;)