1.4: Graphing Functions with Excel

Last updated
Save as PDF

Page ID: 83913

$ \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } $

$ \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} $

$ \newcommand{\id}{\mathrm{id}}$ $ \newcommand{\Span}{\mathrm{span}}$

( \newcommand{\kernel}{\mathrm{null}\,}\) $ \newcommand{\range}{\mathrm{range}\,}$

$ \newcommand{\RealPart}{\mathrm{Re}}$ $ \newcommand{\ImaginaryPart}{\mathrm{Im}}$

$ \newcommand{\Argument}{\mathrm{Arg}}$ $ \newcommand{\norm}[1]{\| #1 \|}$

$ \newcommand{\inner}[2]{\langle #1, #2 \rangle}$

$ \newcommand{\Span}{\mathrm{span}}$

$ \newcommand{\id}{\mathrm{id}}$

$ \newcommand{\Span}{\mathrm{span}}$

$ \newcommand{\kernel}{\mathrm{null}\,}$

$ \newcommand{\range}{\mathrm{range}\,}$

$ \newcommand{\RealPart}{\mathrm{Re}}$

$ \newcommand{\ImaginaryPart}{\mathrm{Im}}$

$ \newcommand{\Argument}{\mathrm{Arg}}$

$ \newcommand{\norm}[1]{\| #1 \|}$

$ \newcommand{\inner}[2]{\langle #1, #2 \rangle}$

$ \newcommand{\Span}{\mathrm{span}}$ $ \newcommand{\AA}{\unicode[.8,0]{x212B}}$

$ \newcommand{\vectorA}[1]{\vec{#1}} % arrow$

$ \newcommand{\vectorAt}[1]{\vec{\text{#1}}} % arrow$

$ \newcommand{\vectorB}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } $

$ \newcommand{\vectorC}[1]{\textbf{#1}} $

$ \newcommand{\vectorD}[1]{\overrightarrow{#1}} $

$ \newcommand{\vectorDt}[1]{\overrightarrow{\text{#1}}} $

$ \newcommand{\vectE}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash{\mathbf {#1}}}} $

$ \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } $

$ \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} $

One area where Excel is different from a graphing calculator is in producing the graph of a function that has been defined by a formula. It is not difficult, but it is not as straight forward as with a calculator. It is a skill worth developing however. When we are given a formula as part of a problem, we will want to easily see a graph of the function.

We will walk through the process for producing graphs for three examples of increasing complexity. For the first example we have a specific function and specific range in mind, say $y=x^2-6 x$ over $-10 \le x \le 10\text{.}$ For the second example, we would like to use parameters in the formula, for example, $y = a x^2 + b x + c\text{,}$ with specified values of a, b, and c, and have the ability to easily change the values of the parameters and see the graph. For the third example we would also like to have the ability to change the domain, graphing over $\xLow \le x \le \xHigh\text{,}$ where $\xLow$ and $\xHigh$ can easily be changed.

Example 1.4.1: A Basic Graph.

$Figure \text { } 1.4.2.$ Video presentation of this example

—Graphing $y=x^2-6 x$ over $-10 \le x \le 10$

$clipboard_e164bc23020774f66579d64871a50a23a.png$

We start by producing a column for $x$ and one for $f(x)\text{.}$ In the column for $x$ we start with values $-10$ and $-9\text{,}$ so that we can complete the column with a quick fill. Similarly, we start the $f(x)$ columns in the first cell with the “$x$” replaced by the appropriate cell reference. In this case the formula for $f(x)$ is in cell B15 and $x$ is in cell A15.

We then use quick fill and quick copy to fill out the table.

$clipboard_e289c212482845fdfc9cfb663c2664af2.png$

With the values of the cells filled in we highlight the cells we want to graph (A14 through B35) and add a scatter plot for the highlighted values.

$clipboard_e685e7bdafc5390e8a13e0417f544e22b.png$

(The location of the scatterplot will be a bit different with Macs. The scatterplot is in the Charts ribbon, under other, on Macs.) This gives the desired graph.

$clipboard_e034cc60fd91ffc6f0acadcfa4ea8e504.png$

Example 1.4.3: A Graph with Parameters.

$Figure \text { } 1.4.4.$ Video presentation of this example

— Graphing $y=x^2-6 x$ as an example of $y = a x^2 + b x + c$ over the domain $-10 \le x \le 10\text{.}$

For the second example, we want the same graph, but we want the ability to easily convert the graph of our first quadratic into a different quadratic function. The solution is to consider $a\text{,}$ $b\text{,}$ and $c$ to be parameters that we can change.

Toward the top of the worksheet, we put the labels $a\text{,}$ $b\text{,}$ and $c\text{,}$ and give values for those parameters. In this case the values of $a\text{,}$ $b\text{,}$ and $c$ are in cells B9, B10, and B11 respectively.

Now we set up the problem in the same way we did above except that we are using absolute references for $a\text{,}$ $b\text{,}$ and $c\text{,}$ and relative references for $x\text{.}$

$clipboard_eb42a31e4bef523627252661a5dbf53b4.png$

Now, we once again do a quick fill to complete the table, and then add a scatterplot.

$clipboard_e466e7faeecf1ce93bb24578217add86a.png$

The difference with this second example is that if I now want to look at the graph of $y = -x^2 + 3 x + 10\text{,}$ I simply change the values of the parameters $a\text{,}$ $b\text{,}$ and $c\text{.}$

$clipboard_e523fbf3c2b777bc90211274adcbf5f89.png$

Example 1.4.5: Controllling the Viewing Window.

$Figure \text { } 1.4.6.$ Video presentation of this example

— Graphing $y=x^2 - 6 x$ as an example of $y = a x^2 + b x + c$ over the domain $-10 \le x \le 10\text{,}$ but with the ability to easily change the domain of the graph.

Often, when we graph, we will want to change the domain of the graph. Most easily, I may want to zoom in on a particular region to get a better view of some interesting feature. I may want to look closely at several different regions.

To do this we will again plot 21 points, but we want to have control of the starting point and the change in x between the first and second points. First we add labels and values for x-start and x-step. Then we need a bit of care in defining the values of $x\text{.}$ The first value of $x$ (cell A18) is the value of x-start. Every other value of x is defined as the previous value of x plus the value of x-step.

$clipboard_e2572e7a3d568c874744bc48c17fd334f.png$

In this case I want a better look at the vertex of the parabola. I decide I want to see the graph for $0 \le x \le 5\text{.}$ My value for x-start is 0. My value for x-step is one twentieth of the distance from 0 to 5, or $(5-0)/20 = 0.25\text{.}$ I plug those values in and see the graph.

$clipboard_ef6c4c9a84ea64fd5d33627631af55605.png$

Example 1.4.7: Graphing more than One Function.

$Figure \text { } 1.4.8.$ Video presentation of this example

We would also like to put two or more graphs together. For our examples, we will want to use the functions $f(x) = x – 3\text{,}$ $g(x) = (x^2 – x)/10\text{,}$ and $h(x) = x^3 – x\text{.}$ We start by using the procedure given above to make a chart of values for the three functions.

$clipboard_e3ff14772970ab33afa07921a7db92eb2.png$

We then simply select the cells for $x$ and the functions we want graphed together and produce a scatterplot as before. (To graph $g(x)$ and $h(x)$ together, we want to select the columns for $x\text{,}$ $g(x)\text{,}$ and $h(x)\text{.}$)

$clipboard_ed489208cc92f30e237ed01f49cdb7f40.png$

One problem with the graph of $g(x)$ and $h(x)$ together is that the functions have different orders of magnitude, so we do not see that $y = g(x)$ is a parabola. One remedy is to use a secondary axis for the graph of $h(x)\text{.}$ (Simply double click on one of the points for $h(x)\text{,}$ and select secondary axis from the axes tab.)

$clipboard_e1b5ff1f3e0d534ef85467911b6502bc2.png$

Formatting a chart

Excel has a lot of ways to add formatting to a graph or chart, many more than we want to be concerned with at this point. We simply point out a few and leave it to the reader to explore how this should be used for a good visual presentation. If you click once on the chart to select it, the Chart tab in the home ribbon, adds sub-tabs for layout and format. With Chart Title, you can add a title to the chart, then edit it. The Axes icon allows you to add titles for the axes. If you select a data point form $g(x)\text{,}$ you can then use the Data Labels icon to add values next to the points. The chart with these annotations is given below. The rule of thumb to follow is to add enough annotations for a reader to be able to easily understand what is happening in the chart.

$clipboard_e87b9325ee3d9723e62e2bc8d453935c3.png$

It is also worthwhile to note that you can manually set the y-range of a graph by double clicking on the axis and setting the values. This is particularly useful of the function has a vertical asymptote.

Online graphing tools: Wolfram Alpha

Throughout this book, we are limiting ourselves to mathematical tools that the student can reasonably expect to find in a generic work environment. That is one of the reasons for focusing on using spreadsheets and Excel. A second reason is that we will spend a significant amount of time on functions defined by data points, where we then try to construct a formula. However when we are starting with a formula, there are easier ways to produce a graph. The simplest is to use the free website, Wolfram Alpha. For example to obtain a graph of the functions $f(x) = x^2 – 3 x\text{,}$ as $x$ ranges from $-5$ to $5\text{,}$ we simply type “plot x^2 – 3 x for x from -5 to 5” and obtain:

$clipboard_eb978146a18d33eb9f1029324934787f7.png$

We will return to Wolfram Alpha from time to time, when we have nice formulas to manipulate.

class="

ExercisesExercises 1.4 Graphing functions with Excel

Exercise 1:

Produce a worksheet that with a graph of the function $f(x) = x^2 - 5 x\text{,}$ with $x$ going from -10 to 10 by 1.

Answer

The entry in cell B2 is =A2^2-5*A2; remember to use quickfill to complete the table

$clipboard_e94971f3e2005a776c3e9f1842c92d695.png$

Exercise 2:

Produce a worksheet that with a graph of the function $g(x) = (x^2 - 5 x)/(x^2 + 7 x + 10)\text{,}$ with $x$ going from -10 to 10 by 1. Explain why the graph is inaccurate. (Pay attention to places where there should be asymptotes.)

2* – Extra credit) — Fix the graph from problem 2 by adjusting the set of x-values used.

Exercise 3:

Produce a worksheet with a graph of $h(x) = x^3 + a x^2 + b x + c$ for $x$ from -10 to 10, where the values of $a\text{,}$ $b\text{,}$ and $c$ can be changed and the graph will update automatically. For initial values, use $a = -2\text{,}$ $b = 1\text{,}$ and $c = -11\text{.}$

Answer

The entry in B5 should be =A5^3+$B$1*A5^2+$B$2*A5+$B$3. Note that the references to $a\text{,}$ $b$ and $c$ are absolute references.

$clipboard_e059432a160a7985985e150f8a302a256.png$

Exercise 4:

Produce a worksheet with a graph of $k(x) = (x^2 + a x + b)/( x + c)$ for x from -10 to 10, where the values of $a\text{,}$ $b\text{,}$ and $c$ can be changed and the graph will update automatically. For initial values, use $a = -5\text{,}$ $b = 2\text{,}$ and $c = -11\text{.}$

Exercise 5:

Produce a worksheet with a graph of $h(x) = x^3 -2 x^2 + x -11$ for $x$ going from a to b, where the values of $a$ and $b$ can be changed and the graph will update automatically. For initial values, use $a = -5$ and $b = 5\text{.}$

Answer: The entries are $a$ and $b\text{,}$ and the step size. We assume here that we are using 10 points to create a graph.

$clipboard_e91c7e70e25b16c5469a086fe86405c26.png$

The data and the graph looks as follows, and changing $a$ and $b$ allows us to quickly find several different graphs of the same function.

$clipboard_e85c20dd04f9fad9bbe848c1538785eb6.png$

Search

Text Color

Text Size

Margin Size

Font Type