Page 62 - Numerical Analysis Using MATLAB and Excel

Approximations with Spreadsheets

Solution:

We start with a blank worksheet. In an Excel worksheet, a selected cell is surrounded by a heavy
border. We select a cell by moving the thick hollow white cross pointer to the desired cell and we
click. For this example, we first select A1 and we type x. We observe that after pressing the
<enter> key, the next cell moves downwards to A2; this becomes the next selected cell. We type
0.00 in A2. We observe that this value is displayed just as 0, that is, without decimals. Next, we
type 0.05 in A3. We observe that this number is displayed exactly as it was typed.

We will enter more values in column A, and to make all values look uniform, we click on letter A
on top of column A. We observe that the entire column is now highlighted, that is, the back-
ground on the monitor has changed from white to black. Next, from the Tools drop menu of the
Menu bar, we choose Options and we click on the Edit tab. We click on the Fixed Decimal check
box to place a check mark and we choose 2 as the number of decimal places. We repeat these
steps for Column B and we choose 3 decimal places. Then, all numbers that we will type in Col-
umn A will be fixed numbers with two decimal places, and the numbers in Column B will be fixed
with three decimal places.

To continue, we select A2, we click and holding the mouse left button down, we drag the mouse
down to A3 so that both these two cells are highlighted; then we release the mouse button.
When properly done, A2 will have a white background but A3 will have a black background. We
*
will now use the AutoFill feature to fill−in the other values of in Column A. We will use valuesx
in 0.05 increments up to 5.00. Column A now contains 100 values of from 0.00 to 5.00 in incre-
x
ments of 0.05.

Next, we select B1, and we type f(x). In B2, we type the equation formula with the = sign in front
of it, that is, we type

= A2^3-7*A2^2 + 16*A2-2
where A2 represents the first value of x = 0.00 . We observe that B2 displays the value 12.000– .
This is the value of fx() when x = 0.00 Next, we want to copy this formula to the range
B3:B102 (the colon : means B3 through B102). With B2 still selected, we click on Edit on the
main taskbar, and we click on Copy. We select the range B3:B102 with the mouse, we release the
mouse button, and we observe that this range is now highlighted. We click on Edit, then on Paste
and we observe that this range is now filled with the values of fx() . Alternately, we can use the
Copy and Paste icons of the taskbar.

* To use this feature, we highlight cells A2 and A3. We observe that on the lower right corner of A3, there is a small black
square; this is called the fill handle. If it does not appear on the spreadsheet, we can make it visible by performing the
sequential steps Tools>Options, select the Edit tab, and place a check mark on the Drag and Drop setting. Next, we point
the mouse to the fill handle and we observe that the mouse pointer appears as a small cross. We click, hold down the mouse
button, we drag it down to A102, and we release the mouse button. We observe that, as we drag the fill handle, a pop−up
note shows the cell entry for the last value in the range.

Numerical Analysis Using MATLAB® and Excel®, Third Edition 2−9
Copyright © Orchard Publications