1. The Big Treasure Hunt
A tramping club runs an annual fun team event called The Big Treasure Hunt. In this
project you will calculate the times and scores for teams in this event. You will also
determine the winning team, individual’s scores and times and various other statistics.
Teams of 2 or more people compete in The Big Treasure Hunt. The event has three "legs"
and each leg has a different route. Each leg also has up to twelve separate "treasures" that can be found. These are firmly secured cards on which a message has been printed. For each leg the teams are given a list of clues, one for each treasure. When they locate the treasure they write its message beside its clue on their list. Each correct message subsequently earns one point. The winning team (or teams) is the one that scores the most points. Time taken is not usually an issue, although teams like to know how long they have taken, but see below. Rules
The Big Treasure Hunt has the following rules:
1. Each team must have a minimum of two and a maximum of 12 members.
2. The official start time is 10 am.
3. Not all team members have to participate on all three legs but the leader and at least one
other member of each team must complete all legs. (i.e. At least two people from each
team (the captain and one other) must do the entire race. Any other people in the team
can do as many legs as they like.)
4. Each team must take at least a half-hour "food break" in the Hash House between legs. 5. All members who start a leg must finish the leg. i.e. The team doesn't "finish" the leg
until all those who started it have returned.
6. The last leg must be completed within 12 hours of the official start time. 7. A one mark per five minute (or part five minute) penalty applies if a team does not finish
by the official finish time. (e.g. Finishing 45 seconds late will attract a penalty of 1
point and 7? minutes late a penalty of 2 points.)
Failure to abide by any one of the rules above results in disqualification of the team.
(Note: This exercise has to some extent been modelled on a 24 hour orienteering style team event called Twalk run annually by the University of Canterbury, New Zealand Tramping Club (http://www.cutc.org.nz).
In this project you will use the information above and the data provided below to set up an Excel spreadsheet.
The Completed Spreadsheet
At minimum this will have the following named sheets:
; A Start sheet which will also describe the structure/layout of your workbook. ; A Teams sheet which will have the columns containing data and calculations about the
; A People sheet containing the details of the people in the teams and their results. ; A chart sheet called Chart of Results displaying the teams' scores and times.
It may also have one or more macro modules and other sheets for:
; Summaries including a frequency distribution of times and other statistics. ; One or more sheets for pivot tables.
; Income from the event.
; A scenario summary sheet containing various income scenarios. Data Sources
You will be supplied with the two spreadsheets People.xls and Results.xls.
The details of the people who competed in the event have been entered into People.xls.
This spreadsheet also has the details of who has completed which leg. The Results.xls
contains the data for each team, including each team leader’s Race No., in the treasure hunt. It gives the end times for the first leg and the start and end times for the other two legs. It
also has each team's points for each leg.
A. Using whatever technique you like merge the single sheets from each workbook into a
new workbook called TreasureHunt.xls. Make sure you also rename the Results
sheet as Teams.
B. On the Teams sheet set up columns for, and calculate, the following: (note: you may
also include other columns to assist calculations if you want) :
Food Break 1 Duration of the first "food break". (Hint: if your answers look
strange check the formatting.)
Food Break 2 Duration of the second "food break".
Total Racing Time Total time (formatted to show hrs, mins and secs) that the team
spent on the course on the actual legs. (i.e. excluding the food
breaks). (Assume they started the first leg at 10 am.)
Total Points The accumulated points from each leg.
Time Penalties Any points to be deducted for finishing too late. (Hint: check
out the time and rounding functions.)
Corrected Total Points Final points after time penalties have been deducted.
More columns will be added to this sheet in Task D.
C. On the People sheet set up a column to calculate whether or not each person has
completed all three legs. (Hint: it is best to calculate the result as 1 (for yes) and 0 (for
no) because this means it will be easier to compute the number of people in each team
who have completed all 3 legs in Task D.)
D. On the Teams sheet set up columns to calculate the following:
Completed all Legs The number of people in the team who have completed all 3
legs. (Hint: The SUMIF function could be very useful
Leader all Legs Indicator as to whether or not the leader of the team has
completed all legs. (Hint: It is easiest to use a VLOOKUP
where the People sheet is used as a lookup table with Race No
as the matched column.)
Fail (all legs) An indicator as to whether a team has been disqualified - if the
conditions in rule 3 are not met, the team is disqualified.
Fail (breaks) An indicator as to whether a team has been disqualified for
having had a break of less than half an hour.
Rank Final ranking of the team (provided it hasn’t been disqualified).
(Hints: look at the on-line help for the RANK function. You
might want to insert an extra column (Points for Ranking) that
only displays corrected total points for the teams who haven’t
Please include named cells as follows:
FirstTotalTime The cell containing the total racing time for team no 1.
FirstPenalties The cell containing the time penalties for team no 1.
FirstAllLegs The cell containing the number of people who have completed
all legs for team no 1.
FirstRank The cell containing the ranking for team no 1.
E. On a single chart, (use column type), show the Total Racing Times and Corrected Total
Points for all teams including the disqualified ones. Your graph will need a secondary
axis with the scales and gap widths adjusted to make both series clearly visible. Make
sure the location is a chart sheet called Chart of Results.
F. Set up the Start sheet so that it provides useful documentation of your project with sheet,
chart and range names and descriptions.
Add the following features to your workbook. Make sure you also extend the Start sheet to
include details of these additions.
G. On a new sheet called Summary set up a bin range, named bins, with about 10 steps,
suitable for generating a frequency distribution of the Corrected Total Points. Use
this range to create a chart (histogram) on the same sheet.
H. Insert a table called stats on the Summary sheet containing the maximum, median,
minimum and modal values of the Corrected Total Points.
I. The Big Treasure Hunt is intended to be a fundraiser as well as a fun event. There is
some sponsorship (which we will ignore) and each team is charged a $20 fee. In
addition the individuals within each team pay $8 each if they are a club member and $12
if they are not.
J. On an Income sheet set up a table to calculate the income from participants. As well as
named cells (TeamFee, MemberFee, NonMemberFee) for the various fees, include
cells with formulas for the number of teams (NoTeams), number of club members
(NoMemb) and number of non-members (NoNonMemb). Include formulas to
calculate the income from each category (team, member and non-member) as well as the
K. Set up 3 or 4 scenarios for different combinations of the three fees (e.g. Include a
scenario where there is no team fee and members pay $10 and non-members $15.)
Generate a Scenario Summary to illustrate the income that would be generated from the
various scenarios. You must show how the income from each category and the overall
total income changes.
L. On a Pivot Tables sheet create pivot tables (and optionally pivot charts) to show:
i. The number of participants for each combination of Gender and Club Membership.
Page this table by Team.
ii. The total number of people in each team and how many completed all the legs and
how many are club members.
M. Macro tasks
The data in People.xls and Results.xls were carefully checked to make sure they did not
contain errors. In practice, this might well not happen especially if the data is entered on a laptop, in poor lighting, in the Hash House during the event. Write a report, about one page long, describe some of the errors that might occur and how easy it would be to anticipate and correct them.
Recycling this Project
This project could be reused without needing any changes to the data as it uses only time and not date data.
An earlier version of the same project used a simpler version of rule 3 above:
3. Not all team members have to participate on all three legs but two members of each team
must complete all legs. (i.e. At least two people from each team must do the entire