To calculate the Shannon-Weiner Diversity for each of the observed sites, first open a new Excel spreadsheet.
Label cell A1 ‘species name’, and cell B1 ‘total count’.
Starting from the second row and in column A, fill in the sheet for each species found in the first site, using the data recorded in Table 1.
Calculate the total number of each species found in the entire sampling effort and record this in the B column of the Excel spreadsheet.
Now, label cell G1 ‘total count of all species’, and in cell H1, write a formula, or highlight the appropriate cells to sum all of the count values entered in column B: = SUM(B2: BX)
Label cell C1 ‘proportion of each species’ and in cell C2, type the formula for the proportion of the total of each species at the site. = B2/H$1
Drag this formula down for each row of data represented in column B.
Label cell D1 ‘natural log proportion of species’ and in cell D2, type the formula for the natural log of the proportion of species at the site. =LN(C2)
Drag this formula down for each row of data represented in column C.
Add the formula into cell E1 for the proportion of species multiplied by the natural log of the proportion of species: ps* ln ps
In cell E2, type the formula multiplying the proportion of species by the natural log of the proportion of species at each site, and then drag this formula down for each row of data represented in column D. = C2 * D2
Label cell G2 as ‘sum of ps * ln ps’, and then, in cell H2, type a formula summing all of the values in column E. = SUM(E3 – EX)
Label cell G3 ‘Shannon-Weiner Index’, and in cell H3, enter the formula for the Shannon-Weiner Diversity Index. = (EXP – 1 * H2) NOTE: This is the calculated Shannon-Weiner Diversity Index for the first sampled site, and will be used for comparison between the two sites.
Repeat the data analysis in a new sheet using the data collected for the second site, which was recorded in Table 2. NOTE: Areas with greater index values are considered to have greater species diversity. Note whether you see differences between your sites.
Repeat the Shannon-Weiner Diversity calculations using a total count of all the species at each distance along the transect, then create a table of all of these values in a new Excel sheet.
Click on the Data tab in Excel, and then on the ‘Data Analysis’ button.
Select ‘Regression’ and click ‘Okay’.
For the Input Y Range, highlight the Shannon-Weiner Diversity values, and for the Input X range, highlight the distance values.
Select an output location for the regression results and click ‘Okay’.
Check the P-value box in the intercept row to see if there is a significant correlation between distance from edge to core habitat and biodiversity. NOTE: A value of less than 0.05 rejects the null hypothesis of no change in diversity along the transect, while a value greater than 0.05 does not reject the null hypothesis.
Next, to perform t-tests for determining soil composition differences between sites, open a new Excel spreadsheet.
In cell A1, type ‘Site 1:pH’, and in cell B1, type ‘Site 2:pH’.
Fill in cells A2 through A6 and cells B2 through B6 with the soil pH collected in the disturbed and undisturbed sites, respectively.
Click the Data tab in Excel and then select ‘Data Analysis’.
Now scroll down and select ‘t-Test: Two-Sample Assuming Equal Variances’ and select ‘Okay’.
Click the drop down next to Variable 1 Range and highlight cells A2 through A6 in the spreadsheet, then click the dropdown next to Variable 2 Range and highlight cells B2 through B6 in the spreadsheet.
In the box for hypothesized mean difference, type zero as our null hypothesis. This means that there is no difference between the two disturbance regimes.
Leave the Labels box unchecked and set the alpha value to 0.05.
Select ‘Output Range’, and in the box, click an empty space in the spreadsheet.
Click ‘Okay’. A table of t-Test results should be outputted in the spreadsheet.
The relevant data are the means for each of the sites and the alpha value. If this second value is less than our alpha value of 0.05, we can reject the null hypothesis that there is no difference in pH between the two sites, and the hypothesis that disturbed versus undisturbed sites differ in their pH value would gain support. If this value is greater than 0.05, however, we may not reject our null hypothesis.
Repeat this analysis for each of the other three soil variables, creating a new spreadsheet for each of the phosphorus, potassium, and nitrogen elements.
Note whether your pH and nutrient level results were different in each condition, and discuss with your group how this might affect the community and diversity in the different sites.