485 Math Spreadsheet Lab Activity 1
Lab description: In this lab activity, we will investigate some of the basic functions when using spreadsheets. In particular, we will use the spreadsheet software Excel. It has good graphing capabilities and several valuable data analysis tools. In the scientic world (outside of mathematics) most researchers use spreadsheets as their main graphical and analytical tool. We will use Excel to create a report style presentation such as the one seen here:
Graphing in Excel - A Sample Problem
x -5 -4.5 -4 -3.5 -3 -2.5 -2 -1.5 -1 -0.5 0 0.5 1 1.5 2 2.5 3 3.5 4 4.5 5
f(x)=x^2*SIN(x) 23.97310687 19.79498488 12.10883992 4.297094539 -1.270080073 -3.740450901 -3.637189707 -2.24436372 -0.841470985 -0.119856385 0 0.119856385 0.841470985 2.24436372 3.637189707 3.740450901 1.270080073 -4.297094539 -12.10883992 -19.79498488 -23.97310687
f(x)=x^2*SIN(x)
20 15 10 y-axis 5 0 -5 -4 -3 -2 -1 0 -5 -10 -15 -20 x-axis 1 2 3 4 5 x^2*SIN(x)
As you can see by the graph above, the limit of f(x) as x goes to zero is zero. This can be proved using the squeeze theorem. For more information, please see the attached document.
First, we would like to know how to graph functions using Excel. Excel is not ideally set up to create graphs for complicated functions, but it is excellent for adding labels and for creating good presentation of data and simple functions. To do this I have provided a template for you to download and to work with. Download the spreadsheet SpreadsheetLab1.xls There, I have provided 4 sheets for you to work on.
Part 1: Using Sheet 1, we will produce a document which will look like the one above. In this excercise we will use Excel to graph the function f (x) = x2 SIN (x) from [5, 5] To do this follow these instructions: 1. Starting with Sheet 1, the rst thing we need to do is to discretize the domain [-5,5]. We will use a step size of dx=0.5. Click on the cell A6. Once in that cell enter = A5 + 0.5. This should put -4.5 in the cell. 2. Make sure that cell A6 is highlighted and place the cursor near the lower right-end corner of the cell. You will see the cursor change shape between a fat cross to a hand to a skinny cross. When you have a skinny cross, click the mouse and drag it down to the end of the line (should be cell A25). This should ll in the discretized domain from [-5,5]. To make your formula easier to understand, we want to take advantage of the variable naming feature in Excel. 3. To do this, highlight the x column including the x cell (from A4 to A25). 4. Go to the Insert menu at the top and click on Name followed by Create, then choose Top Row and press ok. This creates the name x for each value in the column. 5. Now click on Cell B5. 6. Now enter the following command = x2 SIN (x). The value 23.97310687 should appear. 7. Again, move the cursor to the lower right-end corner until you see a skinny cross. Click and drag down the column until cell B25. This should put all the function values into the chart. Now we will use Excels plot capabilities to plot this function on the given interval. 8. Highlight the data to be graphed (columns A and B from rows 5 to 25). Then click on the Chart Wizard icon on the top toolbar Chart Wizard Icon. 9. This will open up a menu which looks like the following:
2
10. Click on the XY (Scatter) and select the smooth continuous curve choice. It should look like:
11. Click on Next. This will take you to formatting options. 12. The next screen asks about Data Range. Click on the Series option at the top. This is where you get to name your function. This is important when there are multiple plots on one graph. Click in the name slot and enter My Function. So it will look like this:
3
13. Click on Next. 14. In the next menu, you will be given the option to lable the graph, the x-axis, the y-axis, change the axis and gridlines. Give your Chart title the name Plot of f(x), the Value (X) axis the name x-values, and the Value (Y) axis the name Amplitude. 15. Now click on Gridlines on the top menu. You can click on the options here to see how it would aect your graph. Uncheck all entries (namely the (Y) Major gridlines). Feel free to browse the other options but dont change any of them. Click on Next. 16. The next menu is for Chart Location. No changes needed here, click Finish. 17. The graph should appear on your spreadsheet. You can shrink it and place it next to the table list on your spreadsheet. 18. will You get something like this:
4
19. Now you can change the axes on the graph itself. To do this move your cursor over the x-axis and (double) click on it. It will bring up the Format Axis menu (note this is for the x-axis, we will do the y-axis next). Change the Minimum and Maximum to -5 and 5 respectively. Note that this will unselect the auto button. Press OK. 20. Now to change the y-axis, click on it and a similar menu will appear. Change the Minimum and Maximum to -20 and 20 respectively. Press OK. You should now have a graph which looks like the following:
21. If you double click on the edge of the Chart Area, you will open up the Format Chart 5
Area menu. Here, under the Line options, click on the black color rectangle and select no line. Press OK. This removes the box outline of your chart. At this point your spreadsheet should look like this:
22. Now you can insert a text box if you want. No need for this exercise. One of the neat things about the graphing capabilities in Excel is that if you change your function, the plot will update automatically. 23. For example, change your formula in cell B5 to = x2 COS(x). This should cange the entry in cell B5. Click on the lower right-end corner (with the skinny arrow) and drag the selection down the list. This will change all the values to correspond with this new function. Now just change the entry in cell B4 to correspond to the function f (x) = x2 COS(x). You should have a page which looks like:
6
Part 2: In this part of the lab, we will reproduce the results found in section 1.2 of the text. Use sheet 2, and follow these instructions: 1. First, we must ll in the population column. To do this, click on cell B8 and enter = B7 + 0.0194 B7. The number 101.94 should appear. Note that the population entries in the text have been rounded to the nearest whole number. 2. Make sure that cell B8 is highlighted and place the cursor near the lower right-end corner of the cell. You will see the cursor change shape between a fat cross to a hand to a skinny cross. When you have a skinny cross, click the mouse and drag it down to the end of the line (should be cell B21). This will ll in the appropriate Sandhill Crane populations. 3. Now graph the population by highlighting the cells A7-A21 and B7-B21, then click on Chart Wizard and select XY (Scatter). Choose the smooth continuous option. 4. You can then rename the graph, the axes, and choose no grid lines. Then, you can specify (by double clicking on the x-axis) the domain to be from t = 0 to t = 14. You can reproduce the following graph:
7
This should match the graph illustrated on page 12 in the text. Part 3: In this section we will discuss Relative and Absolute Addressing. We will reproduce the Sandhill Crane Model populations with three dierent growth rates. 1. Using sheet 3, you will notice that the three dierent growth rates are given in cells B4-B7. 2. First, we will ll in the entries in column B. 3. To do this, highlight entry B8 and enter = B10 + B10 $C$7. The $ (dollar sign) around C and follwed by 7, says that the number in that cell is an absolute address. This will allow you to just change one entry when changing the growth rate in the model. 4. Place the cursor near the lower right-end corner of the cell, and when the skinny cross appears drag down to the end of the column (should be cell B24). 5. Repeat this procedure for the Medium case and Worst case. You will have a sheet which should look like:
8
6. Now we will graph all three cases on the same graph. 7. Highlight A10-A24 through D10-D24. Click on Chart Wizard and choose XY (Scatter). Instead of choosing the smooth continuous curve, choose the one to the left of it with the data point included. On your own, enter the appropriate labels to make a graph which looks like:
8. Save your results. Now using another page on sheet 3, do the following excercise: 9. Create a similar page but with dierent growth rates and time scale. Let the growth rates for the second part of sheet 3 be the following values: Best 2=0.023, Medium 2=-0.035, and Worst 2=-0.056 10. Extend the time scale to be over the next 30 years. Label your graph appropriately,
9
and adjust the scale as needed. You should get something like this:
Part 4: Lab credit: Save and print your lab results. This should be four pages. One page for part 1, one page for part 2, and two pages for part 3. Turn these pages in along with homework 1.
10