Project track · Project 1 of 4
UFO sightings dashboard
Read a raw sightings log, find the rows behind its summary, and report what it says.
Beginner
The brief
The file
ufo.xlsx holds one sheet, Sightings, with fourteen logged sightings. Columns A to F are the date, the city, the state, the shape reported, how long the sighting lasted in seconds, and how many witnesses were present. Rows 17 to 20 carry a summary block the sheet computes for you. Open the file in Excel, Sheets or LibreOffice and the numbers in B17 to B20 are already there.
The work
You are the analyst the report goes to. Eight tasks: read the summary block, find the rows behind it, and answer in the box under the sheet. The preview under each task shows the same values the file shows, so nothing here needs a download. The download is there when you want to sort the rows yourself, which is how most of these are quickest.
How it is marked
Each answer is checked the moment you submit it, and a wrong answer tells you what to look at rather than only that you were wrong. Nothing is timed and you can leave and come back: the project keeps its place.
ufo.xlsx · Sightings
screen 1 of 8 · answer in the bar
How many sightings does the log hold? B17 states it for you.
Preview the sheet
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Date | City | State | Shape | Duration (s) | Witnesses |
| 2 | 2023-03-04 | Phoenix | AZ | Triangle | 120 | 3 |
| 3 | 2023-03-11 | Portland | OR | Light | 45 | 1 |
| 4 | 2023-04-02 | Phoenix | AZ | Disc | 300 | 5 |
| 5 | 2023-04-19 | Austin | TX | Light | 60 | 2 |
| 6 | 2023-05-06 | Portland | OR | Triangle | 180 | 4 |
| 7 | 2023-05-23 | Denver | CO | Orb | 90 | 2 |
| 8 | 2023-06-10 | Austin | TX | Light | 30 | 1 |
| 9 | 2023-06-27 | Phoenix | AZ | Orb | 240 | 6 |
| 10 | 2023-07-14 | Denver | CO | Triangle | 150 | 3 |
| 11 | 2023-07-30 | Seattle | WA | Light | 75 | 2 |
| 12 | 2023-08-16 | Austin | TX | Disc | 210 | 4 |
| 13 | 2023-09-02 | Seattle | WA | Orb | 105 | 1 |
| 14 | 2023-09-19 | Phoenix | AZ | Light | 55 | 2 |
| 15 | 2023-10-05 | Portland | OR | Disc | 320 | 5 |
| 16 | ||||||
| 17 | Sightings | 14 | ||||
| 18 | Witnesses | 41 | ||||
| 19 | Mean duration (s) | 141.4 | ||||
| 20 | Longest (s) | 320 |
The sheet computes B17 =COUNTA(B2:B15), B18 =SUM(F2:F15), B19 =ROUND(AVERAGE(E2:E15),1), B20 =MAX(E2:E15)
What is the total number of witnesses recorded across every sighting?
Preview the sheet
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Date | City | State | Shape | Duration (s) | Witnesses |
| 2 | 2023-03-04 | Phoenix | AZ | Triangle | 120 | 3 |
| 3 | 2023-03-11 | Portland | OR | Light | 45 | 1 |
| 4 | 2023-04-02 | Phoenix | AZ | Disc | 300 | 5 |
| 5 | 2023-04-19 | Austin | TX | Light | 60 | 2 |
| 6 | 2023-05-06 | Portland | OR | Triangle | 180 | 4 |
| 7 | 2023-05-23 | Denver | CO | Orb | 90 | 2 |
| 8 | 2023-06-10 | Austin | TX | Light | 30 | 1 |
| 9 | 2023-06-27 | Phoenix | AZ | Orb | 240 | 6 |
| 10 | 2023-07-14 | Denver | CO | Triangle | 150 | 3 |
| 11 | 2023-07-30 | Seattle | WA | Light | 75 | 2 |
| 12 | 2023-08-16 | Austin | TX | Disc | 210 | 4 |
| 13 | 2023-09-02 | Seattle | WA | Orb | 105 | 1 |
| 14 | 2023-09-19 | Phoenix | AZ | Light | 55 | 2 |
| 15 | 2023-10-05 | Portland | OR | Disc | 320 | 5 |
| 16 | ||||||
| 17 | Sightings | 14 | ||||
| 18 | Witnesses | 41 | ||||
| 19 | Mean duration (s) | 141.4 | ||||
| 20 | Longest (s) | 320 |
The sheet computes B17 =COUNTA(B2:B15), B18 =SUM(F2:F15), B19 =ROUND(AVERAGE(E2:E15),1), B20 =MAX(E2:E15)
What is the mean sighting duration in seconds, rounded to one decimal? B19 holds it.
Preview the sheet
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Date | City | State | Shape | Duration (s) | Witnesses |
| 2 | 2023-03-04 | Phoenix | AZ | Triangle | 120 | 3 |
| 3 | 2023-03-11 | Portland | OR | Light | 45 | 1 |
| 4 | 2023-04-02 | Phoenix | AZ | Disc | 300 | 5 |
| 5 | 2023-04-19 | Austin | TX | Light | 60 | 2 |
| 6 | 2023-05-06 | Portland | OR | Triangle | 180 | 4 |
| 7 | 2023-05-23 | Denver | CO | Orb | 90 | 2 |
| 8 | 2023-06-10 | Austin | TX | Light | 30 | 1 |
| 9 | 2023-06-27 | Phoenix | AZ | Orb | 240 | 6 |
| 10 | 2023-07-14 | Denver | CO | Triangle | 150 | 3 |
| 11 | 2023-07-30 | Seattle | WA | Light | 75 | 2 |
| 12 | 2023-08-16 | Austin | TX | Disc | 210 | 4 |
| 13 | 2023-09-02 | Seattle | WA | Orb | 105 | 1 |
| 14 | 2023-09-19 | Phoenix | AZ | Light | 55 | 2 |
| 15 | 2023-10-05 | Portland | OR | Disc | 320 | 5 |
| 16 | ||||||
| 17 | Sightings | 14 | ||||
| 18 | Witnesses | 41 | ||||
| 19 | Mean duration (s) | 141.4 | ||||
| 20 | Longest (s) | 320 |
The sheet computes B17 =COUNTA(B2:B15), B18 =SUM(F2:F15), B19 =ROUND(AVERAGE(E2:E15),1), B20 =MAX(E2:E15)
Which state appears on the most rows of the log? Answer with its two-letter code.
Preview the sheet
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Date | City | State | Shape | Duration (s) | Witnesses |
| 2 | 2023-03-04 | Phoenix | AZ | Triangle | 120 | 3 |
| 3 | 2023-03-11 | Portland | OR | Light | 45 | 1 |
| 4 | 2023-04-02 | Phoenix | AZ | Disc | 300 | 5 |
| 5 | 2023-04-19 | Austin | TX | Light | 60 | 2 |
| 6 | 2023-05-06 | Portland | OR | Triangle | 180 | 4 |
| 7 | 2023-05-23 | Denver | CO | Orb | 90 | 2 |
| 8 | 2023-06-10 | Austin | TX | Light | 30 | 1 |
| 9 | 2023-06-27 | Phoenix | AZ | Orb | 240 | 6 |
| 10 | 2023-07-14 | Denver | CO | Triangle | 150 | 3 |
| 11 | 2023-07-30 | Seattle | WA | Light | 75 | 2 |
| 12 | 2023-08-16 | Austin | TX | Disc | 210 | 4 |
| 13 | 2023-09-02 | Seattle | WA | Orb | 105 | 1 |
| 14 | 2023-09-19 | Phoenix | AZ | Light | 55 | 2 |
| 15 | 2023-10-05 | Portland | OR | Disc | 320 | 5 |
| 16 | ||||||
| 17 | Sightings | 14 | ||||
| 18 | Witnesses | 41 | ||||
| 19 | Mean duration (s) | 141.4 | ||||
| 20 | Longest (s) | 320 |
The sheet computes B17 =COUNTA(B2:B15), B18 =SUM(F2:F15), B19 =ROUND(AVERAGE(E2:E15),1), B20 =MAX(E2:E15)
The longest sighting lasted 320 seconds. Which city reported it?
Preview the sheet
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Date | City | State | Shape | Duration (s) | Witnesses |
| 2 | 2023-03-04 | Phoenix | AZ | Triangle | 120 | 3 |
| 3 | 2023-03-11 | Portland | OR | Light | 45 | 1 |
| 4 | 2023-04-02 | Phoenix | AZ | Disc | 300 | 5 |
| 5 | 2023-04-19 | Austin | TX | Light | 60 | 2 |
| 6 | 2023-05-06 | Portland | OR | Triangle | 180 | 4 |
| 7 | 2023-05-23 | Denver | CO | Orb | 90 | 2 |
| 8 | 2023-06-10 | Austin | TX | Light | 30 | 1 |
| 9 | 2023-06-27 | Phoenix | AZ | Orb | 240 | 6 |
| 10 | 2023-07-14 | Denver | CO | Triangle | 150 | 3 |
| 11 | 2023-07-30 | Seattle | WA | Light | 75 | 2 |
| 12 | 2023-08-16 | Austin | TX | Disc | 210 | 4 |
| 13 | 2023-09-02 | Seattle | WA | Orb | 105 | 1 |
| 14 | 2023-09-19 | Phoenix | AZ | Light | 55 | 2 |
| 15 | 2023-10-05 | Portland | OR | Disc | 320 | 5 |
| 16 | ||||||
| 17 | Sightings | 14 | ||||
| 18 | Witnesses | 41 | ||||
| 19 | Mean duration (s) | 141.4 | ||||
| 20 | Longest (s) | 320 |
The sheet computes B17 =COUNTA(B2:B15), B18 =SUM(F2:F15), B19 =ROUND(AVERAGE(E2:E15),1), B20 =MAX(E2:E15)
How many sightings lasted longer than 150 seconds? Exactly 150 does not count.
Preview the sheet
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Date | City | State | Shape | Duration (s) | Witnesses |
| 2 | 2023-03-04 | Phoenix | AZ | Triangle | 120 | 3 |
| 3 | 2023-03-11 | Portland | OR | Light | 45 | 1 |
| 4 | 2023-04-02 | Phoenix | AZ | Disc | 300 | 5 |
| 5 | 2023-04-19 | Austin | TX | Light | 60 | 2 |
| 6 | 2023-05-06 | Portland | OR | Triangle | 180 | 4 |
| 7 | 2023-05-23 | Denver | CO | Orb | 90 | 2 |
| 8 | 2023-06-10 | Austin | TX | Light | 30 | 1 |
| 9 | 2023-06-27 | Phoenix | AZ | Orb | 240 | 6 |
| 10 | 2023-07-14 | Denver | CO | Triangle | 150 | 3 |
| 11 | 2023-07-30 | Seattle | WA | Light | 75 | 2 |
| 12 | 2023-08-16 | Austin | TX | Disc | 210 | 4 |
| 13 | 2023-09-02 | Seattle | WA | Orb | 105 | 1 |
| 14 | 2023-09-19 | Phoenix | AZ | Light | 55 | 2 |
| 15 | 2023-10-05 | Portland | OR | Disc | 320 | 5 |
| 16 | ||||||
| 17 | Sightings | 14 | ||||
| 18 | Witnesses | 41 | ||||
| 19 | Mean duration (s) | 141.4 | ||||
| 20 | Longest (s) | 320 |
The sheet computes B17 =COUNTA(B2:B15), B18 =SUM(F2:F15), B19 =ROUND(AVERAGE(E2:E15),1), B20 =MAX(E2:E15)
How many witnesses in total reported the triangle shape?
Preview the sheet
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Date | City | State | Shape | Duration (s) | Witnesses |
| 2 | 2023-03-04 | Phoenix | AZ | Triangle | 120 | 3 |
| 3 | 2023-03-11 | Portland | OR | Light | 45 | 1 |
| 4 | 2023-04-02 | Phoenix | AZ | Disc | 300 | 5 |
| 5 | 2023-04-19 | Austin | TX | Light | 60 | 2 |
| 6 | 2023-05-06 | Portland | OR | Triangle | 180 | 4 |
| 7 | 2023-05-23 | Denver | CO | Orb | 90 | 2 |
| 8 | 2023-06-10 | Austin | TX | Light | 30 | 1 |
| 9 | 2023-06-27 | Phoenix | AZ | Orb | 240 | 6 |
| 10 | 2023-07-14 | Denver | CO | Triangle | 150 | 3 |
| 11 | 2023-07-30 | Seattle | WA | Light | 75 | 2 |
| 12 | 2023-08-16 | Austin | TX | Disc | 210 | 4 |
| 13 | 2023-09-02 | Seattle | WA | Orb | 105 | 1 |
| 14 | 2023-09-19 | Phoenix | AZ | Light | 55 | 2 |
| 15 | 2023-10-05 | Portland | OR | Disc | 320 | 5 |
| 16 | ||||||
| 17 | Sightings | 14 | ||||
| 18 | Witnesses | 41 | ||||
| 19 | Mean duration (s) | 141.4 | ||||
| 20 | Longest (s) | 320 |
The sheet computes B17 =COUNTA(B2:B15), B18 =SUM(F2:F15), B19 =ROUND(AVERAGE(E2:E15),1), B20 =MAX(E2:E15)
How many witnesses in total were in Arizona?
Preview the sheet
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Date | City | State | Shape | Duration (s) | Witnesses |
| 2 | 2023-03-04 | Phoenix | AZ | Triangle | 120 | 3 |
| 3 | 2023-03-11 | Portland | OR | Light | 45 | 1 |
| 4 | 2023-04-02 | Phoenix | AZ | Disc | 300 | 5 |
| 5 | 2023-04-19 | Austin | TX | Light | 60 | 2 |
| 6 | 2023-05-06 | Portland | OR | Triangle | 180 | 4 |
| 7 | 2023-05-23 | Denver | CO | Orb | 90 | 2 |
| 8 | 2023-06-10 | Austin | TX | Light | 30 | 1 |
| 9 | 2023-06-27 | Phoenix | AZ | Orb | 240 | 6 |
| 10 | 2023-07-14 | Denver | CO | Triangle | 150 | 3 |
| 11 | 2023-07-30 | Seattle | WA | Light | 75 | 2 |
| 12 | 2023-08-16 | Austin | TX | Disc | 210 | 4 |
| 13 | 2023-09-02 | Seattle | WA | Orb | 105 | 1 |
| 14 | 2023-09-19 | Phoenix | AZ | Light | 55 | 2 |
| 15 | 2023-10-05 | Portland | OR | Disc | 320 | 5 |
| 16 | ||||||
| 17 | Sightings | 14 | ||||
| 18 | Witnesses | 41 | ||||
| 19 | Mean duration (s) | 141.4 | ||||
| 20 | Longest (s) | 320 |
The sheet computes B17 =COUNTA(B2:B15), B18 =SUM(F2:F15), B19 =ROUND(AVERAGE(E2:E15),1), B20 =MAX(E2:E15)
Reference answer
What it does
The summary block is four formulas and nothing else. COUNTA counts the city cells that are not empty, so nobody has to count rows by hand. SUM adds every witness across the fourteen rows.
AVERAGE divides that duration column by the number of values in it, and ROUND pins the result to one decimal so the report does not print a repeating decimal. MAX finds the single longest sighting. The eight tasks work on the rows behind those numbers, and most are a sort away.
Syntax
=COUNTA(B2:B15), =SUM(F2:F15), =ROUND(AVERAGE(E2:E15),1)Worked example
On ufo.xlsx · Sightings, =ROUND(AVERAGE(E2:E15),1) returns .
=ROUND(AVERAGE(E2:E15),1)Common errors
| Error | What it means and how to fix it |
|---|---|
A zero instead of a blank | AVERAGE ignores an empty cell but counts a zero, so a missing duration typed as 0 pulls the mean down. Leave it blank. |
The summary in the range | The data stops at row 15 and the summary starts at 17. A range like E2:E20 pulls the summary numbers into the average as well. |
At work
Every sightings, complaints or orders sheet that arrives on a Monday morning is this file: raw rows and a block of totals someone will read first. The person who can add the totals, and then say what sits behind the largest one, is the one who gets asked next.