Introduction: Spreadsheet Calculus: Derivatives and Integrals
Calculus can be kind of tricky when you're first learning it. Here's how you can use spreadsheet programs to your advantage. Use this to check your answers or just get an idea of what a graph looks like. If you're doing integration then you also probably know that there are some functions that don't have elementary antiderivatives. You can use spreadsheets to visualize what the antiderivatives of these functions looks like.
Just a quick note: throughout this instructable I refer to myself as we. This isn't the royal we, I'm doing this project with a group of 3 other people for a class at UBC, so when I say we I mean us, my group.
Step 1: Set Your X Values
First thing you're going to need is a spreadsheet program like Excel, Numbers, or OpenOffice. If you don't know how to use any of these programs, don't worry, it's pretty easy learning.
To demonstrate, we're going to use the equation y=2x3+6x2-12x+4. It's the same equation shown in the picture below, which looks a lot nicer. We chose this equation because finding it's derivative and antiderivative will be easy, so we can check our answer.
First, you want to put your x values down in your spreadsheet, I made mine go from -5 to 5. Also set your step size, I set mine at 0.1. You could also use 0.01 (it would be a bit more accurate) but you generally don't want to go smaller. Once your columns are more than a few thousand cells long, it takes forever for your computer to process them all at once. For my computer, below 1000 cells usually works well.
Put your step size in a cell (I use A2). Put your initial value at the top of the next column over, the second picture below shows you what this should look like. Then in the cell below (B2) type in "=B1+$A$2" without the quotation marks, hit enter. The dollar signs tell your spreadsheet program to reference A2 regardless of which cell you copy the equation into. Place your cursor over the bottom-right corner of the cell, there should be a small black square, click it and drag it down, as you drag it, you should see the numbers slowly getting bigger. It's hard to describe, look at the third picture. Drag this box down until you reach the other end of your X range, in this case 5.
Step 2: Plug in Your Function and Graph It
Now just plug in your function so that every cell is a function of the one next to it. Wherever you have an undefined variable (x) just put in B1,B2, etc depending on which cell you're working on. In this case, write "=2*B13+6*B12-12*B1+4" in the C1 box (use shift+6 to do the power sign in excel, instructables just superscripts everything when I use it here.) Notice that it's the same function as before, except instead of X we're using B1. Also, make sure you put in asterisks for multiplication signs, it makes sense to write 2B1, but it won't work, write in 2*B1.
Next, just drag your box all the way down, just like the previous step. If you're using excel you can also double click the bottom right corner of your cell, and it will do it for you.
Now, to graph it. Find the charts button on whatever spreadsheet program you're using. Make sure you're making an XY scatterplot. Then right-click your new chart and hit "select data" you want your X values (column B) to be your X values, and Y values (column C) to be your Y values. Hit OK.
You should now have a nice plot of your data. It's probably going to be a huge collection of dots, if you right click on of the data points on your chart and select "format data series" you can turn it into a line instead (make sure you check "smoothed line" if you have the option.) Also, go to marker style and select "no marker" they just get in the way.
There's your function, now comes the fun stuff, derivatives and integrals!
Step 3: Differentiate It!
Plotting the derivative is relatively easy. A derivative has the form "dy/dx", in other words, the change in y over the change in x. The change in x is easy, and it never changes, it's just what we wrote into cell A2, which in this case is 0.1. The change in y is simply going to be the difference between the cells, which isn't hard to figure out. Type in "=(C2-C1)/$A$2" for cell D2, then drag it down to the bottom (or double click the bottom right of the cell.) The picture should give you a good idea of what to do. There wont be anything in cell D1, by differentiating we lose one of the cells, but if your dx is small enough, it doesn't matter. Make sure to type this into D2 instead of D1, that way when you drag it all the way down the last cell wont display a ridiculously huge number.
Plot this on the same graph as the last one, use the same x values as before. There's your derivative. You can also do the derivative by hand and plot it to make sure that it matches up, it should.
This is helpful if you're having trouble differentiating a function and you want to see what the derivative looks like. Once you get the hang of differentiation though, it gets pretty easy and you wont really need this. Integration on the other hand is quite a bit harder, there are also some functions you wont know how to integrate. Let's do it with spreadsheets!
Step 4: Integrate!
An integral is essentially the area between a curve and the x-axis. There's also a "negative area" when the function is negative. The integral is the net area beneath a curve. That should be pretty easy to calculate.
We're finding the area using a method of approximation known as Riemann sums. Basically we're drawing a lot of rectangles that approximate the shape of our curve. If we add up the area of each rectangle, we know (more or less) the area beneath the curve. The picture below is worth a thousand words.
In cell E1 type in "=C1*$A$2" and drag this down. These are the "rectangles" that we need to add up.
In cell F1 type in "=SUM($E$1:E1)" and drag the cell down. What you are doing here is adding up all the rectangles from cell one to cell x. This is the indefinite integral. Go ahead and graph it on your plot. All integrals are related by a constant. If you had typed in "=SUM($E$1:E1)+200" in the last step instead, it would still be the integral you're looking for. It doesn't really matter what constant you use.
Step 5: Let's Do a Definite Integral
If you're in a calculus course, you're probably going to be asked to do a few definite integrals. Basically you integrate from one x value to another. Using the integral you just made, checking your answer can be pretty easy. For example, let's try the integral shown below. This is the one we just plotted earlier. If you already know how to integrate, solve it by hand, you should get 92.5.
To approximate it with out spreadsheet, we just write (in any cell) "=F81-F31". Row 81 is where x=3, row 31 is where x=-2. What you're doing is taking the indefinite integral at x=3 and subtracting the indefinite integral when x=-2 from it.
On this spreadsheet the answer we got was 94.58. Not a bad approximation, but it wouldn't get you any points on a homework assignment (unless you're asked to approximate a function using finite sums.) If you use a 0.01 step size you get 92.70, even more accurate. Using 0.001 step size you get 92.52, which is even more accurate. Trying smaller step sizes would just be ridiculous though, it would take a few hundred thousand cells, it would take your computer a while to figure it out.
I've attached the demo spreadsheet below. Enjoy.
