CSE200 MIDTERM
Autumn 2007 KReeves
MW 1:30-3:18pm
Name
________________________________________
Seat #____________
Circle
Lab Day/Time: Wed 3:30pm Thurs 1:30pm
Instructions:
·
Up to a maximum
of -3 deduction for missing any name, seat# or lab
information on the exam or answer
sheet.
·
Do not open your
exam until it is time to begin.
·
Put away all
books, papers, and calculators. Hats on
backwards!
·
Write your name,
lab day/time and seat # on the first page (above) AND on the Answer Sheet. ALL sheets must be turned in when handing in
the exam or your exam will not be graded.
·
Read each
question carefully and fill in the answer on the answer sheet. Answers must be legible or they will be
marked incorrect.
·
You should only have
one answer for each problem on your answer sheet. If you have more, be sure to circle the
correct answer or it will not be graded!
·
You CANNOT use
data that is not input data unless it has been solved in a previous problem.
·
Please make sure
you have all pages before you start this exam.
·
If you have
already determined the answer to a previous problem that can be used to solve a
current problem, you must use the already solved problem in your answer.
·
All answers given
should be “updatable” unless otherwise noted.
·
Do not use extra
IF structures. An example of using an
extra IF structure is IF(cond,true,false)
when just the condition would be the correct answer.
·
Do not use an answer
that is significantly more complex when an easier solution is available. There
is some leeway here, as we’ve talked about in class. An example of too complex
is more than two IF structures nested when a reference function can be used.
·
Do not use
functions not covered in class.
·
Use cell
references and named ranges whenever possible.
·
Only use quotes
when necessary – do NOT use quotes around Boolean values.
·
Don’t use a $ if
NOT copying.
DESCRIPTION
Playing paintball is pretty simple. It is a mixture of tag and capture the flag, but instead of physically tagging your opponent out, you use "guns" that shoot paint pellets that mark the target with an ooze of paint. The typical game is played with equal amounts of players on two teams.
The field make up can be any size in any type of environment. Bunkers can be tires or mounds of dirt to actual buildings. Each of the teams will proceed to their home base stations where they will try to defend their flag while capturing their opponent’s flag. At the sound of the whistle, the game starts and whatever time limit, starts to count down.
As a paintball fanatic, you want to eventually own your own paintball game location. You are collecting data and determining factors important to paintball teams. Starting with a small amount of data, you want to set up some worksheets to keep track of different information as shown on the given worksheets. The input or given data is colored in gray. The original cells for the problem solutions are also shaded slightly. The following are further descriptions of the worksheets you will use to set up your data and other calculations:
PLAYERS – This worksheet contains the given input data by player. Each player is on a certain team (column B) and has a level of experience (column C) from 1-5 where 1 means beginner and 5 means close to the professional level.
TEAMS –The input data in this worksheet (A4:D7) specifies the name of each team in the tournament, the team number, the number of points earned during the tournament and the place earned (to be explained in the one of the problems given below). The team numbers that are given are consecutive and start with 1. The rest of the data is created by the solving of the problems given below.
FIELDS – There are different fields in a paintball park where different games can be played. The input information is given in gray (columns A-F). The size of the field (length and width) is given in feet, whether the game is played indoors or not (TRUE if the game is played indoors), the time limit for each game in minutes, as well as whether or not a reduction in speed is necessary for each particular game due to playing close to each other (and not wanting anyone to get hurt).
UNITS – This worksheet has conversion data on it. Cells B1:B8 are considered input data; cells B9 and B10 will be filled in by a problem given below.
SOD – This worksheet
shows the cost per square yard of sod depending on the area in square yards to
be bought for a particular paintball field. There is a named range called sodcost which must be used
instead of the range SOD!A2:B8.
WIN – This worksheet
defines the monetary winnings for both professional teams as well as amateur
teams. The top 5 teams will win the amount of money designated for 1st
through 5th place. If they do not place in the top 5, then they
don’t win any money.
.
EXCEL PROBLEMS
1.
(12 pts)
You want to determine the number of teams entered in the tournament. You have
come up with the following possible Excel formulas. Determine, YES/NO, which
one(s), if any, will work correctly. NOTE: The answer for the given data is 4.
=COUNT(teams!A4:A7) yes __________ no __________
=MAX(players!B3:B14) yes __________ no __________
= LARGE(players!B3:B14,1) yes __________ no __________
=SMALL(players!B3:B14,4) yes __________ no __________
2.
(10 pts)
Write an Excel formula in cell TEAMS!E4, which can be
copied down to TEAMS!E7, to determine the number of players on the Dragon team
i.e. team#2.
3.
(22 pts)
Write an Excel formula in cell TEAMS!F4, which can be
copied down to TEAMS!F7, to determine the average experience level for the
Dragon team i.e. team#2 rounded to the nearest tenth.
4.
(16 pts)
Write an Excel formula in cell TEAMS!G4, which can be copied down to TEAMS!G7,
to determine if the Dragon team i.e. team#2 is considered to have the
experience of an amateur team or a professional team. A team is considered to
be amateur, or “ama” for the answer, if the average
level of experience is less than 3; otherwise, the team is considered to be
professional, or “pro” for the answer.
5.
(10 pts)
Why does G6 say “ama” and G7 says “pro” but the
values in F6 and F7 are the same?
6.
(15 pts)
Write an Excel formula (answer not shown) to determine if only professional
teams scored over 600 points. NOTE: The data in column G as well as the number
of teams cannot change for this question only. However, the values in the other
columns can change and the answer should still work. FYI: The answer for the given data is TRUE.
7.
(13 pts)
Write an Excel formula in cell TEAMS!H4, which can be
copied down and across to TEAMS!J7, to determine how much money
was spent on uniforms for the Dragon team i.e. team#2. The cost given in H2
(and subsequently I2 and J2) is the cost of the uniform PER PERSON.
8.
(27 pts)
Write an Excel formula in cell TEAMS!K4, which can be
copied down to TEAMS!K7, to determine the winnings, if any, for the Dragon team
i.e. team#2. The amount of money won is determined by the place earned by the
Dragon team, which was based on total points, and is given in TEAMS!D4. REMINDER: The tournament has dozens of teams, but all
the data is not shown here. Your answer should work no matter how many teams
have entered the tournament. NOTE: See the description of the WIN worksheet for
more information.
9.
(10 pts)
Given the chart below, determine the following:
a.
What
type of chart is shown? ______________________
b.
What is
the range used for the data shown?
________________________
10.
(13 pts)
Write an Excel formula in cell FIELDS!G2, which can be
copied down to FIELDS!G6, to determine if speedball is a good game. A game is
good when either the time is less than 20 minutes or the game is indoors with
speed reduction.
11.
(13 pts)
Write an Excel formula in cell FIELDS!H2, which can be
copied down to FIELDS!H6, to determine the area of the speedball field in
square yards.
12.
(13 pts)
Write an Excel formula in cell FIELDS!H7 to determine
the total number of acres for all the
given fields on the list.
13.
(17 pts)
Write an Excel formula in cell FIELDS!I2, which can be
copied down to FIELDS!I6, to determine the cost of sod for the speedball field.
14.
(8 pts)
You have only enough land for a total of 15 acres of fields
space to play games on so need to reduce each field by a certain amount to get
H7 to be 15 or less. Is this a What-if analysis or a
goal seek type of problem?
15.
(16 pts)
Write an Excel formula (not shown) to determine the monthly payment for the
start-up costs of opening your own paintball park where the start-up costs are
$450,000 with 10% annual percent rate of interest compounded quarterly over 5
years. One nice thing is that you have a CD that will be worth $5,000 at the
end of the 5 years so the $5,000 can be used as a balloon payment to help lower
the monthly payments.
16.
(16 pts)
Instead of borrowing the money to open your own paintball park, you are going
to rent a place, and invest the income toward a place of your own. Write an
Excel formula (not shown) to determine
how long it will take, in years, to save the necessary start-up costs
($450,000) if the rental costs (including any and all outgoing costs) are
$2,000 per month, the monthly income is $15,000 (which means you will be
investing $13,000 per month) and the annual percentage interest rate is 12%
compounded monthly.
17.
(16 pts)
So, how fast does a paintball really go? Yes, it stings when it hits you ;o) A
safe velocity is given in UNITS!B2 with a reduction
velocity (used in games that have close quarters) given in UNITS!B3. Velocity
is given in feet/second. Write an Excel formula in cell UNITS!B9,
which can be copied down to UNITS!B10, to determine the safe velocity that a
paintball travels in miles/hour.
Name: ____KEY_____________________ Lab Day/Time ____________ Seat# ________
|
=COUNT(teams!A4:A7) yes __________ no ____X_____ =MAX(players!B3:B14) yes ____X_____ no __________ = LARGE(players!B3:B14,1) yes ____X_____ no __________ =SMALL(players!B3:B14,4) yes __________ no ____X_____ |
|||
|
=COUNTIF(players!B$3:B$14,B4) Optional $ on column |
|||
|
Optional $ on
column |
|||
|
Optional $ on
column |
|||
|
a.
What
type of chart is shown? __bar or column____________________ b.
What
is the range used for the data shown? __ =fields!$A$1:$C$6__ |
Points: ____ /250