Don Cox, March 2007 1 Excel--An Introduction to Its Use, and Some Useful Uses By Prof. Donald P. Cos 2007 The purpose of this assignment is to prepare you for a numerical lab that uses Excel to solve two coupled differential equations. It doesn't matter at this point whether you have any idea what that means, but it might matter to you that the goal is to show you that, with a little patience, you can calculate just about anything with a computer. The use of the spreadsheet program Excel is one way to do it, one that is widely available. If you already prefer using some other programming scheme, you might later adapt what you learn here to that scheme, if that would make you more likely to use it when needed. A List of Useful Excel Skills--assuming you have started it up 1. selecting a cell (click on it) 2. naming a cell (click on it, then click on the box in the upper left part of the page where you see the initial name of the cell, then enter the name you want for it in that box, and hit "return"--Example: suppose you have clicked on cell B2, in column B and row 2. "B2" will appear in the name box. Change the name to Karen. Hit "return". Anything you now put into cell B2 will be known as Karen. Type 27 into cell B2. Click on cell D5 and type "=Karen" without the quotes. Voila, when you hit return, cell D5 will say "27".) 3. entering a number in a cell (see example of entering 27 in B2 above.) 4. entering an equation in a cell (see example of entering "=Karen" in D5 above) 5. entering a column of sequentially increasing numbers--learning to "fill down" (click on cell D6 and type "=D5+Karen/10", again-and always-without the quotes. Hit return. If cell D6 does not remain selected, select it again (depends on how preferences are set up, I think). Notice that there is a box around the selected cell and a spot of some sort on the lower right corner of that box. Click on that spot and drag down in column D by about 10 cells. You should find that the cells show a sequence of numbers starting with 27 in B5 and increasing by 2.7 in each step. Now go back to cell B2 [Karen] and change it to 10. Your sequence should now start with 10 and increase by one each as you go down. 6. getting a fresh start (close this workbook and if the computer asks if you want to save it, reply "no" or "don't save". Then go to the upper left menu item "File" and ask it for a "New Workbook". You are ready to start fresh without erasing a bunch of things (cell names are a bit tricky to get rid of). 7. getting a functional relationship (in cell A1 of your new worksheet, type "apple =", hit return. Select cell B1 and change its name to "apple". Then type 2 into cell B1. This makes apple=2, while the typing in cell A1 just reminds you that "Apple" is found in B1. Repeat this process with "pear =" in A2 and naming B2 "pear". Then type 4 into B2, making pear=4. Now, select C5 and type in "x". Similarly, type "y" in D5. We are going to have a look at some function y(x) in columns C and D below these headers. In C6 type "=0". In C7 we are going to arrange to have "=C6+0.1" but there is an easier way to do this once you get used to it. Select C7, type "=", then click on the cell right above--your equation has inserted the name of the clicked cell, then type "+0.1" and hit return. Fill this down for 10 cells, that is, grab the lower right corner of C7 [selected first if necessary] and pull it down until the x values run from 0 to 1.Don Cox, March 2007 2 Now we are ready to enter the equation we want in column D. Select D6, right next to the zero value of x, below the name y. We are going to enter "=apple + pear*C6^2". So, type "=apple +pear*", then poke C6, then type "^2". Hit return. The symbol "*" stands for multiply, the symbol "^" means "raise to the power". So our equation for y is y = apple + pear(times)(x squared). Is it obvious what to do next? You might think, "Ahh, I just have to grab the corner of D6 and drag it down to fill the other cells next to the values of x." But then you might lose your courage, thinking it will always fill down "=apple + pear*C6^2", when what you really need is for the "C6" name to change as you fill down. Try that first idea to see what happens! The "fill down" procedure somehow chose the value of x next to each y! Click on one of those y cells. You will see that if you selected D10, its equation will read "=apple + pear*C10^2". This is the true magic of a spreadsheet program. The equation "=apple + pear*C6^2" is really in the machine as "=apple + pear*(the value of the cell just to your left)^2". It is what is called a "relative reference". There are ways to prevent this, in part or in whole. [In D6, if you were to have typed "=apple + pear*$C6^2", it would be interpreted as "=apple + pear*(the value of the cell in column C on this row)^2". If you were to have typed "=apple + pear*C$6^2", it would be interpreted as "=apple + pear*(the value of the cell in the column just to the left but always in row 6 )^2". And finally, if you put $ before both column and row, it is a fully absolute reference, always meaning the value of cell C6.] 8. getting a graph of the function. (Select the cells C6 through D16 by clicking on C6 and then dragging down and over to D16. These contain the data you wish to graph. Then click on the Chart Wizard symbol in the row of tools above the worksheet. [It looks a little like a vertical bar chart on a Mac.] A Dialog Box will come up asking what sort of graph you want. Click first on the "XY(scatter)" choice in the left column, and then on the choice on the right that shows a smooth set of curves with symbols on the data points. Then poke "next" for further choices about your graph. There are 5 areas of choice, Titles, Axes, Gridlines, Legend, and Data Labels. Choose "Titles" if you aren't already there. In chart title, type "My Graph"; in Value x axis, type "x (meters)"; in value y axis type "y (mmHg)". Then poke "Axes" and make sure both primary axes are chosen. Then poke Gridlines and poke x axis "Major gridlines". Then poke Legend and poke "Show Legend" to turn it off. Skip Data Labels. Then hit Finish (if you hit "Next" instead, it will ask you where to put the chart). You should …
View Full Document
Unlocking...