• WORK
• TOOLS
• TRAINING
• VIEWS
• CONTACT

This is the first in series of blog posts where I’ll illustrate the use of Excel as an aid to the study of calculus. I’ll first start with graphing functions with a single independent variable x in a cartesian coordinate system.

Lets graph the function y = f(x) = (1 - 4x - x3/17) * sin(x2) in the interval [-15,15]

Next, I need to generate a collection of ordered pairs (x,y). For that I'm going to use Excel's new Dynamic Arrays functionality to spill a series of 10 equally spaced Xs using the SEQUENCE function as follows:

5. Enter the following formula in cell B8
= SEQUENCE(C4,1,C2,C5)

The worksheet should look like this:

# MATHS IN EXCEL - 2D CARTESIAN PLOTS

6. Enter the following formula in cell C8:
= IFERROR((1 - 4 * B8# - B8#^3 / 17) * SIN(B8#^2),NA())

Notice that I used the IFERROR function to force an #N/A where functions cannot be evaluated (i.e. division by zero, LN of numbers less than or equal to zero, square roots of negative numbers, etc.). Like that the Excel charts will show #N/A as an empty cells (gaps) instead of a zero.

The worksheet should look like this:

Next, lets create two named ranges—one for the Xs and one for the Ys—that will be used as data ranges for the chart.

7. On the Formulas tab, in the Defined Names group, click Define Name.
8. In the New Name dialog box, in the Name box, type lstX.
9. In the Refers to box, click the Collapse Dialog button (which temporarily shrinks the dialog box), select cell B8 on the worksheet, type # at the end of the reference and then click the Expand Dialog as follows:

10. Click the Ok button to close the New Name dialog box.
11. Repeat steps 7-10 to create another named range lstY by referring to cell C8 (don't forget to add the hash key # at the end of reference to tell Excel that you are referencing a Dynamic Array).

By now we have created two named ranges lstX and lstY as can be seen in the Name Manager dialog box below:

12. Next, click Insert > Scatter > Scatter with Smooth Lines to insert a scatterplot.
13. On the worksheet, right-click the chart and then, on the shortcut menu, click Select Data.
14. Next, In the Select Data Source dialog box, add a new data series by clicking the Add button under Legend Entries (Series).
15. In the Edit Series dialog box, type "Function" in the Series name edit box.
16. Next, edit the Series X values to refer to cell B8.
17. Delete \$B\$8 from the newly created reference so that you are left with =Sheet1! in the box.
18. Next, press F3. The Paste Name dialog box appears. Select lstX from the Paste name list and press the Ok button to close the Paste Name dialog box.
19. Repeat steps 16-18 above to edit the Series Y values by referring to lstY as per below:

20. Press the Ok button twice in a row to close the Edit Series and the Select Data Source dialog boxes and the chart should look like this:

So far we've used 10 data points to draw the curve. To increase the accuracy of the plot lets increase the # of points.

21. In cell C4 type 2000.

Because we used Dynamic Arrays in cells B8 and C8 the calculations will spill to generate 2000 pairs of data points. Since we used Named Ranges, the chart will automatically adjust and should look like this:

The first thing to do is to define the boundaries of the interval, the number of points to be generated and the step between each point on the horizontal x axis.

1. Enter "Lower bound", "Upper bound", "# of points" and "Step" in cells B2, B3, B4 and B5 respectively.
2. Enter the number -15 in cell C2 and the number 15 in cell C3.
3. Enter the number 10 in cell C4. This will define the number of points that will make up the graph. The more points we have the more accurate the shape of the curve will be (more about this later).
4. Enter the following formula in cell C5
= (C3 - C2) / (C4 - 1)

The worksheet should look like this:

Views > Excel charts > Maths in excel 2D cartesian plots

MATHS IN EXCEL - 2D CARTESIAN PLOTS        22. Finally, do some formatting such as adjusting the bounds of the horizontal and vertical scales, the color of the curve, the font, its color and size, and the gridlines. Add the chart title and the equation and the graph should look like this:  23. If you want to plot the curve of another equation, say y = f(x) = x * sin(1/x) in the interval [-1.5,1.5], then type the following formula in cell C8:
= IFERROR(B8# * SIN(1 / B8#),NA())

24. Type -1.5 in cell C2 and 1.5 in cell C3

Adjust the boundaries of the horizontal scale and the chart should look like this: Thank you for reading, and I hope you found this useful. If you have any questions, find me on Twitter and ask me anything.