EXCELV1

Goals:

1. Familiarization with Excel spreadsheets

2. Learn to use Excel to perform calculations, copy formulas, rapidly fill cells, and edit contents

3. Graphing equations and formatting texts and graphs

4. Compare observational data with model data

5. Learn about the world population growth

This activity assumes that you are familiar with basics of file management and will utilize some specific that we have covered in lecture.

Part 1: Creating a spreadsheet

In this section, we will start creating our spreadsheet. Text that you will need to type will be in bold and after entering text or numbers into a spreadsheet cell, you will need to press the enter key, which will be indicated by [E]. The direction arrows will also perform the same function.

1. Open excel. If you do not have a computer with you or do not have access to Excel, please look on with a classmate while working through this activity. You version of Excel may differ, but your worksheet will look similar to the one shown below; if it is different, it should not be a problem for this activity.

2. First enter some labels in cells A2-A4.

Select cell A2 by pointing on the cell A2 and clicking once.

Type in Name: [E]

The pointer is now in cell A3.

Type in Date: [E] and then Type in Assignment: [E] for cell A4

We use the shorthand notation below for the somewhat verbose instructions above to make this instruction guide easier to follow.

Instructions

A2: Name:

A3: Date:

A4: Assignment:

Note: the enter key is not explicitly included in our shorthand notation but should be pressed.

You may want to change the column width if your entries are too long for the default widths. You can do this by moving the cursor to the line between A and B and clicking and dragging the width to a new size.

3. In cells B2 - B4 enter your name, date, and “World Population” for the assignment. So far, your spreadsheet should look similar to:

At this point, it would be a good idea to save your work. Newer version of excel save with the file extension “.xlsx.” You may encounter older data formats with an “.xls” extension. Newer version of excel should be able to read these older versions, but there can be compatibility issues occasionally.

Part 2: Repetitive calculations with Excel

1. In this assignment we are going to create a table of values for world population from 1950 to 2050 assuming that it obeys the theoretical logistic growth function:

Here A is the population at any time t, Ao is the initial population at t=0 [1950 in our case], r is the growth rate, and K is the maximum population able to be sustained by the Earth.

Let’s assume that we know Ao and r from measurements, and will use this analytical model to calculate the population for different values of time after 1950 (t=year-1950). Later we’ll use this table to make a graph of the World Population (predicted from the logistic growth model) from 1950 to 2050.

2. Instructions

In cells A6 - A8 enter:

A6: Initial population

A7: Carrying capacity

A8: Initial growth rate

In cells B6 - B8 enter:

B6: Ao=

B7: K=

B8: r =

In cells C6 - C8 enter:

C6: =2.25

C7: =100

C8: =10

(the equal signs for C6, C7, & C8 are important) These original values are Very unrealistic for the Earth but we’ll assume that they are okay for Planet X.

Your spreadsheet should now look like:

Great! Now let’s modify the data labels a bit.

First, Select cells B6 - B8 by: pointing on B6, pressing and holding the mouse button, dragging the pointer to cell B8. (B6 - B8 should now be highlighted.)

From the Home Tab click on the right justification icon under alignment.

With these cells still selected: Click on the box labeled [B] in the tool bar under the Home Tab (font) to make your labels in B6-B8 bold. Alternatively you can press ctrl + B ( + B on macs) to embolden the text.

Make the labels in cell A2 - A4 Bold by following the same procedure as above.

Select cells C6 - C8 and left justify these cells using the left justify icon.

OOPS! We made a mistake. We forgot to put units on the labels in cells B6 - B8. Units are always very important. Although Excel can only do calculations with numerical values, indicating what units these values are in is important so that the numbers are useful. No problem, we'll do it now.

Select cell B6. Position the cursor between the o and = sign of the formula bar at the very top of the screen and click. Now type (Billions) then [E]. This should give you Ao (Billions)= in cell B6. In the same way include units for B7 (Billions) and B8 (%/yr).

Use the Home tab Number command to increase or decrease the number of decimal places used. Select 2 for C6, C7, and C8 so your number appear as 2.25, 100.00, and 10.00 respectively.

In cells B10 & C10, enter:

B10: Year

C10: Population (Billions)

Make these labels bold and align them in the center with center alignment.

3. Here’s a sample problem that we’ll solve using the Excel environment:

Given that the initial 1950 population is 2.25 billion people, the human carrying capacity of planet X is 100 billion people, and the growth rate is 10% per year, what is the estimated planet X population in 2000?

Instructions

Enter B11: =2017 as the year. We will then use excel to put this value into our formula to be calculated as the population at this time.

Enter C11:

