CSE200 MIDTERM
Autumn 2006 KReeves TR 1:30-3:18pm
Name
_________________________________________________ Seat #____________
Instructions:
· -1 points 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. For example IF(cond,true,false) when the condition would be the correct answer.
· Do not use a complex answer 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.
DESCRIPTION
The input data is colored in gray. The original cells for the solutions are also shaded.
TDK – This worksheet contains the input data about each person taking tae kwon do lessons; their name, age, current belt color and last test date are given in columns A thru D. The remaining data will be filled in as the below problems are solved.
BELTS – This worksheet specifies the color of the different belts that can be earned, the gup level specified by that particular belt and the number of weeks it will take to earn that belt. The gup level is defined to be the number of levels away from a black belt minus 1 since the black belt is level 1.
UNITS - The units worksheet has conversion and constant data, all of which are considered input data. To note are the following two pieces of data. One is the number of belts as defined by the tae kwon do master which is given in cell B1. However, this cell has a named range associated with it called numbelts; be sure to use this name range whenever you reference units!b1. Cell units!b2 is the level or gup at which a student is allowed to start weapons training as defined by the master.
TEST – This worksheet defines the test dates for the year. Each test date is given a test number which is defined/given in row 2.
1. (8 pts) Write an Excel formula in cell
belts!B2 to determine the maximum number of belts as defined by the tae kwon do
master (see unit worksheet description).
=numbelts
2. (8 pts) Write an Excel formula in cell
belts!B3, which can be copied down to belts!B11, to determine the gup level for
a yellow belt (see belts worksheet description).
=B2-1
Optional $ on column
3. (25 pts) Write an Excel formula in cell tkd!E4,
which can be copied down and across
to cell tkd!F9, to determine the gup level (and automatically the associated number
of weeks when copied) for Tom. Remember, the gup level is based on the belt
color. You will need to use the values in cells E2 and F2 to help determine
which column_index to choose when copying.
=VLOOKUP($C4,belts!$A$2:$C$11,E$2+1,FALSE)
No extra $ allowed
4. (15 pts) Write an Excel formula in cell
tkd!G4, which can be copied down to cell tkd!G9, to determine the date that Tom
can test again for his next belt. NOTE: you already know the last test date as
well as the number of weeks (determined in problem#3) that you have to wait to
test again.
=D4+F4*units!B$6
Optional $ on column
5.
(30 pts)
Write an Excel formula in cell tkd!H4, which can be copied down to cell tkd!H9,
to determine the test number for Tom. Since the date that Tom can test again
(determined in problem#4) may not necessarily match the date of a test being
given, it is appropriate to determine the test number instead of the test date.
That is, since the lookup value (hint hint) will be the date to test again, I
don’t want you returning the value that you are looking up ;o) you can, but I
don’t want you to do that here; instead, I want the test number associated with
the test date.
Was =IF(HLOOKUP(G4,test!B$1:M$2,2,TRUE)=12,1,HLOOKUP(G4,test!B$1:M$2,2,TRUE)+1)
Now =HLOOKUP(G4,test!B$1:M$2,2,TRUE)+1
True is optional
Optional$ on column
6.
(12 pts)
Write an Excel formula in cell test!B3, which can be copied across to cell
test!M3, to determine the number of students taking test number one.
=COUNTIF(tkd!$H4:$H9,test!B2)
Optional $ on row
7.
(10 pts)
In cell test!B4, I want to determine the percent of people taking test#1 and
copy it across to test!M4. I tried the formula =B4/COUNT(tkd!A$4:A$9). Does it
work, yes or no? If it doesn’t work, explain why and give the corrected formula. Assume cell test!B4 is
formatted to be a percent.
=B3/COUNT(tkd!$B4:$B9)
No, doesn’t work – okay if implied
Need to use numeric range for count
function – check that corrected in given formula
$ need to be on column not row –
check that corrected in given formula
8.
(10 pts)
Now that the above formula is correct, what type of chart would best be used to
represent this data? Pie
9.
(18 pts)
Write an Excel formula in cell tkd!I4, which can be copied down to cell tkd!I9,
to determine what age group Tom is in given the following:
·
The
student is a “child” if they are less than 13 years old
·
The
student is an “adult” if they are 18 years or older
·
Otherwise,
the student is a “teen”
=IF(B4>=18,"adult",IF(B4>=13,"teen","child"))
=IF(B4>=18,"adult",IF(B4<13,"child”,"teen"))
=IF(B4<13,"child",IF(B4<18,"teen","adult"))
=IF(B4<13,"child",IF(B4>=18,"adult","teen"))
=IF(and(B4>=13,b4<18),”teen”,IF(B4<14,"child","adult"))
=IF(and(B4>=13,b4<18),”teen”,IF(B4>=18,”adult”,"child"))
Optional $ on column
10.
(12 pts)
Write an Excel formula in cell tkd!J4, which can be copied down to cell tkd!J9,
to determine, true or false, if Tom is allowed to participate in weapons training.
Weapons training is allowed when you are an adult or, if you are not an adult,
you have to be at the appropriate level as defined by the master (see the units
worksheet) and at least 10 years old.
=OR(I4="adult",AND(E4<=units!$B$2,B4>=10))
Optional $ on column
11.
(15 pts)
Write an Excel formula in cell tkd!G11 to determine, true or false, if only
adults, but not necessarily all adults, are allowed weapons.
=AND(OR(J7,J8),NOT(OR(J4,J5,J6,J9)))
=AND(OR(J7,J8),NOT(J4),NOT(J5),NOT(J6),NOT(J9))
=AND(OR(J7=true,J8=true),J4=false,J5=false,J6=false,J9=false)
No $ allowed
12.
(15 pts)
Write an Excel formula in cell tkd!G12 to determine the shortest possible time
frame to earning a black belt to the nearest year. Assume you will start as a
white belt and continue on schedule, testing as soon as possible for each
level, until you earn the black belt.
=ROUND(SUM(belts!C2:C11)/units!B5,0)
No $ allowed
13.
(8 pts)
The tae kwon do master is thinking that waiting 48 weeks (belts!C11) to go from
gup2 to gup 1 is just too long, so wants to lower that number of weeks, but
wants to keep the current time frame to earn a black belt the same (i.e.
approximately 3 years as determined in problem #12). This means changing some
of the other values for length of time to the next gup level (belts worksheet
column C). Is this a goal seek or what if analysis problem? What-if
14.
(8 pts)
If I use the format button to increase the decimal points on cell tkd!G12, what
value will be displayed when the icon is used to increase the value to two
decimal points?
3.00
15.
(8 pts)
Write an Excel formula in cell tkd!G13 to determine the gup level of the most
inexperienced person currently taking tae kwon do lessons.
=MAX(E4:E9) no $ allowed
16.
(10 pts)
I want to know the average age of the children taking tae kwon do lessons. I
entered the following formula. What is the result of the formula? Is it correct
(yes or no)? NOTE: You do not have to tell me how to correct it or what’s wrong
with it.
·
=AVERAGE(SUMIF(I4:I9,"child",B4:B9)) è 17, no -5 for each
17.
(16 pts)
The tae kwon do master took out a 5-year loan at 6% interest compounded quarterly for $105,000 to start
up his business. Write an Excel formula in cell tkd!G14 to determine the monthly payment necessary in
order to pay back the entire loan in that 5 year period.
=PMT(6%/4,5*4,105000)/3
no $ allowed
18.
(16 pts)
The tae kwon do master has an income of $2000 per month. Checking the solution
for the above problem, the master does not have enough money to pay off this
loan in 5 years. Write an Excel formula in cell tkd!G15 to determine how much
the master still owe at the end of the 5 year period if the payment is only
$2000 per month. DOESN’T SAY COMPOUND RATE.
If
assume quarterly compound rate =FV(6%/4,5*4,-2000*3,105000)
If
assume monthly compound rate =FV(6%/12,5*12,-2000,105000)
no $ allowed
Name: ______________________________ Lab Day/Time ____________ Seat# ________
Points: ____ /250