CSE200 SP10
KREEVES MIDTERM
SEAT# ____________
NAME _______________________________________________ Lecture: TR 1:30-3:18pm
Lab section (check one): _______ F 1:30-3:18pm ________ F 3:30-5:18pm
Instructions:
· Filling out your name, correct seat# and lab section on both answer sheets is worth 4 points.
· Turn off all beepers, cell phones and anything else that you can turn off that might make noise.
· 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.
· Put away all books, papers, calculators, cell phones and music devices. There WILL BE deduction of 50 points minimum with the possibility of a score of ZERO for not adhering to these directions.
· When time has run out you will be told to put all pens/pencils down. There WILL BE a point penalization with the possibility of a score of ZERO for not adhering to these directions.
· Please stay in the answer box for each question i.e. do not write an essay for a short answer problem!
· 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 and appropriate.
· Do not use a $ if NOT copying.
· You are only allowed to use the given functions.
· Do NOT put quotes around Boolean values.
· Do not use unnecessary functions, for example =average(sum(a1:a5)). If you use an extra function that is not needed (the “average” function in this example), it’s assumed that you thought it was necessary thus will be marked incorrect.
· Caveat/Reminder to above: Do not use =IF(cond, TRUE, FALSE) as the IF structure in this case is unnecessary.
· All of the values are formatted as shown unless otherwise specified. That is, do NOT use a ROUND function unless you are specifically asked to round the value(s).
· Do NOT use a nested IF function when a reference function will work since the reference function is a better more updatable less complex solution.
· Your answer should update correctly when additional input data is added to the problem or when input data is changed – unless otherwise noted.
Worksheets
on other side ==========è
Name:
______________________________ Lab: _______ 1:30pm _______ 3:30pm Seat#
_______
PROBLEM DESCRIPTION
You
are considering taking a trip to visit castles around the world so you have
collected information about some famous castles that you would like to visit. You have decided to computerize some of the data
you have collected (just a portion of it) and have set up the following worksheets:
CASTLES - On this
worksheet, there is non-consecutive input data given in the following ranges: A4:F14, H4:K14, and
N4:N14. The reason for this is to help in keeping the data flowing through the
given problems. Starting in column A, the name of each castle is given, then
the country location, followed by the year it was built; then the type of each castle i.e. it’s function
which is palace, fortress, manor, monastery, or home. These same function
values are listed on the other worksheet described
next.
In
column E is a unit designation where “m2” stands for meters squared and “ft2”
stands for feet squared. The floor space area for each castle is given in
column F based on the unit given in column E. For instance, the Neuschwanstein
castle in Germany is a palace that was built in 1869 and the floor space in the
castle is given as 6,000 meters squared. Additional information in the next range
includes the number of floors in the castle, the number of rooms, as well as
whether or not (true/false Boolean values) the castle has gardens followed by
whether or not the castle has towers. The last column of input, column N, is a
copy of the castle names given in column A… there’s reason for this duplication
of data :o)
OTHER – Besides the data in A3:A7, which is given, all of the
other information will be determined when answering the following questions.
The data in this column matches the function information given in the castles
worksheet in castles!D4:D14 although each function is listed only once. Know
that if a different function is added to the castles worksheet,
the function will also be added to this worksheet for updatability purposes.
UNIT – Conversion factors are given here. Although some are well known
facts, you are to use these cell
references whenever necessary to show proficiency and knowledge in
using constant values that may need to change in the future.
HISTORICAL – This worksheet assigns a historical significance
description depending on the year a castle was built. Castles built before year
1000 are considered a “treasure”.; those built from year 1000 to before year
1500 are considered “heritage” castles; those built from year 1500 to before
1650 are considered to be “nominated” for their historical significance;
castles built from year 1650 and before 1900 are not sure of their historical significance
yet therefore have “depends” for a description; and last, any castle built in
1900 or after have “none” for a historical significance.
There is a NAMED RANGE defined on this worksheet that
you MUST USE.
The defined range is HISTORICAL!$B$1:$F$2 and the name
is HISTORY
SCRATCH:
Name:
______________________________ Lab:
_______ 1:30pm _______ 3:30pm Seat# _______
Answer Sheet CSE 200 – Midterm SP10 – TR 1:30-3:18pm
KReeves
|
1.
(16 pts) Write an Excel formula in cell castles!F16 to
determine the age (in years) of the oldest castle on the list. |
|
|
2. (25
pts) Write an Excel formula in cell castles!G4, which can be copied down to
cell castles!G14, to determine the size, in
acres, of the floor space in the Neuschwanstein castle. REMINDER: You
might want to re-read the castle worksheet description, 2nd
paragraph. |
|
|
3.
(12 pts) Write an Excel formula in cell other!B3, which can be copied
down to cell other!B7, to determine how many palaces are on the castle list. |
|
|
4.
(16 pts) Write an Excel formula in cell other!C3, which can be copied
down to cell other!C7, to determine the percent of castles on the list that
are palaces, rounding the value to the nearest whole percent. |
|
|
5.
(6 pts) When you sum the values
in other!C3:C7, you get the value 99% exactly; no decimal portion at all
(they are all zeroes).. Explain why. |
|
|
6.
(20 pts) Write an Excel formula
in cell other!D3, which can be copied down and across to other!F7, to
determine the average size, in acres, of all the palaces on the list of
castles. |
|
|
7. (25 pts) Write an Excel formula
in cell castles!L4, which can be copied down to cell castles!L14, to
determine if the Neuschwanstein castle has “both” gardens and towers,
“neither” gardens nor towers, or “one” of either a garden or a tower. |
|
|
8.
(10 pts) You decide you only want to visit palaces in Germany. Write
an Excel formula in cell castles!M4, which can be copied down to cell
castles!M14, to determine if Neuschwanstein is a castle you want to visit. |
Points
Deducted PAGE 1 = __________________
Name:
______________________________
Lab: _______ 1:30pm _______ 3:30pm Seat# _______
Answer Sheet CSE 200 – Midterm SP10 – TR
1:30-3:18pm KReeves
|
9.
(16 pts) Write an Excel
formula in cell castles!F17 to determine if Germany is the only country that
contains palaces on the castle list. |
|
|
10.
(25 pts) Write an Excel formula in cell castles!B20, which can be
copied down and across to cell castle!D22, to determine the name of the
castle that has the most number of
acres (i.e. size in acres) of floor space. NOTE: The solution to the problem
is shown below, not on the worksheet page… it wouldn’t fit ;o) Also, the
numbers given in cells castles!A20:22 and castles!B18:D18 are important –
they are given in the worksheet because you need to use them…but of course we
cannot tell you how or why, so please don’t ask.
|
|
|
11. (16 pts) Write an Excel formula
in cell castles!O4, which can be copied down to cell castles!O14, to
determine the historical significance description, as given on the historical
worksheet, for the Neuschwanstein castle.. |
|
|
13. (20 pts) The trip to all of the castles with
airfare, hotel costs, food, etc, is going to cost $33,000. You went to the
bank to get a loan. Write an Excel formula (cell not shown) to determine how
long it will take to pay off the loan in years with a payment of $500 per
week at 10% annual interest rate compounded monthly. NOTE You cannot assume
there are 4 weeks per month (it’s not true) or the number of days in a month,
but you can use any other constant value conversion that you might need to
solve the problem i.e. 52 weeks in a year, 12 months in a year, 365 days in a
year - we’ll ignore the leap year problem ;o) |
|
|
14. (10 pts) I want to create a
chart that will show, based on the total size in acres for all of the castles
combined, what percent of the total size in acres each castle has. |
|
|
15.
(6 pts) Define both of the following terms: What-if and Goal Seek. Be
sure that the definitions you give show the differences between the two
options. |
|
|
16. (17 pts) Based on these cell
references coming from the castles worksheet, determine the results for the
following functions: a.
=COUNT(A4:A14) ____________________ b.
=SMALL(F4:F14,5)
____________________ c.
Putting the following function in P4 and copy it down
to P14, =RANK(H4,H$4:H$14)
|
Points
Deducted PAGE2 = ___________ Score
____________ / 250