Business Calculus with Excel

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

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.

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.

(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.

Graphing $y=x^2-6x$ as an example of $y=a{x}^2+bx+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{.}$

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

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

Graphing $y=x^2-6x$ as an example of $y=a{x}^2+bx+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.

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.

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.

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{.}$)

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.)

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.

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.

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

³

https://www.wolframalpha.com/

. For example to obtain a graph of the functions $f(x)=x^2-3x\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:

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