Graphing with Excel

In this assignment, you will run through the features of Excel needed to graph a function f(x) given by a data table or by a formula. You can get the latest revision of this sheet by going to http://www.math.msu.edu/~magyar/calculus/ExcelGraph.html . After you finish, read about the basic principles of spreadsheets (such as relative addressing) in the handout Introduction to Spreadsheets.

As you do each numbered item, read all the instructions for that item before doing it.

1. Open a new worksheet
   1. Start Excel, open a new worksheet. Save and name the worksheet by clicking on the Edit menu and choosing Save As.
   2. If at any point you mess up your sheet, you can always go to the Edit menu and Undo as many times as it takes to remove the mess.
   3. Use the mouse to move around the cells. Click on cell A1, type your name, and hit Enter. Enter the assignment title "Homework 9/12" in cell D1. Enter your section (Math 132-12 or 16) in cell G1.

2. Here comes the hardest part. Use the Fill Down command to make a column of numbers from 0 to 2, spaced at intervals of 0.1. First read all the instructions (a)-(e) below.
   1. In cell A4, enter x. This is a label to remind you that column A will contain the x-axis values for your graph.
   2. In A5, enter the number 0 .
   3. In A6, enter the formula  =a5+0.1 , which means "the contents of A5 plus 0.1". This will display the number 0.1 .
   4. Fill down from A6 (as described in the next item) to make the full column of numbers. That is, take the formula you already entered in A6 as a model for all the cells under it.
      • When this is done, A7 should contain the formula  =a6+0.1 , and display the number 0.2 .
      • A8 should contain  =a7+0.1 , etc. The lowest cell should display 2 .
      • You do not have to type each line! The Fill Down command does it for you, using the principle of relative addressing.
   5. Methods of doing Fill Down.
      • First method: Click on A6, then on the dot at the lower right corner of this cell. Drag the dot down 20 rows. The formula will be copied down appropriately, and the numbers should display.
      • Second method: Click on A6, then drag the mouse down 20 rows, selecting the desired 20 cells. In the Edit menu, choose Fill > Down (or type Ctrl+D , holding down the Ctrl key and D simultaneously). The formula will be copied.
      • Wrong method: You get a surprise if you fill down from A5 instead of A6. Why?

3. In the Homework, you approximated the derivative of f(x) = x^1/3 using Δx = 0.1 . Call this approximate derivative function g(x) = Df/Dx.
   1. Put the label Df/Dx in cell B4.
   2. Next to each x-value in column A, enter the corresponding value of g(x) in column B, accurate to 1 decimal place. For example, if A6 contains 0.1, and g(0.1) = 1.206, then enter 1.2 in cell B6.

4. You also got an exact formula for the derivative h(x) = df/dx = (x^1/3)' . Your answer should be: h(x) = 1/3 x^−2/3.
   1. Put the label df/dx in cell C4.
   2. In cell C5, enter the formula 1/3*a5^(-2/3). The cell should display an error message, since 0^-2/3 means dividing by 0. This is correct: the derivative is infinite at this point.
   3. Now use Fill Down from C5 to get the values of h(x) in the appropriate cells of column C.

5. Make a graph of Df/Dx and df/dx.
   1. Select the block of cells A5:C25 by clicking on A5 and dragging the mouse to C25.
   2. In the toolbar at the top of the window, click the Chart Wizard icon (a little picture of a bar graph).
   3. Choose the XY(Scatter) type of chart, then the subtype which has curves going through the data points. Click Next.
   4. A little mock-up of the graph should appear. The Data range box should read =Sheet1!$A$5:$C$25 . (The $ signs indicate absolute addresses.) Just click Next.
   5. Using the dialog boxes, fill in your chart's title "Numerical and Algebraic Derivative of f(x) = x^(1/3)". Click Finish. The graph should appear.
   6. To change the data in the graph, click on the graph, then on the Chart Wizard icon, then make the change in the appropriate window.

6. Save your work! You're lucky Excel hasn't crashed yet, losing everything you typed.

7. Make your spreadsheet prettier.
   1. Move your chart by dragging it, and resize it by dragging the little black boxes in the corners. Add labels to the legend-box on the right by entering them in the Chart Wizard Series window.
   2. Note the incorrect value of df/dx = 0 for x = 0. Get rid of this bogus data-point by changing the Range of Series 2 in the Chart Wizard Series window.
   3. Make the column labels boldface and centered, by using the buttons on the Formatting toolbar. (Probably near the top right of your window. Otherwise click View > Toolbars > Formatting). Make your name and the title in boldface.
   4. Do whatever other prettifying you can think of. Double-click parts of the chart to see how you can format them. Right-click on cells to see the options.

8. The homework also asks for a graph involving the derivative of sin(x). Do this in the empty cells below your previous work.

9. Print out your spreadsheet to hand in. Use the Menu item File > Print.