263
264
265
266
267
268
269
270
3
271
272
273
274
310
275
277
279
276
278
280
281
282
27
287
36
283
284
286
285
7
8
3
15
288
289
290
291
3
292
293
3
311
294
295
296
297
298
67
299
36
300
7
8
1
2
3
4
5
301
7
8
1
-3
2
3
4
5
305
312
208
209
206
207
306
7
8
1
-3
2
0
3
15
307
4
48
308
5
105
309
258
228
3
229
230
250
231
232
233
3
251
234
3
235
236
237
238
239
302
304
303
240
241
242
243
252
253
244
245
174
175
176
27
177
178
179
36
-2
181
180
182
183
184
185
186
190
189
187
188
191
-2
-1.5
-1
-0.5
0
0.5
1
1.5
2
2.5
3
314
315
316
317
192
67
193
194
36
195
196
7
8
-2
-8
-1.5
197
-1
198
-0.5
199
0
0.5
1
1.5
2
2.5
3
200
201
202
203
204
205
208
209
206
207
119
0
1
2
104
215
9
10
11
7
8
-2
6
-1.5
3
-1
1
-0.5
0
0
0
0.5
1
1
3
1.5
6
2
10
12
13
14
210
15
16
3
4
17
5
6
211
18
19
20
21
22
105
112
113
114
67
115
36
127
116
7
8
117
-2
4
-8
-1.5
2.25
-3.375
-1
1
-1
-0.5
0.25
-0.125
0
0
0
0.5
0.25
0.125
1
1
1
1.5
2.25
3.375
2
4
8
313
3
125
126
118
120
121
122
123
124
0
1
213
214
104
216
9
10
11
217
218
7
8
-1
-0.5
-0.5
-0.66666666666666663
0
-1
0.5
-2
1
1.5
2
2
1
2.5
0.66666666666666663
3
0.5
12
13
14
210
219
220
7
8
0
-1
0.1
-1.1111111111111112
0.2
-1.25
0.3
-1.4285714285714286
0.4
-1.6666666666666667
0.5
-2
0.6
-2.5
0.7
-3.333333333333333
0.8
-5.0000000000000009
0.9
-10.000000000000002
1
1.1000000000000001
9.9999999999999911
1.2
5.0000000000000009
1.3
3.333333333333333
1.4
2.5000000000000004
1.5
2
1.6
1.6666666666666665
1.7
1.4285714285714286
1.8
1.25
1.9
1.1111111111111112
2
1
221
222
30
31
27
32
33
34
35
36
37
7
38
10
-2000
39
40
41
97
42
43
44
45
98
46
99
47
48
100
101
49
3
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
110
36
69
70
7
8
0
7
1
2
104
2
-1
9
10
3
-2
4
-1
5
2
6
7
111
71
72
73
7
8
2
-1
102
74
75
103
76
77
3
78
79
106
107
108
109
128
83
130
84
131
319
36
132
133
7
8
117
-3
-27
-8
-2.5
-15.625
-6.5
-2
-8
-5
-1.5
-3.375
-3.5
-1
-1
-2
-0.5
-0.125
-0.5
0
0
1
0.5
0.125
2.5
1
1
4
1.5
3.375
5.5
2
8
7
2.5
15.625
8.5
3
27
10
134
135
136
137
138
139
141
172
173
142
143
152
153
7
8
117
140
-2
-8
-5
-3
144
145
157
156
146
147
151
148
7
8
117
140
0
0
1
-1
149
150
3
154
155
1993
0
34
1994
1
36
1995
2
37
1996
3
40.81
1997
4
42.14
1998
5
44.52
96
320
321
322
323
324
325
326
327
328
329
330
16
34
18.5
86.5
22
111.1
27
113.9
32
84.5
37
35.4
42
6.8
171
168
335
332
333
334
331
336
337
338
339
340
169
341
343
342
344
345
346
335
331
338
347
341
348
349
350
351
170
To graph functions in Excel, you must first create a table of data with the information about the x and y
values. You then use Chart Wizard to create the plot. The following example will take you through the
process step by step.
Check it out
Modify the above table to graph the function
Make sure you change all the entries in the y-column. You can copy
the formula from the first y-column cell, then select the rest of the column
x
f(x)
on
Tables
We first create a table of x and y values. The y-values are given by f(x).
Next, select the entire table. A special macro called "grapher" has been written to plot the
table of values. Press the "grapher" button to the right side of the table. You should see a graph
similar to the one below. (The grapher button does not appear in printouts of worksheets.)
Click out of the graph that you created.
You can also click back into it and move, resize or delete it. Practice a little with your graph.
with the same set of x-values.
Remarks
Notice that you have to specify the x-range of values in order to create your table and
then the graph. You must pick a suitable x-range so that the salient features of the
graph are captured. This may take a few trials before you get a suitable range of
x-values.
Introduction to Excel
various components of the spreadsheet.
Graphs of Functions
Tables in Excel
Example 1
Finding Zeros of a Function with Goal Seek
Finding x-intercept of a line
To find the x-value where a function is zero, you can use a feature of Excel called Goal Seek.
The next example will tell you how to use Goal Seek.
Let the profit function for a company be given by p(x) = 200x - 4000,
where x denotes the number of items produced. The manufacturer
wants to know how many items to produce to break even. That is, she
wants to know when the profit will be zero.
Solution
First we make a table with x and the formula for p(x):
p(x)
Change the value of x and see what happens to p(x).
Now, we want to find the value of x such that p(x) = 0. Since this is a
linear equation, there will be only one such value.
Note: the boxes on the left
are just pictures! You need to
go to the Tools menu and start
Goal Seek to get the real thing.
type in 0. Hence, you should see the following:
Therefore, click into the blue cell, and the dialog box will automatically record
its cell reference. Your completed box should look like the following:
and yellow boxes for x and p(x) will be changed accordingly.
Scroll up to see what solution Goal Seek gave you.
You should get a value of x=20 to make p(x)=0. This
means that the company must make at least 20
products before realizing a positive amount of
profit.
Redo the problem by hand and recheck the solution.
Exercise
What is the break-even point if p(x) = 300x-8800?
Finding zeros of a parabola
You know from algebra that a parabola could have 0,1 or 2 x-intercepts. Also, Goal Seek
will return only one x-intercept at a time. Which one it returns depends on the initial value
of x which is already in the box when you start Goal Seek. In the previous example, we knew
there would only be one x-intercept, and since the function was linear, it did not matter
what value x had when starting.
Therefore, it is advisable to graph the function before starting Goal Seek. You can then
set the initial value for x close to the x-intercept you are interested in. We will illustrate this
in the next example.
Example 2
Find the zeros of the function .
We first make a table of values and then graph the function using
the grapher macro.
grapher macro. You will get a graph like the following:
We see that there is one x-intercept near 2 and another near 4. We can
start Goal Seek in the following table with the starting value of x=2.
given in the previous example. The box should look like the following after
you entered all pertinent data.
The x-intercept near 2 is approximately 1.585816. Note that Goal Seek gives
an approximate answer. The y-value is very small but not quite zero.
Find the x-intercept near 4 using Goal Seek.
Your answer should be 4.414
Finding Intersections of Graphs of Functions with Goal Seek
Before starting this worksheet, make sure you review the worksheet on finding
zeros.
We can use Goal Seek to find the intersection points of graphs of two functions.
Example
Linear Regression
Linear regression is a procedure where we fit a linear function to a set of data which
seem to exhibit a linear relationship. It uses all the data points, not just two. Hence,
all the points in the data set may not necessarily pass through the line.
Let us illustrate how to find the line of best fit using the functions in Excel.
showing the price P of a one-day adult admission to Disney World for
years since 1993. Fit a regression line to this set of data.
Year
x: yrs. since 1993
P: price of adult ticket
We first make a scatterplot of the data.
Step A: Scatterplot
To do this, go to
Tools -
. You will get the following dialog box:
In the
Set Cell
box, click the yellow box which stands for profit. In the To Value box,
Next, you want to fill in the last box called
By changing cell
. This is the x-value.
Click
OK
, and you will see the following box.
Click
OK
and the cell values in the blue
Invoke Goal Seek from
Tools-
, and follow the directions
Click
OK
and you should get the following window.
Click for help
Graphing more than one function
Note: The grapher macro will work only within this workbook. To use
it for your problems, insert a new worksheet
within this workbook
by
choosing
Insert -
in the menu bar. You can then have access
to the grapher macro by pressing
<
f(x) = x^2-6x+7
Now select the entire boxed region above and press
To graph more than one function on the same plot with the same x-range of values, simply
create a table with multiple column headings, one for each function. The next example
illustrates this.
Graph f(x)=x^2 and g(x)=x^3 on the interval [-2,2].
each have a separate column.
g(x)
Changing options in the graph
Graphing a single function
You can change the scale of the x and y-axes by clicking into the graph and double
clicking into the axes you wish to customize. The options that are possible are too
numerous to mention here. The best way is to play around with the dialog boxes.
Similarly, you can change the colors of the lines that are graphed by clicking
graph and double clicking the lines. Have fun exploring!!
Graph the functions f(x)=x^3 and g(x)=3x+1 on the same graph.
Use x values from -3 to 3 with x-spacing=0.5. This example is on p.61.
We create the following table with x-spacing of 0.5. Note that f(x) and g(x)
Finding zeros
Click for help on
The next example will show you how.
Suppose you want to determine the intersection of the graphs of the
We first create a table of values for f(x) and g(x) as shown in the worksheet
for graphs of functions. X-values range from -3 to 3 with an x-spacing of 0.5.
Graph the boxed region above by selecting it with the mouse and pressing
to invoke the grapher. You will get a graph similar to the one below.
There are three points of intersection: one near x=-2, another near x=0, and the
third near x=2.
We can now call Goal Seek. Remember that Goal Seek can find the zeros only
one at a time.
f(x)-g(x)
The following table has been set up to use Goal Seek. Note that there is a new
Goal Seek will give an error if you try to set value of f(x) equal to g(x). That is why
we must use f(x)-g(x) as the
Set Cell
reference.
Follow the same steps as in the worksheet Zeros of Functions to
call Goal Seek. We set the yellow colored cell to zero by changing
Click
OK
and you will get one of the intersection points in the blue box.
The approximate answer is -1.53. You should get this answer.
Since this intersection point is near x=0, the starting value of x will be 0.
Invoke Goal Seek as above. Fill in all cell references.
Your answer should be approximately -0.35.
Second Intersection Point:
First Intersection Point:
Since this intersection point is near x=-2, the starting value of x will be -2
Use the procedure outlined above, find the third intersection
point. It should be approximately 1.88.
Goal Seek Help
the x-value in the blue cell. Your box should look like the following.
Polynomial Regression
Not all data sets possess a linear relationship. Some may be better fit through a quadratic,
cubic, or even a quartic. Polynomial regression is easy to perform in Excel.
The next example shows you how.
The following chart gives the age and average number of live births
per 1000 women. We would like to fit a quadratic and cubic function
to this set of data and see which function fits the data better.
Fitting the quadratic function
# live births per 1000 women
Age
worksheet.
The equation is y=-0.4868x^2 + 25.95x - 238.49
We see that the cubic function is a better fit for the data that was given.
You essential follow the same steps as in the Linear Regression
column titled "f(x)-g(x)". The intersection points are those where f(x)-g(x)=0.
This is equivalent to the statement f(x)=g(x).
In order to use the graphing features of Excel, you will need to generate tables of x and y
values first. In this section, you will learn to easily generate equally spaced entries for use as
x-values.
Let us generate a table of values from -2 to 3 in increments of 0.5.
We could of course do this by hand but that would be laborious. Let
us have Excel automatically generate this table by using the
Fill
feature.
2. Select the blue cell with the mouse.
1. Let the first x-value begin in the blue cell at left.
3. In the menu bar, go to
Edit -
You will get the following dialog box.
4. You usually want your list in columns; so check the
columns
box for "Series in" section. The type is
"linear"
since we want
equally spaced points.
Step value
is set to
0.5
since our
Click
OK
5. On your left you should now see a filled column of values from
we terminate.
increments are in 0.5.
Stop value
is set to
3
, since that is where
-2 to 3 in increments of 0.5, like the one below.
A table of x and y values
Suppose we want to generate x and y values in a table. For example, find
f(x)=3x-2 for the x-values given in the table above.
Make a table with x and f(x) column headings. Fill the x-column as directed in
Example 1.
Note: the table was formatted with
borders using the formatting icons in the
rightmost section of the second toolbar
Next, we need to fill in values for f(x). As you know, Excel only understands
cell references. Therefore, the first y-value will have the formula
=3*d61-2
Click into the yellow cell and see how the formula is entered in the
cell entry box right below the toolbars.
We next fill the entire f(x) column by simply copying and pasting the formula
in the yellow cell:
Note that the cell references automatically change to the
x-value directly to the left of the y-value.
1. Select the yellow cell in the above table. Press
<
to copy.
2. Select the rest of the f(x) column. Press
<
to paste.
You can also access grapher anywhere in this workbook by pressing
<
and paste the formula using
<
Graphs of Rational Functions
process step by step. Unlike the previous example, however, you must be careful when working
with functions which may not be defined for certain values of x.
Example 1:
Let us graph the function f(x)=2x^2+x.
Example 1:
Let us graph the function f(x)=1/(x-1)
This function is not defined at x=1, and so that space for the y-value
is left blank.
If you need a closer look at the graph near x=1, you need to generate a new table
from, say, 0 to 2 in increments of 0.1.
Select the above table and press
graph similar to the one above.
Once you have done some work in your worksheet, you will want to save it first:
box will appear and it is self explanatory.
You will also want to format your work. Excel has many ways to help you
beautify your work, and it would fill many pages to describe all the possibilities.
Pull up the Page Setup option and set margins, headers, footers, etc.
menu.
Use Print Preview to preview your work after page setup.
When you are ready to print, choose the Print option under the File menu
But what about formatting your data and tables...
Once again, the possibilities are endless! The second row of icons in the tools bar are
all devoted to helping you format your data etc.
what it does.
Type some text in a cell, select the cell, and click on some of the
formatting icons.
With all these bells and whistles, it is easy to get carried away and produce very busy
looking documents. Keep your formatting simple. Highlight the information you
want. Do not use too many fonts and too many sizes of letters.
More Formatting...
You will often want to increase width of a column. This is easy - position your
mouse cursor at the very top of the column line that you want to widen. You will see
a small picture like
These are some basic formatting operations which you will use often. Consult a general Excel
guide for a more extensive review.
Formatting and Printing
Saving files
Previewing and Printing
When you start up Excel97, you will see a screen like the following. Familiarize yourself with the
Formatting data and tables
Point your mouse at each of the icons in the second row of the toolbar to see
To wrap text within a cell, go to
Format
menu, choose
Cells
option . Go to
the
alignment
tab and check the
Wrap text
box.
Formatting pages
SAVE by going to the
File
menu and selecting
Save As
(if this is a
new file) or
Save
(if you are resaving to an existing file). A dialog
Some basic page formatting will be done in the
Page Setup
option under the
File
menu.
You will next want to preview your work, so you bring up
Print Preview
under the
File
Data and cell references
All information in a spreadsheet is entered through data in cells. Each cell has a unique
reference given by its column letter and row number.
For example, the blue cell below is referenced as
B38
.
Click into this cell. You'll notice that the cell reference box
above the column headings says "B38". Also, you know the
reference of the cell by locating the column and row that it belongs to.
You can also have a range of cells. The yellow range below can be referenced
by
c44:g44
To select a range, click into the beginning of the range of cells. Hold down the mouse
and drag to the end of the range.
Select the range of cells
b45:h45
Select the range of cells
j42:j48
Formulas
Once you have entered data into cells, you will want to perform some operations with them.
+ : addition
- : subtraction
/ : division
* : multiplication
^ : exponentiation
The usual order of operations holds.
Using the above operators, you can write formulas which manipulate the data you have
entered in cells.
We need to store the x value in a cell. We also need to store the x^3-4x result
in another cell. Hence, we can make a simple table as follows. Note that you can
work.
enter text into a cell as well. Using a spreadsheet makes it easy to annotate your
Let x=3. Calculate f(x)=x^3-4x.
Now, the value of x is contained in the cell D72. The value for f(x) is computed by
the formula using the cell reference D72 in place of x.
So, the formula for f(x) using cell references is
=D72^3-4*D72
This formula is typed into the cell E72. (Note: E72 is the same as e72)
Change the value in D72 from 3 to some other number
and press
cell references in E72.
Copying and Pasting
Now suppose you want to compute f(x) in Example 1 for x =1,2,3,4,5. You also want to
display all these values simultaneously by creating a table. Instead of typing the formula
over and over again, we can copy and paste. This is illustrated in the next example.
Compute f(x) for x=1,2,3,4,5 and display the results in a table.
Make columns for x and f(x). Enter the x values that you are interested in:
In the yellow f(x) cell, enter the formula for f(x)= x^3-4x. This gives the following:
Inserting Rows, columns, worksheets
in the menu bar. To insert a new worksheet, simply choose
Insert -
To insert rows and columns, select the insertion point and go to
Insert
Since we want to compute the values of f(x) for the other values of x as well, we
Once you do this your table will look like the following:
Click into these cells
to see how the formulas are
entered
Basic arithmetic operators are:
Change f(x) to be f(x)= 2x^2+1. Enter the formula using
can copy the formula in the yellow cell as follows:
Invoke the grapher by pressing
Shortcut
If you are filling a column or row of equally spaced values, you can type in the first two
values of the series in two adjacent cells, highlight the two cells, and drag the values down by
moving your mouse to a cross hair at the lower right hand corner of the selected cells.
The following table lists data
functions f(x)=x^3 and g(x)=3x+1.
1. Select the x and the P columns above.
2. Click in Insert and Choose Scatterplot.
3. Select the XY Scatter for chart type.
4. The Chart Tools Tab allows you to choose your options
a. Input titles for the axes
b. Input a title for the graph
5. Click on Trendline (in Layout tab)
a. Choose Linear for this graph
6. Option 9 displays the actual equation of the line added and the value of R^2.
Congratulations! You have finished your scatterplot.
Your graph should look like this one (Colors and wording might vary).
Choose Add Trendline
Difference:
This is not a linear model!
The plot shows a change in the trend (increasing then decreasing)
Insert Scatterplot
Choose more Trendline Options at bottom
A new Menu appears
Choose Polynomial Trendline
Clcick to set the degree to 2 (quadratic)
Note: You can actually watch as you try different "fits"
Check the boxes to display the equation and the R^2 values.
Note: these values are optional and will not display otherwise.
This is not a very good model (low value for R^2.
Touches no points. Too low then too high.
Fitting the cubic function
You essential follow the same steps as in the Quadratic Regression
Click to set the degree to 3 (cubic)
NOTE: This is a much better fit. You can see it touches more points.
The R^2 value is much higher.
R^2 = .9946
there is very little error in the model!
Menu bar
Formatting
Numeric formatting
ChartWizard
Reference of
selected cell
Selected cell
Current worksheet
Format cells
Click into this cell to see
how the formula is entered
Click into the cell to see
how formula is entered