=C$6* EXP(C$8*0.01*($B11-1950)) / (1+(C$6*(EXP(C$8*0.01*($B11-1950))-1) / C$7) )

(again make sure to include the = sign and hit the enter key). This formula corresponds to:

= Ao e0.01r(t-1950) / [ 1 +{ Ao (e0.01r(t-1950) ) -1} / K]

^ The $s in the Excel version are optional right now but leave them in because they will be very useful soon. The 0.01r is needed if r is in %/year and the * is the multiply by symbol.

If everything works correctly when you change B11 from 1950 to 2017 the value of C11 goes from

2.25 billion to 94.92 billion. (This is way too high for Earth but it may be right for Planet X.)

When you change B11 from 2017 to 1980 the value of C11 becomes _____________?

The above is great because we have created a calculator that allows us to put any value of time into cell B11 and have Excel calculate the population of Planet X for that year and display it in cell C11. We can also change the values of Ao, K, and r by entering new values in C6, C7, or C8. This is a very handy tool for evaluating an equation. The advantage of this over a calculator is once the formula is typed in correctly it can easily be used over and over again. Another advantage is that if you type in the formula incorrectly, you don’t have to start all over to fix it but can simply edit it in the formula bar.

Part 3: Making a table of values from an equation

It gets even better! We can use what we have to make a table theoretical of Planet X’s world population from 1950 to 2050. This will make it easy to graph our results to help us visualize the behavior of this equation over a large range of years.

1. Instructions

The first step is to generate a bunch of years between 1950 and 2050 that are equally spaced.

Enter:

B11: 1950 (you should change it back to 1950 if you had change B11 to something else. )

B12: 1955

•

•

B31: 2050

WAIT!!!!!!!!!!!!!!!!!!!!!!!!!!!!

You can do this an easy way or a hard way.

a) The hard way is to enter each number into each cell one at a time.

2) The easy way is to enter:

B11: 1950

B12: = B11 + 5

Select cells B12 through B31 (highlight them with the mouse by clicking and dragging)

Use the Home tab Editing Fill-Down command from the Edit file menu at the top of the Worksheet. Alternatively, after selecting B12 through B31, you can use ctrl + D to fill in the cells.

Another easy way to fill down the equation written in cell B12 is to type it into B12 and press [E]. Then carefully move the mouse pointer to the lower right corner of cell B12 and when the pointer changes to a solid pointer you can click and then drag it down to cell B31. This automatically fills the cells B13 to B31 with the B12 formula.

Notice that when we used the Fill-Down command the equation that is in cell B12 (= B11 + 5) is copied into cell B13 as (= B12 + 5). The cell assignment is relative as the equation is copied. Another way of looking at it is that the equation in B12 can be read as, add 5 to the cell above this location and make that the value of this cell.

2. Let’s enter the equation for the Planet X World population for the years 1950 to 2050 into cells C12 through C31. Do this by copying (Fill Down) the formula in cell C11 to cells C12 through C31. Select cells C11 - C31 and use the Edit Fill Down command. That's all there is to it! Format Cell-number to two decimal places in C11 to C31.

Select cell C12. Notice that the $ in front of the row or column label fixes the row or column (or both) so that when the formula is copied the fixed cell reference does not change. This is a fixed cell reference. Depending on what you want to do, both variable and fixed cell references are very useful. The c$6 reference will always go to row 6 of whatever column you’re in. Whereas the $b11 reference will always go to column B of whatever row you’re in (the row will be relative). Click on C12. In the formula bar C12 has:

=C$6*EXP(C$8*0.01*($B12-1950))/(1+(C$6*(EXP(C$8*0.01*($B12-1950))-1)/C$7))

Remember C11:

=C$6*EXP(C$8*0.01*($B11-1950))/(1+(C$6*(EXP(C$8*0.01*($B11-1950))-1)/C$7))

For each time value in column B a population is calculated in column C.

Cells C11 - C31 now contain the Planet X world population for the years 1950 to 2050.

This would be a good time to use the Move or Copy Sheet-Create copy command under Format from Cells region of the home tab. Make sure Sheet 1 is selected and check the box to create a copy. This will give you two sheets of what you just created so you can modify one of them following the instructions in Part 3b and not worry about losing what you’ve already done. Resave the whole worksheet again.

Part 4: Making a graph of your results

To make a graph of Planet X Population (C11 - C31) on the y-axis versus time (B11-B31) on the x-axis

Select cells B11 through C31 (2 columns)

Click on the insert tab, and then Scatter under the charts region. Select scatter with straight lines and markers. Your scatter plot button may appear different from what is displayed here depending on your excel version.

* The x-axis values are always in the first column on the left

