CSE200 WI07
KREEVES
QUIZ#1
SEAT# ____________
NAME
_______________________________________________
Lecture: MW 9:30-11:18am
Lab section
(check one): _______ R 9:30-11:18am
________ R 11:30-1:18pm
Instructions:
·
Put away all books,
papers, and calculators.
·
Turn off all beepers
and cell phones.
·
Read each question
carefully and fill in the answer
in the space provided. Answers must be
legible or they will be marked incorrect.
If there are multiple answers to choose from, please CIRCLE the
correct
answer. The question will not be graded at all if there are multiple
answers to
choose from.
·
When time has run out
you will be told to put all
pens/pencils down. Failure to do so will
result in point penalization.
·
Be sure to use values
as determined by previous
problems and do not use values from problems that have not yet been
solved per
the ordering of the questions.
·
Use cell references
whenever possible.
·
Don’t use a $ if NOT
copying
·
Only use the
functions given.
·
Your answer should
update correctly when additional
input data is added to the problem or when input data is changed.
·
Your solutions
shouldn’t be overly complex.
TRUE/FALSE.
Circle one answer for each (1 pt each = 11 points total)
T
F Names
can be assigned to cells and
defines a mapping to its absolute address.
T
F An
Excel workbook is made up of multiple
worksheets.
T
F The
ROUND function changes the precision
of a cell.
T
F It
doesn’t matter what search engine you use when looking up information
on the
WWW.
T
F The
Excel operators and order of precedence issues are exactly the same
ones used
in regular math equations.
T
F The
power of Excel is in its ability to automatically update equation
results when
input values are changed.
T
F =MAX(A1:A5)
yields the same result as
=LARGE(A1:A5,1)
T
F The
following is a syntactically valid file path: Z:\Windows\Personal\Lab1.
T
F Windows
Explorer is used to manage your
files.
T
F When
using absolute addressing, the $ is
used when copying to keep that portion of the cell reference the same.
T
F The
#REF! Excel error value means that a
cell reference is not valid.
FUNCTIONS AVERAGE(number1,number2,…) COUNT (range) COUNTIF(range,criteria)
LARGE(array,k) MAX(number1,number2,…) MIN(number1,number2,…) RANK(
number, ref, order) ROUND(number,
num_digits) SMALL(array,k) SUM(number1,number2,…) SUMIF(range,criteria,sum_range)
*
For each problem, if multiple answers are
given, please CIRCLE the correct answer.
- (4 pts) Write an Excel formula in cell G5,
which can be copied down and across to I12, to
determine the cost of tuition for one 9 month period at PSU based on
the number of divisions per 9 months. There are 2 semesters and 3
quarters per 9 month period as given in column F.
=B5*$F5 no additional $ allowed
- (4 pts) Write an Excel formula in cell J5,
which can be copied down to J12, to determine how much it costs to go
to PSU for 4 years (where each “year” is one 9 month period).
=SUM(G5:I5)*4 optional $ on column allowed
- (5 pts) Write an Excel formula in cell K5,
which can be copied down to K12, to determine, from lowest to highest,
where PSU falls with respect to total cost (from the previous problem).
NOTE: the number 1 in column K indicates the lowest college cost in
this list.
=RANK(J5,J$5:J$12,1) optional $ on column allowed
- (6 pts) Write an Excel formula in cell G13,
which can be copied across to I13, to determine the average tuition
cost for all the colleges on the list rounded to the nearest hundreds
place (NOT hundredths place).
=ROUND(AVERAGE(G5:G12),-2) optional $ on row allowed
- (7 pts) Write an Excel formula in cell G14,
which can be copied down and across to I16, to
determine the lowest tuition cost.
=SMALL(G$5:G$12,$F14) no extra $ allowed
- (4 pts) Write an Excel formula in cell L13 to
determine the number of colleges on the list.
=COUNT(B5:B12) any numeric column is also accepted i.e.
CANNOT used
columns A or E; no $ allowed
- (6 pts) Write an Excel formula in cell L14,
which can be copied down to cell L15, to determine the percent of
colleges that are on the “quarter” system.
=COUNTIF(E$5:E$12,K14)/L$13 optional $ on column allowed
- (3 pts) The formula in cell L16 is =L14+L15.
Explain why the answer in cell L16 is 100%, but the values in cells L14
and L15 add up to 101%.
The
101% is only obtained from the
addition of the FORMAT/DISPLAY of L14 and L15; however, the
PRECISION/ACTUAL
values in these two cells add up to 100% which is why L16 has 100% in
it.
SCORE ____________/50