Software‎ > ‎Computation‎ > ‎

Using Spreadsheets

Why use spreadsheets?
Spreadsheet tools such as Microsoft Excel, OpenOffice.org's Calc, and Google Docs allow you to rapidly enter, process, and visualize data.  
For this tutorial, we will use Google Docs Spreadsheet (click for the full help files) because it is quick, easy, and works on all operating systems and browsers. (recommended browser = Google Chrome). 

What is this tutorial about? 
  1. How to rapidly generate a column of numbers with a given difference (examples: all the integers from 1 to 100, or all the numbers from -20 to +40 by increments of 7, etc.).  
  2. How to perform rapid calculations on the entire column of numbers, specifically finding the slope from the definition: ( f(x+h) - f(x) ) / h 
  3. How to easily graph these results. 

Click HERE to go to the spreadsheet used in this tutorial. 

Note 1: Click FILE ==> DOWNLOAD AS to save it to your computer and open with your program of choice.
Note2: Click the tabs at the bottom of the spreadsheet. 

Tutorial begins here
Open a new spreadsheet.  

What we are going to do is quickly generate all the numbers from -10 to +10 via increments of 0.5.  
We will also automatically format the numbers such that they are rounded to two decimal places only. 
Then we will generate points on the curve y = x^2 , using our generated numbers as input, then compute the slope between each pair of points. 
Finally, we will graph both the points y = x^2 and the value of the slopes. 

Give the cell A1 a title (here it is "x where h = 0.5).  The contents of Cell A1 are only a title, it has no bearing on the manipulations we are about to perform other than to remind us what we're doing.  

Begin by selecting the column A and clicking "123" and selecting "2 decimals". (this ensures all numbers are rounded to two decimal places).  
Now start typing in your numbers, you only need to  do three or four in a sequence before the computer will understand what is going on.  
Here we have typed -10.00,  -9.50, -9.00, and -8.50.  This is all we need. 
Now select the four numbers.  Notice the little blue square in the lower right hand corner. 
Click that little blue square and drag it down as far as you need. 
The spreadsheet will notice that the sequence is increasing by steps of 0.5 and will fill in everything for you automatically. 
Now in cell B1 type y = x^2.  Again, cell B1 is just a title.  
The real action happens in cell B2.

In cell B2 type exactly this: 
=A2^2  
Or you type only the equal sign and then CLICK cell A2 (it will change color, and the corresponding color will appear in cell B2). 
This is saying "Take whatever is in cell A2 an square it". 
Press enter and if the value changes to 100, you are ready for the next step. 
Using the same trick as before, select cell B2 (which should display "100"). 
Now drag the little blue square down as far as you need. 
Everything should automatically be filled in, only the decimals will not all be rounded to the same number of places. 
To resolve this situation, click the letter "B" and the entire column B should be selected. 
Now click "123" and select "2 Decimals".  Now everything should be displayed rounded to two places. 
The definition of the derivative comes from the slope formula: ( f(x+h) - f(x) ) / ( (x-h) - x )  or, in prettier print: 
Here is a brief explanation of where this formula comes from. (it's a generalized slope formula)
(click for larger image)
Here we use the same trick as before, only our formula is a bit more complicated.  
Remember to start with the Equal sign and then enter in exactly what you see.
(Or click the relevant cells in the exact order they appear). 
After pressing enter, the spreadsheet will calculate the value. 
Select cell C3 and drag the little blue square down just as before. 

Now we are ready to insert a chart!  Select Everything from A1  to C42. 
Then click "INSERT==> CHART"
A box will appear which allows you to fine-tune your chart.  
Enter in appropriate names for labels, ranges, etc.  
This particular chart is a SCATTER chart with NO CONNECTING LINES. 
Click "SAVE CHART" when done. 

(click for larger image)

Click the chart for further options or to make changes. 

Note how the slope formula for the slope between each successive pair of points on the graph of x^2 yields a line which looks very much like y = 2x. 
We can see this immediately because the red line goes through (0,0) and (10,20), meaning it has a slope of 2. 
As it turns out, the derivative of y = x^2 is indeed 2x.