Applications

Spreadsheets are used in a variety of applications in our fields, including fisheries. For example, migrating smolt may be captured in a screw trap, identified to species, measured, and weighed to keep tabs on population trends. Often a subsample of fish will be marked. We can calculate how effective our trap is (efficiency) by tracking marked fish.

Trapping efficiencies are calculated with the following formula:

[latex]E = R/M[/latex]

Where E = trap efficiency, R = the number of marked fish recaptured, and M = the number of marked fish
released.

Efficiency will vary by river conditions, so if we track conditions like flow and turbidity, we can examine how these factors affect trapping efficiency. This is most easily accomplished in a spreadsheet!

Once you are comfortable rearranging them, you’re ready to use formulas in a spreadsheet application (like Excel or Google Sheets). Spreadsheets allow us to perform calculations on data sets quickly!

In a spreadsheet, formulas include the following elements:

• Equal sign (=): Indicates the beginning of a formula.
• Values: Numbers used in the calculation.
• Cell references: References to data in other cells. These generally represent the variables in the formula.
• Operators: Mathematical symbols (+, -, *, /) to perform calculations.
• Functions: Built-in formulas for specific calculations (e.g., SUM, AVERAGE, COUNT).

To enter a formula in a spreadsheet, select the cell where you want the result, type the equal sign (=), enter the formula using values, cell references, operators, or functions, and press enter.

For example, to add the values in cells A1 and B1, you would write: =A1+B1

As you work in spreadsheets, remember to use parentheses to control the order of calculations.

Save time by copying formulas from one cell to the adjacent using the fill handle. Cell references are relative. This means that they’ll move to refer to other cells if you copy the formula or move it. To keep the cell reference (or part of it) from moving, use the $ symbol. For example, $B$3 will always refer to cell B3. B$3 will stay on row 3, but if you copy the formula, it will move from column B to C, etc.

Depending on your spreadsheet application, it may offers a variety of functions for various calculations. Some common ones include:

• • SUM: Adds values
  • AVERAGE: Calculates the average
  • COUNT: Counts numbers
  • MAX: Finds the largest value
  • MIN: Finds the smallest value

To calculate the sum of values in cells A1 to A5, you would write: =SUM(A1:A5)

The most straightforward way to learn how to work in a spreadsheet is to watch and follow along in a spreadsheet on your own. The link below provides a good start.

52-minute short course in Excel formulas

Our examples should help too!