CSE200 MIDTERM
Spring 2007 KReeves TR 9:30-11:18am
Name
________________________________________
Seat #____________
Instructions:
·
Up
to a maximum of -2 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
A triathlon is a competition involving the combination of 3 different events: swimming, biking and running. The details of each person involved in the triathlon are designated on the following worksheets. The input or given data is colored in gray. The original cells for the solutions are also shaded slightly. The following are further descriptions of the worksheets you will use for the given problems:
INPUT – This worksheet contains the input data (A4:E9) about each person competing in the triathlon. Remember that this is only a sample of the data.
HOWFAR –A named range has been defined for howfar!B1:D2 called far. The data in this worksheet specifies the distance in miles for each event; 16 miles for the bike portion of the triathlon, 1 mile for the swim portion of the triathlon and 3 miles for the run portion of the triathlon.
UNITS - The units worksheet has conversion and constant data to be used in solving some of the given problems. B1:B4 are considered input data.
LEVEL – This worksheet defines the different level descriptions based on the time it takes a person to finish the triathlon. Each value in column A represents a range of values. For instance, if a person takes at least 0.8 hours to finish the race, but less than 1.2 hours, then they are considered to be at the “master” level. If a person takes at least 1.2 hours to finish the race, but less than 1.4 hours, then they are considered to be at the “natural” level, etc.
EXCEL PROBLEMS
1.
(8 pts) Determine
the result of the following formula: =COUNT(B4:B9)=COUNT(C4:C9)
FALSE
The count column B function will return a zero which is not equal to
the count column C function which returns a 6… the relational expression
returns a boolean value – no quotes!
2.
(12 pts)
What type of chart is given on the worksheet page? What range(s) is used to
plot the values?
Bar chart
=input!$A$3:$A$9,input!$C$3:$E$9
$ signs not necessary
3.
(14pts)
Write an Excel formula in cell input!C10, which can be copied across to E10, to
determine the average time in minutes, rounded to the nearest minute, for all
the people who swim.
=ROUND(AVERAGE(C4:C9),0)
Optional $ on row
4.
(18 pts)
Write an Excel formula in cell input!A11 to determine the event (i.e. swim,
bike or run) that has the highest average time in minutes.
=IF(C10=MAX(C10:E10),C3,IF(D10=MAX(C10:E10),D3,E3)) Other
condition is E10=MAX(C10:E10)
5 other options for the ordering of this problem; just make sure the
correct cell reference average matches the description of the event i.e. if
checking C10 max then C3 is the related answer, if checking D10 max then D3 is
the related answer and if checking E10 max then E3 is the related answer
No $ allowed
5.
(14 pts)
Write an Excel formula in cell input!F4, which can be copied down to F9, to
determine if all of Abby’s event times are below the average of each event
time.
=AND(C4<C$10,D4<D$10,E4<E$10)
Optional $ on column
6.
(14 pts)
Write an Excel formula in cell input!G4, which can be copied down to G9, to
determine the total time in hours it takes for Abby to finish the triathlon.
=SUM(C4:E4)/units!B$4
Okay if put (C4+D4+E4) instead of SUM but must have parentheses
Optional $ on column
7.
(18 pts)
Write an Excel formula in cell input!G12, which can be copied down to G13, to
determine the average total time in hours for the male competitors. NOTE:
copying down allows the original formula to determine the average total time in
hours for the female competitors.
=SUMIF(B$4:B$9,F12,G$4:G$9)/COUNTIF(B$4:B$9,F12)
Optional $ on column
8.
(14 pts)
Write an Excel formula in cell input!H4, which can be copied down to H9, to
determine what place Abby received in the triathlon for her total time considering
that the fastest competitor got 1st place, the second fastest
competitor got 2nd place, etc.
=RANK(G4,G$4:G$9,1)
Optional $ on column
9.
(8 pts)
Write an Excel formula in cell input!I4, which can be copied down to I9, to
determine if Abby will be able to compete in the next triathlon since only the
top 4 ranked competitors can go.
=H4<=4
Optional $ on column
10.
(14 pts)
Write an Excel formula in cell input!I14 to determine if only males competitors
are able to compete in the next triathlon and none of the females can compete
in the next triathlon. NOTE: this is the only problem on the exam that can be
answered assuming the data will not change.
=AND(OR(I5,I7,I8,I9),NOT(OR(I4,I6)))
=AND(OR(I5,I7,I8,I9),NOT(I4),NOT(I6))
Can put =TRUE for I4,I7,I8, I9 and =FALSE for I4, I6 (first solution)
Can put =TRUE for I4,I7,I8, I9 and =TRUE for I4, I6 (second solution)
No $ allowed
11.
(14 pts)
Write an Excel formula in cell input!J4, which can be copied down to J9, to
determine Abby’s level (see the level worksheet).
=VLOOKUP(G4,level!A$2:B$6,2)
Optional TRUE for 4th argument
Optional $ on column
12.
(8 pts) What
value would be returned for the above problem if the total time in hours is
less than 0.8?
An excel error (#NA to be exact)
Cannot find the “greatest value that does NOT exceed the criteria”
13.
(10 pts)
Why is the value in input!J7 “basic” instead of “having fun”? Same thing
happens in input!J5 where the answer is “master” instead of “natural”. The
answer is listed correctly on the worksheet. Why does this not look like the
correct answer?
Because the PRECISION or ACTUAL
value is less than the formatted value; so even if the number in I7 is 1.8,
it’s actual value is less than that which is why “basic” is the result of the
solution.
14.
(10 pts)
Evan wants to be a “master” which means his total time needs to be at least 0.8
but less than 1.2. Is this a goal seek or a what-if situation? Describe what
changes would be made to the worksheet to help Evan determine how he can become
a “master” triathlete.
This is a what-if
analysis/situation. There isn’t an exact goal although there is a range that
you want to consider for the goal; but the kicker is that I can change multiple
inputs to still obtain the goal, so the changes would be to the input data of
swim, bike and run for Evan then watching the change in the total time in
hours.
15.
(18 pts)
Write an Excel formula in cell input!K4, which can be copied down and across to
input!M9, to determine Abby’s speed in miles per hour for the swim portion of
the triathlon. You MUST use the named range defined on the howfar worksheet for
this problem.
=HLOOKUP(K$3,far,2,FALSE)/C4*units!$B$4
No extra $ allowed
16.
(18 pts)
Write an Excel formula in cell input!N4, which can be copied down to N9, to
determine how much Abby will pay for the t-shirt given at the triathlon. The
t-shirt will be free if Abby’s total time in hours is less than an hour and a
half or if she is a level newbie, otherwise, Abby will have to pay $5 for the
t-shirt.
=IF(OR(G4<1.5,J4="newbie"),"free",5)
=IF(OR(G4<1.5, G4>=level!A6
),"free",5)
=IF(AND(G4>=1.5,J4<>"newbie"),5,"free")
=IF(AND(G4>=1.5,G4<level!A6),5,"free")
Optional $ on column
17.
(18 pts)
Between buying equipment, workout time, clothing and gym fees, you are spending
$550/month on training for the triathlon. Write an Excel formula in cell
input!N11 to determine (true or false) if you had invested that money instead,
would you have enough in 5 years to buy into part ownership of a gym costing
$40,000 where the interest rate is 10% compounded monthly.
=FV(10%/12,5*12,-550)>=40000
Can put ,0,0 or even ,, after the -550 argument
Can put 60 instead of 5*12
18.
(18 pts)
Write an Excel formula in cell input!N12 to determine what the annual interest
rate is if I want to buy into the gym membership in 4 years instead of 5 with
the same cost of $40,000, $550 monthly payment and interest being compounded
monthly.
=RATE(4*12,-550,0,40000)*12
Can put ,0 or
just a comma after the 40000
Can leave out the
zero, but must keep the commas
Can put 48
instead of 12*4
No comma allowed
in the 4th argument
Name: ______________________________ Lab Day/Time ____________ Seat# ________
Points: ____ /250