Polynomial Regression
Linear Regression
Intersection of Graphs
Zeros of Functions
Rational Functions
Graphs of Functions
Tables
File Operations
Intro
grapher
Module2.picksheet1
Module2.picksheet2
Module2.picksheet3
Module2.picksheet4
Module2.picksheet5
Module2.picksheet6
picksheet1
picksheet2
picksheet3
picksheet4
picksheet5
picksheet6
picksheet7
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
1. Select the x and the P columns above, including the column headings.
2. Click on the Chart Wizard icon. You should see the following
3. Select the XY Scatter for chart type. Select the dots only for chart sub-type.
See the figure below.
4. Click the Next button. You will see the following
5. Click the Next button. You will get a screen with options. Simply press
the Next button.
6. You will now at Step 4. Click the box that says to include the chart in this
worksheet. Then press the Finish button.
Step B: Inserting the regression line
1. Single-click any one of the data points in the scatterplot you created.
All points will be highlighted.
Choose the linear box.
You can click into the equation box and move it to place it where you can
read it better.
We are finished and the equation is y = 2.138x + 33.733.
Use the equation above to predict the ticket price in 2000.
To do this, go to Tools -> Goal Seek. 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-> Goal Seek, and follow the directions
Click OK and you should get the following window.
2. In the menu bar choose Chart -> Add Trendline. You see the following.
3. Click into the options tab and check the box that says "display equation".
Click OK. You chart will be similar to the one below.
Click for help
Graphing more than one function
Congratulations! You have finished your scatterplot. It should look like the
following.
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 -> Worksheet in the menu bar. You can then have access
to the grapher macro by pressing g
f(x) = x^2-6x+7
Now select the entire boxed region above and press g to start the
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 g
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
Choose the polynomial box with Order 2 for the drop down box.
Step B
Step A
worksheet.
Follow all items in Step A of linear regression worksheet
The equation is y=-0.4868x^2 + 25.95x - 238.49
for a cubic. Then follow the same steps as before.
Follow Step A as above. In Step B, part 2, change polynomial order to 3
should be similar to the following.
Find the best cubic for this set of data. Your final graph
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 -> Fill -> Series
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 c to copy.
2. Select the rest of the f(x) column. Press v to paste.
You can also access grapher anywhere in this workbook by pressing g
and paste the formula using V
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 g to invoke the grapher and you should get a
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 <--||--> Use your mouse to widen the cell.
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 . What do you notice?
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 -> Worksheet
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 g. You will see a graph like the one below.
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.
Copyright (c) 2003 R.Narasimhan Page of
00.00
00.00
00.00
00.00
00.00
000.00
00.00
000.00
00.00
00.00
00.00
00.00
00.00
0.00
# live births per 1000 women
# live births per 1000 women
Copyright (c) 2003 R.Narasimhan Page of
0000.00
0.00
00.00
0000.00
0.00
00.00
0000.00
0.00
00.00
0000.00
0.00
00.00
0000.00
0.00
00.00
0000.00
0.00
00.00
P: price of adult ticket
P: price of adult ticket
Copyright (c) 2003 R.Narasimhan Page of
-0.00
-00.00
0.00
-0.00
0.00
0.00
-0.00
-00.00
-0.00
-0.00
-0.00
-0.00
-0.00
-0.00
-0.00
-0.00
-0.00
-0.00
-0.00
-0.00
-0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
00.00
0.00
0.00
00.00
00.00
-0.00
-0.00
0.00
-0.00
0.00
0.00
-0.00
0.00
0.00
0.00
0.00
0.00
0.00
-0.00
f(x)
g(x)
Copyright (c) 2003 R.Narasimhan Page of
00.00
-0000.00
000.00
0000.00
0.00
0.00
0.00
0.00
0.00
-0.00
0.00
-0.00
0.00
-0.00
0.00
0.00
0.00
0.00
0.00
-0.00
0.00
0.00
0.00
Click into the cell to see
how formula is entered
f(x)
Copyright (c) 2003 R.Narasimhan Page of
-0.00
-0.00
0.00
0.00
-0.00
-0.00
0.00
-0.00
0.00
-0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
-0.00
0.00
0.00
0.00
-0.00
0.00
-0.00
0.00
-0.00
0.00
-0.00
0.00
-0.00
0.00
-0.00
0.00
-0.00
0.00
-0.00
0.00
-00.00
0.00
0.00
00.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
f(x)
f(x)
Grapher
Grapher
Copyright (c) 2003 R.Narasimhan Page of
-0.00
0.00
0.00
0.00
-0.00
0.00
-0.00
0.00
-0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
00.00
-0.00
0.00
0.00
-0.00
0.00
-0.00
0.00
-0.00
-0.00
0.00
-0.00
-0.00
0.00
-0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
f(x)
f(x)
g(x)
Grapher
Copyright (c) 2003 R.Narasimhan Page of
-0.00
-0.00
-0.00
-0.00
-0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
-0.00
-0.00
0.00
0.00
-0.00
-0.00
-0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
Copyright (c) 2003 R.Narasimhan Page of
Copyright (c) 2003 R.Narasimhan Page of
0.00
00.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
-0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
-0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
00.00
0.00
0.00
0.00
00.00
0.00
0.00
0.00
000.00
0.00
0.00
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