Data Analysis: Individual Plots

Using A Computer Spreadsheet
Calculating Coverage Percentages for Individual Plots

Making a Spreadsheet for Individual Plots

1) Set-up the column titles across the top.

Open the spreadsheet tool in Claris Works by double clicking on the spreadsheet icon.
Click on the first square under column A.
In cell A1, type the word "natives".
Hit the tab key, and the B1 square should be highlighted.
In cell B1, type the word "diameter in" .
Hit the return key, and the B2 square should be highlighted.
In cell B2, type "sq. cm" and hit the return key. The whole title for this column could take up too much room and make this column too wide, therefore we will use two squares for the column title.
Highlight square C1 and type "native area" and hit the tab key.
Make three more columns using the same procedure for the non-natives.

2) Fill in the natives column (A).

Use the mouse to click on square A3. (Skip line 2 because we used it as part of the column title for B2.).
Type the name of the first native plant from your mapping grid along with the plant number you gave it on your map.
Enter the name of your next native plant and its identification number in the next square (A4). Each plant is entered into its own row, even if it is the same type of plant as one already listed. Continue down the column until all of the native plants from your plot have been listed.

3) Fill in the diameter in sq. cm column (B).

Click on the B which will highlight the entire B column.
Under Format on the Menu bar, click on numbers. This shows a box with choices of how to display numbers in this square. Fixed will display number in regular fashion. Put the arrow in the circle next to the word "Fixed" and click.
You will notice a box highlighted at the bottom of this window with a number in it next to the word Precision. Put a number 2 in the box; this means that the decimal in this square will have up to two places.
Highlight the B3 square.
Type in the diameter of the plant that is listed in A3 and hit return.
Continue down the column typing in the diameters for the corresponding plants listed in column A.

4) Fill in the native area column (C).

Format this column the same way you did column B.
Highlight the C3 square.
You will now give the computer a formula that will enable it to calculate the area covered by the plant listed across in column A. The formula for the area of a circle is shown below:

Area of a Circle = x radius ² or x (diameter/2)²

We know that pi ( ) is a constant equal to 3.14 and the diameter of the plants are listed in column B, and that a number squared is the same as that number multiplied by itself. We can rewrite the equation as

Area of Coverage for plant 1 = 3.14 x diameter(found in column B)/2 x diameter found in column B/ 2.

Type =B3/2*B3/2*3.14 and hit return. This is telling the computer to take the number in B3, divide it by two to get the radius. This number is multiplied by itself and then by pi.
Double check the answer in this square by using a hand calculator. If you did not get the same answer call the teacher over to see what went wrong.
You computer should have square C4 highlighted if you hit the return key after typing in the formula. To copy the area formula to the squares in the rest of this column, click on square C3 and go under the Edit Menu and click on Paste. This computer will automatically change the square numbers to the correct row and should get an area value calculated for the plant in the A column on this row. Hit return. Again check with your hand calculator and call the teacher if you are have problems.
Continue down the column by highlighting the next square and clicking on paste under the Edit menu. You do NOT have to select copy each time.

5) Fill in columns D, E, and F.

Use the same procedure described in steps 2-4 to fill in the columns for the non-native plants. In column F, you can paste the formula you copied for column C. You shouldn't need to hit copy, but just paste again to get it all of the squares.

6) Calculating Totals

Leave three blank rows after the last plant row is listed.
In column A, type "Total natives".
In column B, type "Area". Again this is because the words would be too wide for one column.
In column C, a formula will be used to add up all of the areas of native plants. The formula is: =sum(C3...C?) (The ? Means whatever number is just about the total natives area row) This tells the computer to sum or add all of the numbers in the squares between and including the first and last squares.
Repeat step 6 for the non-natives. Be sure to change the formula to the F column or copy and paste and the computer should change it for you.

7) Calculating Percentages.

Skip one row under the totals.
In column A, type "%natives"
Under column C, put in a formula that twill calculate the percent of natives in your plot. =C?/90000*100 BREAK This tells the computer to take the number in the total area square and divide it by 90000 and then multiply that number by 100. 90000 came from multiplying 300 cm by 300 cm (the size of the plot). Remember that 3 meters = 300 centimeters. The division is multiplied by 100 to change the number from a fraction to a percentage.
Repeat this procedure for the non-natives column. Remember you can copy and paste the formula.

8) Save and Print.

Go under the File menu. Click on Save As. Give your spreadsheet a name that you will remember and click on the Save square.
Go under the File menu. Click on Print. All settings should be properly set, so just click on the Print box at the top.
Write your data on the board under your plot number.
Make sure your name and your partner's name are on the upper right-hand corner and place your paper in the basket.

Sample Spreadsheet

Here is a sample spreadsheet for you to look at. Yours should look similar.