Activity #2

In this activity, you will gain hands-on experience working with real scientific data. You’ll learn how to properly handle datasets, use Excel functions, and perform basic data analysis. We’ll be using the “TCRMP-RAPID-Dec2017-Health-intercept.xlsx” file. This dataset contains coral health information collected from 6 sites in December 2017 using the TCRMP method.

Steps

1. Preparing Your Workspace

  1. Download the dataset from this link: TCRMP-RAPID-Dec2017-Health-intercept.xlsx
  2. Save the downloaded file in your MES503 folder.
  3. Make a copy of this file and rename it by adding “_analysis21aug2024” to the end of the filename. Example: “TCRMP-RAPID-Dec2017-Health-intercept_analysis21aug2024.xlsx”

TIP: This is good practice: keep one clean, unaltered version of your original data, and a second version with your analyses. This ensures you always have the original data to refer back to if needed.

2. Exploring the Dataset

  1. Open your renamed file.
  2. Notice that there are four sheets: “DATA”, and three metadata sheets.
  3. The “DATA” sheet contains information on over 500 corals.

Good Practice: Always take time to familiarize yourself with the structure and content of a dataset before beginning analysis.

3. Adding a New Column

  1. In the “DATA” sheet, scroll to the right until you reach the last column.
  2. In the next empty column, add the header “Species”.
  3. This new column will eventually contain the full species name of each coral.

Suggestion: When adding new columns, always give them clear, descriptive headers. This helps maintain the clarity of your dataset.

4. Using VLOOKUP

  1. In the first cell under your new “Species” header, you’ll use the VLOOKUP function.
  2. The VLOOKUP will search for the species code in one of the metadata sheets and return the full species name.
  3. If you’re unsure how to use VLOOKUP, refer to Appendix A or this VLOOKUP guide.
  4. Once you’ve written the VLOOKUP formula, copy it down for all rows in your dataset.

TIP: Here is where self-directed learning comes in! VLOOKUP is a powerful function in Excel that’s worth mastering.

Note: Are you seeing some NAs when using VLOOKUP? Consider using dollar signs ($) in your formula to define relative versus absolute references. Read more about this here.

5. Data Analysis

Now that you have the full species names, let’s analyze data for “Porites porites”:

  1. Count occurrences:
    • In a new cell, type: =COUNTIF(Species_column_range, "Porites porites")
    • Replace “Species_column_range” with the actual range of your Species column.
  2. Calculate total colony length:
    • In a new cell, type: =SUMIF(Species_column_range, "Porites porites", Length_column_range)
    • Replace “Species_column_range” and “Length_column_range” with the actual ranges.
  3. Calculate average colony length:
    • In a new cell, type: =AVERAGEIF(Species_column_range, "Porites porites", Length_column_range)
    • Again, replace the range placeholders with actual ranges.

Good Practice: When using functions like COUNTIF, SUMIF, and AVERAGEIF, always double-check that you’re referencing the correct ranges. A small mistake here can lead to incorrect results.

6. Challenge Question

Can you think of another way to get these summary metrics for Porites porites using Excel?

Suggestion: Exploring alternative methods to achieve the same result is a great way to deepen your understanding of Excel and data analysis in general. Don’t be afraid to experiment!