You can enter the title and axes labels by first selecting the chart and clicking on the green + to the top right of the chart and selecting Axis Titles and Chart Title. Also, turn off the legend if it is selected.

You can move the chart window to a new position so the table and graph are both clearly visible.

Locate the mouse pointer inside the chart, press and hold the mouse button, and drag the window to its new position. TRY THIS!

Move the chart window to the right side of the monitor screen so it and columns B and C can be seen.

You can change the size of the chart window by pointing on a corner of the window and dragging this corner to its new position. TRY THIS!

Now let’s edit the chart labels:

Double click on the x-axis label and change the text to: Year

For the y-axis label use: Planet X population (billions)

Make up your own title.

Right Click on the y-axis to select it. With the y-axis selected, use the Format axis option to set the minimum y value to 0 and the maximum to 100.0.

Doing this manually fixes the scale of the graph so that when you make changes in the input data the scale doesn't change.

In the same way change the scale for the x-axis to from 1950 to 2050.

Resave your worksheet.

Carefully sketch your graph of Population versus year on the axes provided below. Label both axes, include units, and title the graph. Call this graph 1

With the worksheet selected, change r(cell C8) from 10 to 5.

Reselect the chart and sketch it on the same set of axes as above. Use a different color or line type to clearly and unambiguously distinguish your two curves and use a legend to the right of your graph above to indicate which line is which. Call this graph 2.

With the worksheet selected, change r(cell C8) from 5 to 20. Sketch this on the same set of axes and make sure that this line is clearly labeled. Call this graph 3.

Let’s use Excel to check your answer for the sketch above and put three lines on one set of axes.

Select cells C6 through C31 [highlight these cells], copy them with [ Edit-Copy ] or [Ctrl] + [C], and then select cell D6 and paste with [ Edit-Paste ] or [Ctrl] + [V].

Select cell E6 and Paste again. Because of the fixed and relative references used, the year is always in column B and populations will be in columns C, D, and E. For each column calculation initial population, carrying capacity, and growth rates are in either C6-C8, D6-D8, or E6-E8 respectively.

Cell D11 has the formula

=D$6*EXP(D$8*0.01*($B11-1950))/(1+(D$6*(EXP(D$8*0.01*($B11-1950))-1)/D$7))

and E11

=E$6*EXP(E$8*0.01*($B11-1950))/(1+(E$6*(EXP(E$8*0.01*($B11-1950))-1)/E$7))

Changing C6 –C8 to (2.25, 100, 10) will give you numbers in column C for graph 1 above

Changing D6 –D8 to (2.25, 100, 5) will give you numbers in column D for graph 2 above

and

Changing E6 –E8 to (2.25, 100, 20) will give you numbers in column E for graph 3 above

To add a new line to the chart, select the chart, right click, and click the Select data item and use the Add option.

When asked for the Series x-values select B11-B31 on the worksheet by clicking and dragging with the mouse. Do the same for the series y-values.

Repeat for your third line E11-E31, first choosing select data, and adding a new series.

You’ll have to select the edit option from Select data item to change the name of your first line (10%). Rename the labels to r=10%, r=5%, and r=15%, corresponding to the order that we plotted the lines.

Remember, you can force the x-axis to go from 1950 to 2050. The same technique that we used before to force the y-axis to go from 0 to 100.

With 3 lines on one graph you may want a legend to help identify.

Using the Green + to the top right of the chart, select the legend.

Part 5: Calculating an average

Finally, we are going to explore some of the built in excel functions. We are going to calculate the average trend of our three lines.

In F11, enter =AVERAGE(C11:E11) and [E] to calculate the average of these cells for 1950. Excel has a number of built in functions. To the left of the formula bar, you will notice the insert function button.

Click on the insert function button to view a list of potential functions that can be used for quick calculations, such as the SUM, MAX, or MIN commands.

Fill in the remaining F11:F31 cells using the fill down command.

Plot the average line as a 4th trend on your chart and label it in the legend as Average.

Questions:

1. How does an increase in initial growth rate (r) influence:

The time it takes Planet X to get reach its carrying capacity of 100 billion?

The shape of the population curve?

2. Was the average trend what you were expecting? Did you think it would resemble the r=10 line?

Graphs: After you get your graph of population for the three initial growth rates and average rate, change the symbols for each line by clicking on one of the lines, right clicking, selecting format data series, select the fill and line tab and then select Marker. Under marker, select marker options and change the type of marker. Change each line’s marker such that they can be differentiated even if all the lines were the same color.

Once you think your graph is in good shape print it out and turn it in in the following class. An easy way to print would be to copy the chart and paste it into a word document rather than trying to print it directly from excel.

Also include a print out of your data table fur the following class.