# Spreadsheet Calculus: Derivatives and Integrals

202,735

20

11

## Intro: 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=2x^{3}+6x^{2}-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*B1^{3}+6*B1^{2}-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.

## 11 Discussions

10 months ago

Hi

I have a word problem that I have to solve using Excel. The Problem is as follows:

There are 2 friends who found a bag of 20 cents coin. They equally shared amongst them but 1 coin was left. So they decided to share it with 3 people but then 2 coins was left. This happened till there were a total of 11 people and still 10 coin was left after sharing. This means:

2 people shared - 1 coin left3 people shared - 2 coin left4 people shared - 3 coin left ;........................finally; 11 people shared - 10 coin left.Therefore, how much 20 coins was in the bag???Can someone help me to solve this using Excel.

1 year ago

How can i integrate a formula that i created on a downloaded spreadsheet. Please Help.

1 year ago

Nice posting. I encourage you all to look a my templates at www.xlcalc.net. take a pick at Centroid-i-Area Calc.

3 years ago on Introduction

Thanks for posting! This tutorial helped me re-learn how integrals are approximated. You rock!

3 years ago on Introduction

Nice example and ideas. I gussied up the spreadsheet a little. Needs a macro or two (on the integral from/to) to ensure valid input but it's a little better.

5 years ago on Introduction

Is add Image working fine?, or I used it wrongly, so It did not work for me? I do not know.

Now it works fine. My error was that I posted my first comment without uploading the selected image file. However, the image does not appear in the 'preview'. Is that normal? I will see when this comment is posted.

5 years ago on Introduction

I like this simple approach very much. Many people forget about math after graduation, but those who are in research and or any job related to calculation may also forget many basics and need from time to time to go back through to review the issue in a different way other than that usually given in the class-room, and the spreadsheets are essential tool in their everyday life. Your effort is not lost, on the contrary it is appreciated. I posted your site to my site on Facebook. I am sure many people will refer to your site some time ahead.

8 years ago on Introduction

I know a lot of people could benefit from this. Having just graduated from college a bit ago I cannot believe I never learned this (or even thought to do it!) This is a great way to teach basic calculus. Great tutorial. I'm gonna pass this info along to my college buddies still going through.

9 years ago on Introduction

This was very helpful. Thank you.

9 years ago on Introduction

This is EXCELLENT! Once again I am puzzled as to why some Instructables get to be "featured" when some really good ones, like this one, are passed over.

Reply 9 years ago on Introduction

I'm flattered. I do admit that this instructable is lacking the "fun factor" of most instructables. It's pretty boring unless you're really into math. Thanks though.