CSE200 MIDTERM

Autumn 2007 KReeves MW1:30-3:18pm

Name ______Seat #______

Circle Lab Day/Time: Wed3:30pmThurs1: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 databy 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 sodcostwhich 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 ______

  1. (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.
  1. (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#2rounded to the nearest tenth.
  1. (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.
  1. (10 pts) Why does G6 say “ama” and G7 says “pro” but the values in F6 and F7 are the same?
  1. (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.
  1. (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.
  1. (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 placeearned 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.
  1. (10 pts) Given the chart below, determine the following:
  1. What type of chart is shown? ______
  1. What is the range used for the data shown? ______

  1. (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.
  1. (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.
  1. (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.
  1. (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.
  1. (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?
  1. (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.
  1. (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.
  1. (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# ______

Answer Sheet CSE 200 Midterm AU07 MW1:30-3:18pm Lecture Class

(-) / Pts / # / Answer:
12 / =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_____
10 / =COUNTIF(players!B$3:B$14,B4)
Optional $ on column
22 / =ROUND(SUMIF(players!B$3:B$14,B4,players!C$3:C$14)/E4,1)
Optional $ on column
16 / =IF(F4>=3,"pro","ama")
=IF(F4<3,"ama","pro")
Optional $ on column
10 / Because the actual/real/precision value in F6 is less than 3 and the actual/real/precision value in F7 is greater than or equal to 3.
15 / =AND(OR(C5>600,C7>600),NOT(OR(C4>600,C6>600)))
Can have AND(C4<=600,C4<=600) instead of NOT/OR
No $ allowed
13 / =H$2*$E4 no extra $ allowed
27 / =IF(D4>5,0,HLOOKUP(D4,win!B$1:F$3,IF(G4="ama",3,2)))
=IF(D4>5,0,IF(G4="ama",HLOOKUP(D4,win!B$1:F$3,3),HLOOKUP(D4,win!B$1:F$3,2)))
Optional $ on column
if use “pro” instead of “ama”, then switch to have 2 as row-index first
Okay if used win!F$1 instead of 5
4th argument of ref func can be TRUE or FALSE
No quotes allowed around Boolean values
10 /
  1. What type of chart is shown? __bar or column______
  1. What is the range used for the data shown? __=fields!$A$1:$C$6__

(-) / Pts / # / Answer:
13 / 10 / =OR(AND(D2,F2),E2<20)
Optional $ on column
13 / 11 / =B2*C2/units!B$7/units!B$7
Optional $ on column
13 / 12 / =SUM(H2:H6)/units!B8
No $ allowed
17 / 13 / =H2*VLOOKUP(H2,sodcost,2)
Must use named range
TRUE default 4th argument – no quotes
Optional $ on column
No worksheet name allowed on named range
8 / 14 / What-if
16 / 15 / =PMT(10%/4,5*4,450000,-5000)/3
16 / 16 / =NPER(12%/12,-13000,0,450000)/12
16 / 17 / =B2/B$4*B$5*B$6
Optional $ on column

Points: ____ /250

KReeves Autumn 2007 MidtermPage 1