CSE200 MIDTERM
Spring 2007 KReeves
TR 1:30-3:18pm
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
As a game store owner, you are trying to get an idea of the habits of your customers. You are starting with a small selection of people but want to create some data that will easily allow you to eventually update to include all of your customers. The details of each customer is given 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:
GAMERS – This worksheet contains the input data (A3:D10) about each game playing customer. Remember that this is only a sample of the input data. Important to mention at this point, though, is that the game ratings in E2:I2 will be in the same order as the game ratings listed on the RATINGS worksheet given next.
RATINGS –The data in this worksheet specifies the game ratings in both rows 1 and 6. Notice that these ratings will be in the same order as those on the GAMERS worksheet… EC, E, T, M, AO. The description for each rating is given in row 2. A recommended age is associated with each game rating. Anyone who is at least 3 years old can play an EC rated game; anyone who is at least 6 years old can play an E game; anyone who is at least 13 years old can play a T game; anyone who is at least 17 years old can play an M game; and anyone who is at least 18 years old can play an AO game. The level, defined by one of the problems given below, is just another way to define each game rating.
PLAY – This worksheet defines the games played by each customer in a one week time frame. The input data is given in columns A thru D. The customer is listed on the worksheet multiple times; once for each game borrowed or bought. The game title is given as a letter because the description is given in another worksheet which we do not need at this time.
UNITS – Only the number of ratings currently in existence are listed in the worksheets, but you assume that additional ratings could be added at any time and you still want your worksheets and formulas to work; thus you have defined a cell reference B1 as a named range called numratings. Days/week are also given here to indicate the number of days you can play the games for the given cost (as determined on the PLAY worksheet for the given time frame).
EXCEL PROBLEMS
1.
(10
pts) Write an Excel formula in cell ratings!F5 to designate the maximum number of ratings (at this point in time)
as defined
on the UNITS worksheet which is associated with the level of the AO
game rating.
=numratings
2.
(8
pts) Write an Excel formula in cell
ratings!E5,
which can be copied across to B5, to determine the game rating for the M game rating. Notice that this
is being
copied to the left, an unusual occurrence but not invalid, to determine
the
remaining and descending levels for each game rating.
=F5-1
RANK(E3,$B3:$F3,1)
Optional $ on row
3.
(12 pts)
There is a chart on the worksheet page. What range is being used
to
create this chart? Is this the right kind of chart to use for this
data?
Explain why or why not.
ratings!b2:f3
– not the right
kind of chart because a pie chart represents parts of a whole and there
is no “parts
of a whole” comparison with the age in association with the
description/rating
Optional $ on range
Okay to start range at B1
4.
(27
pts) Write an Excel formula in cell
play!E3,
which can be copied down to play!E22, to determine if Brian is allowed
to play
the A game title he is trying to buy.
Brian is allowed to play the game if he is old enough to do so
which
means it is necessary to compare Brian’s age to the age associated with
the
game rating.
=VLOOKUP(A3,gamers!A$3:C$10,3,FALSE)>=HLOOKUP(C3,ratings!B$1:F$3,3,FALSE)
Optional $ on column
5.
(27
pts) Write an Excel formula in cell
play!F3,
which can be copied down to play!F22, to determine the cost Brian will
need to
pay for buying or renting this game. Brian will not pay anything if he
is NOT
allowed to get the game, however, if he is allowed to get the game, the
cost
is:
·
$15 if he
is buying a new game
·
$10 if he
is buying a used game
·
$5 if he
is renting the game
=IF(E3,IF(D3="buy
new",15,IF(D3="buy
used",10,5)),0)
=IF(not(E3),0,IF(D3="buy
new",15,IF(D3="buy used",10,5)))
=IF(AND(E3,D3="buy
new"),15,IF(AND(E3,D3="buy
used"),10,IF(AND(E3,D3="rent"),5,0)))
6.
(10
pts) What would happen if a data entry
error was given in the rating column i.e. play!C3:C22? For example,
what if
there was a value of “D” or “E “ (E with a blank following) in this
range?
Excel
error - #NA
7.
(8
pts) Write an Excel formula in cell
gamers!C11,
which can be copied across to gamers!D11, to determine the average age
of the
customers.
=AVERAGE(C3:C10)
=SUM(C3:C10)/COUNT(C3:C10)
8.
(12
pts) Pretend that =ROUND(C11,0) is given
in cell C12 and copied across to D12. What are the precision values in
the
following cells: C11, D11, C12, D12?
If they ask what the format is,
it doesn’t matter. The precision is the actual value of the cell, so
the format
of the decimal point has no influence on the answer!
C11=15.125,
D11=4.5625 both must
have all the decimal places; extra zeroes to the right are okay
C12=15,
D12=5 okay if have
decimal places but must be zeroes
9.
(14
pts) Write an Excel formula in cell
gamers!E3, which can be copied down and across to gamers!I10, to
determine
(true or false) if Brian can play a game with an EC rating. NOTE: as
specified
previously, the ratings on the GAMERS and RATINGS worksheet are assumed
to be
in the same order.
=$C3>=ratings!B$3 No extra $ allowed
10.
(7
pts) Write an Excel formula in cell
gamers!I11 to determine if everyone can play all of the games.
=AND(E3:I10)
=AND(I3:I10) since really only have to check for AO
logically to see if everyone can play all the games
11.
(17
pts) Write an Excel formula in cell
gamers!I12 to determine if only guys, but not necessarily all of them,
and none
of the females can play AO games. NOTE: this is the only problem on the
test
that is not updatable.
=AND(OR(I3,I4,I6:I8),NOT(OR(I5,I9,I10)))
Ok if put I3=TRUE (etc)
Ok if put I5=FALSE (etc)
with no NOT and no OR function
Ok if using C3>=18 for I3, etc
12.
(14
pts) Write an Excel formula in cell
gamers!J3, which can be copied down to J10, to determine the total
amount of
money Brian spent on playing games this week (see the play worksheet).
=SUMIF(play!A$3:A$22,gamers!A3,play!F$3:F$22)
Optional $ on column
13.
(14
pts) Write an Excel formula in cell
gamers!K3, which can be copied down to K10, to determine the cost per
hour
Brian spent on playing games this week.
=J3/D3/units!B$2
Optional $ on column
14.
(12
pts) Write an Excel formula in cell
gamers!L3, which can be copied down to L10, to determine the number of
game
rating types that Brian is allowed to play. For example, Brian can play
EC and
E games which gives the answer of 2 ratings that Brian can play.
=COUNTIF(E3:I3,TRUE)
Another solution is to
hlookup the level but have an exception for age
< the first level age
=IF(C3<RATINGS!B$3,0,HLOOKUP(C3,RATINGS!B$3:F$5,3,TRUE))
Of course, can switch the
arguments on the IF when change the condition’s
relational operator to >=
Can default the TRUE on the
hlookup!
15.
(20
pts) Write an Excel formula in cell
gamers!M3, which can be copied down to M10, to determine the highest
rating
that Brian is allowed to play according to his age.
Notice that some people might be too young to
actually play any of the games and thus the answer should be “none”.
=IF(L3>0,HLOOKUP(L3,ratings!B$5:F$6,2),"none")
=IF(L3>0,HLOOKUP(C3,ratings!B$3:F$6,4),"none")
Optional $ on column
Optional TRUE 4th
argument; actually can have FALSE 4th argument for the level hookup as
well (i.e. the first solution only)
16.
(20
pts) Write an Excel formula in the
gamers worksheet (cell not shown/known) to determine how many years it
will
take to save up for the $20,000 down payment I need to open another
store. I
will save 50% of my profit per month where the profit per month is
$2500 (the
other 50% of the profit is put back into the current store). The
current
interest rate is 10% compounded monthly.
=NPER(10%/12,-2500*0.5,0,20000)/12
Can also do -2500/2 instead
of -2500*0.5
17.
(8
pts) Financial functions have a 5th
argument. What are the possible values of this argument and what do
they mean?
0
– payment is made at the end
of the period
1
– payment is made at the
beginning of the period
18.
(8
pts) Explain the difference between a
What/If Analysis and a Goal Seek. Give an example of each using the
given
worksheets.
Goal
Seek allows
us to determine an Input
Value that would be needed to get a Given Output
·
Allows
us to work backwards in a worksheet
·
Lets
you vary only one input variable
What-if
analysis in the reverse
direction.
·
Allows
Us To See The Change In Outcome If We
Change The Input Values
·
Change
one or more input values
à
affected formulas are automatically recalculated
Name: ______________________________ Lab Day/Time ____________ Seat# ________
Points: ____ /250