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