The Big Treasure Hunt

By Betty Clark,2014-09-03 12:47
9 views 0
The Big Treasure HuntThe

    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 (

    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.

    Basic Tasks

    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

    been disqualified

    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.

Further Tasks

    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

    overall total.

    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

    Written Task

    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.

Other Variations

    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