Page 461 - Excel for Scientists and Engineers: Numerical Methods

43 8 EXCEL: NUMERICAL METHODS

3. Set up spreadsheet as in Figure 10-17. This system is very sensitive to
changes in y"; sometimes Goal Seek fails to converge. You may have to
provide some manual guidance.

4. Set up spreadsheet as in problem 3.

5. Set up spreadsheet as in problem 3.

6. Set up spreadsheet as in problem 3.

Chapter 12 PDEs

1. Set up spreadsheet as in Figure 12-2.

2. Set up spreadsheet as in Figure 11-2, but with additional temperature
constants as described in the problem.

Chapter 13 Linear Regression

1. Insert columns for x2 and x3, then use LINEST. Answer: a = 0.00141 f
0.0005, b = -0.193 f 0.019, c = 13.28 f 0.19, d = 0.079 f 0.498, R2 =
0.999986.

2. The constant term d has a standard error much larger than its value; therefore
it should be eliminated from the model. Fitting the data toy = ax3 + bx2 + cx
gives a slightly better R2 value.

3. The answer spreadsheet shows the results from Trendline and also how to get
the regression parameters of a power function using LINEST.

4. The LINEST formula in this example uses an array constant to produce the
squared and cubed values of the known-x's. (Answers: 33.3 wt%, 2.3'F; 42.3
wt%, -12.6'F)

5. The LINEST formula in this example uses an array constant to produce the
values of known-x's raised to the required powers. (Answers: 33.3 wtYo,
2.3"F; 42.3 wt%, -12.6'F)

6. I first made a 3-D plot of the data. The shape of the surface (smooth upward
curvature) suggested to me that the data vs. each independent variable could
be a simple function, perhaps exponential or polynomial. I created XY plots
of Power vs. Throttle and Power vs. Speed and experimented; quadratic or