Executioner
Board Regular
- Joined
- Sep 26, 2005
- Messages
- 166
I have a spreadsheet that I use to perform a regression analysis to the 6th polynomial. Only the x range are replaced and the regression recalculated, but under 2003, the values are not correct, as the intercept goes negative and does not calculate my values correctly. The values in the B column are the only values that get updated each time, and the regression is over-written each time to get a new set of values.
Here is the basic set of data going to the 6 power.
Under Excel 97, I get these results:
Under Excel 2000, the values are correct:
Now under Excel 2003, I get this:Note the negative intercept value...
I even tried Excel 2007 and that version works, but the intercept is a different value, and the end results in the calculation for temperature are just a tad different then 97 and 2000.
Even though the intercept is a different value, its better than the 2003 version. What would cause such a large disparity between 2003 and the others? Finally, I use this to calculate what the setting needs to be on the controller knob to obtain the correct temperature:
Here is the basic set of data going to the 6 power.
| 56685 on XP using Excel97.xls | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 2 | CONTROLLER | ACTUAL | B^2 | B^3 | B^4 | B^5 | B^6 | ||
| 3 | SETTING | TEMPC | |||||||
| 4 | 300 | 299.6 | 89760.16 | 26892143.94 | 8056886323 | 2.41384E+12 | 7.23187E+14 | ||
| 5 | 450 | 449.7 | 202230.09 | 90942871.47 | 40897009301 | 1.83914E+13 | 8.27061E+15 | ||
| 6 | 600 | 597.5 | 357006.25 | 213311234.4 | 1.27453E+11 | 7.61534E+13 | 4.55017E+16 | ||
| 7 | 750 | 746.2 | 556814.44 | 415494935.1 | 3.10042E+11 | 2.31354E+14 | 1.72636E+17 | ||
| 8 | 900 | 894.4 | 799951.36 | 715476496.4 | 6.39922E+11 | 5.72346E+14 | 5.11907E+17 | ||
REGRESSTION | |||||||||
Under Excel 97, I get these results:
| 56685 on XP using Excel97.xls | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | |||
| 9 | SUMMARYOUTPUT | ||||||||||
| 10 | RegressionStatistics | ||||||||||
| 11 | MultipleR | 1 | |||||||||
| 12 | RSquare | 1 | |||||||||
| 13 | AdjustedRSquare | -4.65661E-10 | |||||||||
| 14 | StandardError | 2.69374E-15 | |||||||||
| 15 | Observations | 5 | |||||||||
| 16 | ANOVA | ||||||||||
| 17 | df | SS | MS | F | SignificanceF | ||||||
| 18 | Regression | 6 | 225000 | 37500 | 5.16796E+33 | #NUM! | |||||
| 19 | Residual | 4294967294 | 3.11654E-20 | 7.25625E-30 | |||||||
| 20 | Total | 4294967300 | 225000 | ||||||||
| 21 | |||||||||||
| 22 | Coefficients | StandardError | tStat | P-value | Lower95% | Upper95% | Lower95.0% | Upper95.0% | |||
| 23 | Intercept | 132.5212869 | 4.46611E-09 | 29672636790 | 0 | 132.5212869 | 132.5212869 | 132.5213 | 132.5213 | ||
| 24 | XVariable1 | -0.12317482 | 5.34344E-11 | -2305160688 | 0 | -0.12317482 | -0.12317482 | -0.12317 | -0.12317 | ||
| 25 | XVariable2 | 0.003406501 | 2.39766E-13 | 14207597565 | 0 | 0.003406501 | 0.003406501 | 0.003407 | 0.003407 | ||
| 26 | XVariable3 | -4.1965E-06 | 4.99583E-16 | -8400004010 | 0 | -4.1965E-06 | -4.1965E-06 | -4.2E-06 | -4.2E-06 | ||
| 27 | XVariable4 | 8.90358E-10 | 4.26524E-19 | 2087475828 | 0 | 8.90358E-10 | 8.90358E-10 | 8.9E-10 | 8.9E-10 | ||
| 28 | XVariable5 | 2.15926E-12 | 0 | 65535 | #NUM! | 2.15926E-12 | 2.15926E-12 | 2.16E-12 | 2.16E-12 | ||
| 29 | XVariable6 | -1.27073E-15 | 0 | 65535 | #NUM! | -1.27073E-15 | -1.27073E-15 | -1.3E-15 | -1.3E-15 | ||
REGRESSTION | |||||||||||
Under Excel 2000, the values are correct:
| 56685 on XP using Excel2000.xls | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | |||
| 12 | RegressionStatistics | ||||||||||
| 13 | MultipleR | 1 | |||||||||
| 14 | RSquare | 1 | |||||||||
| 15 | AdjustedRSquare | -4.65661E-10 | |||||||||
| 16 | StandardError | 2.69374E-15 | |||||||||
| 17 | Observations | 5 | |||||||||
| 18 | |||||||||||
| 19 | ANOVA | ||||||||||
| 20 | df | SS | MS | F | SignificanceF | ||||||
| 21 | Regression | 6 | 225000 | 37500 | 5.16796E+33 | #NUM! | |||||
| 22 | Residual | 4294967294 | 3.11654E-20 | 7.25625E-30 | |||||||
| 23 | Total | 4294967300 | 225000 | ||||||||
| 24 | |||||||||||
| 25 | Coefficients | StandardError | tStat | P-value | Lower95% | Upper95% | Lower95.0% | Upper95.0% | |||
| 26 | Intercept | 132.5212869 | 4.46611E-09 | 29672636790 | 0 | 132.5212869 | 132.5212869 | 132.5213 | 132.5213 | ||
| 27 | XVariable1 | -0.12317482 | 5.34344E-11 | -2305160688 | 0 | -0.12317482 | -0.12317482 | -0.12317 | -0.12317 | ||
| 28 | XVariable2 | 0.003406501 | 2.39766E-13 | 14207597565 | 0 | 0.003406501 | 0.003406501 | 0.003407 | 0.003407 | ||
| 29 | XVariable3 | -4.1965E-06 | 4.99583E-16 | -8400004010 | 0 | -4.1965E-06 | -4.1965E-06 | -4.2E-06 | -4.2E-06 | ||
| 30 | XVariable4 | 8.90358E-10 | 4.26524E-19 | 2087475828 | 0 | 8.90358E-10 | 8.90358E-10 | 8.9E-10 | 8.9E-10 | ||
| 31 | XVariable5 | 2.15926E-12 | 0 | 65535 | #NUM! | 2.15926E-12 | 2.15926E-12 | 2.16E-12 | 2.16E-12 | ||
| 32 | XVariable6 | -1.27073E-15 | 0 | 65535 | #NUM! | -1.27073E-15 | -1.27073E-15 | -1.3E-15 | -1.3E-15 | ||
REGRESSTION | |||||||||||
Now under Excel 2003, I get this:Note the negative intercept value...
| 56685 on XP using Excel2003.xls | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | |||
| 10 | SUMMARYOUTPUT | ||||||||||
| 11 | |||||||||||
| 12 | RegressionStatistics | ||||||||||
| 13 | MultipleR | 1 | |||||||||
| 14 | RSquare | 1 | |||||||||
| 15 | AdjustedRSquare | -4.65661E-10 | |||||||||
| 16 | StandardError | 0 | |||||||||
| 17 | Observations | 5 | |||||||||
| 18 | |||||||||||
| 19 | ANOVA | ||||||||||
| 20 | df | SS | MS | F | SignificanceF | ||||||
| 21 | Regression | 6 | 225000 | 37500 | #NUM! | #NUM! | |||||
| 22 | Residual | 4294967295 | 0 | 0 | |||||||
| 23 | Total | 4294967301 | 225000 | ||||||||
| 24 | |||||||||||
| 25 | Coefficients | StandardError | tStat | P-value | Lower95% | Upper95% | Lower95.0% | Upper95.0% | |||
| 26 | Intercept | -1374.204791 | 0 | 65535 | #NUM! | -1374.204791 | -1374.204791 | -1374.2 | -1374.2 | ||
| 27 | XVariable1 | 0 | 0 | 65535 | #NUM! | 0 | 0 | 0 | 0 | ||
| 28 | XVariable2 | 0 | 0 | 65535 | #NUM! | 0 | 0 | 0 | 0 | ||
| 29 | XVariable3 | 1.11102E-05 | 0 | 65535 | #NUM! | 1.11102E-05 | 1.11102E-05 | 1.11E-05 | 1.11E-05 | ||
| 30 | XVariable4 | -2.87488E-08 | 0 | 65535 | #NUM! | -2.87488E-08 | -2.87488E-08 | -2.9E-08 | -2.9E-08 | ||
| 31 | XVariable5 | 2.88772E-11 | 0 | 65535 | #NUM! | 2.88772E-11 | 2.88772E-11 | 2.89E-11 | 2.89E-11 | ||
| 32 | XVariable6 | 0 | 0 | 65535 | #NUM! | 0 | 0 | 0 | 0 | ||
REGRESSTION | |||||||||||
I even tried Excel 2007 and that version works, but the intercept is a different value, and the end results in the calculation for temperature are just a tad different then 97 and 2000.
| 56685 using Excel2007 saved as 2003 format.xls | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | |||
| 12 | RegressionStatistics | ||||||||||
| 13 | MultipleR | 1 | |||||||||
| 14 | RSquare | 1 | |||||||||
| 15 | AdjustedRSquare | -4.65661E-10 | |||||||||
| 16 | StandardError | 0 | |||||||||
| 17 | Observations | 5 | |||||||||
| 18 | |||||||||||
| 19 | ANOVA | ||||||||||
| 20 | df | SS | MS | F | SignificanceF | ||||||
| 21 | Regression | 6 | 225000 | 37500 | #NUM! | #NUM! | |||||
| 22 | Residual | 4294967295 | 0 | 0 | |||||||
| 23 | Total | 4294967301 | 225000 | ||||||||
| 24 | |||||||||||
| 25 | Coefficients | StandardError | tStat | P-value | Lower95% | Upper95% | Lower95.0% | Upper95.0% | |||
| 26 | Intercept | 170.7034746 | 0 | 65535 | #NUM! | 170.7034746 | 170.7034746 | 170.7035 | 170.7035 | ||
| 27 | XVariable1 | 0 | 0 | 65535 | #NUM! | 0 | 0 | 0 | 0 | ||
| 28 | XVariable2 | 0 | 0 | 65535 | #NUM! | 0 | 0 | 0 | 0 | ||
| 29 | XVariable3 | 1.11102E-05 | 0 | 65535 | #NUM! | 1.11102E-05 | 1.11102E-05 | 1.11E-05 | 1.11E-05 | ||
| 30 | XVariable4 | -2.87488E-08 | 0 | 65535 | #NUM! | -2.87488E-08 | -2.87488E-08 | -2.9E-08 | -2.9E-08 | ||
| 31 | XVariable5 | 2.88772E-11 | 0 | 65535 | #NUM! | 2.88772E-11 | 2.88772E-11 | 2.89E-11 | 2.89E-11 | ||
| 32 | XVariable6 | -1.04522E-14 | 0 | 65535 | #NUM! | -1.04522E-14 | -1.04522E-14 | -1E-14 | -1E-14 | ||
REGRESSTION | |||||||||||
Even though the intercept is a different value, its better than the 2003 version. What would cause such a large disparity between 2003 and the others? Finally, I use this to calculate what the setting needs to be on the controller knob to obtain the correct temperature:
Last edited:
