Excel Supreme
Search

Project track · Project 2 of 4

Trout river case

Turn a field log into the read a river trust would put on one page.

Intermediate

The brief

  1. The file

    trout.xlsx holds one sheet, Catch, with fourteen fish logged by three anglers over two beats of one river. Columns A to F are the date, the beat, the angler, the species, the length in centimetres and whether the fish was released. Rows 17 to 20 compute how many fish were logged, how many went back, the longest fish and the mean length.

  2. The work

    A river trust wants a one-page read of the season: which beat is producing, which species is being kept, and whether the lengths say anything. All eight tasks answer from this one sheet. This is the case-style project. The questions are the kind a volunteer coordinator would ask, and each one is a filter and a count over the same fourteen rows.

  3. Where people go wrong

    Two traps. 'Released' is text, so counting the word yes does not count fish that were kept. Do the subtraction rather than guessing. And the beat column repeats, so a total per beat is three or four rows at a time, never the whole column.

trout.xlsx · Catch

screen 1 of 8 · answer in the bar

How many fish does the log record? B17 states it.

Download the dataset (.xlsx) It opens in Excel, Sheets or LibreOffice.

Preview the sheet
ABCDEF
1DateBeatAnglerSpeciesLength (cm)Released
22024-04-06UpperHaleBrown trout28yes
32024-04-06UpperOkonkwoRainbow trout31yes
42024-04-13LowerHaleBrown trout22no
52024-04-13LowerReyesBrown trout34yes
62024-04-20UpperReyesBrook trout19yes
72024-04-27LowerOkonkwoRainbow trout26yes
82024-05-04UpperHaleBrown trout41no
92024-05-11LowerReyesRainbow trout29yes
102024-05-18UpperOkonkwoBrook trout17no
112024-05-25LowerHaleBrown trout24yes
122024-06-01UpperReyesBrown trout38yes
132024-06-08LowerOkonkwoRainbow trout33no
142024-06-15UpperHaleBrook trout21yes
152024-06-22LowerReyesBrown trout27yes
16
17Fish logged14
18Released10
19Longest (cm)41
20Mean length (cm)27.9

The sheet computes B17 =COUNTA(D2:D15), B18 =COUNTIF(F2:F15,"yes"), B19 =MAX(E2:E15), B20 =ROUND(AVERAGE(E2:E15),1)

What it does

COUNTA counts the species cells, one per fish, which gives the number of entries in the log. COUNTIF asks how many of the released cells say yes. That answer is a count, not a length: the fish that were kept are what is left of fourteen once ten went back.

MAX and ROUND(AVERAGE(E2:E15),1) describe the lengths without needing a second sheet. Every remaining task applies those three ideas to a subset: filter a column to one value, then count or add what stays.

Syntax

=COUNTA(D2:D15), =COUNTIF(F2:F15,"yes"), =ROUND(AVERAGE(E2:E15),1)

Worked example

Common errors

ErrorWhat it means and how to fix it
Counting yes to answer a keeled questionTen fish were released, so four were kept, not ten. Read which side of the question the yes counts belong to.
Whole-column totalsA beat's total is three or four rows. Summing the beat column and comparing numbers per row is the error this project is built to catch.

At work

A field log, a support queue export and a sales sheet are the same table with different words. 'Which subset is producing, and how big is what it produces' is the question a report answers, and it is always a filter and an aggregate.