CSE200 MIDTERM
Winter 2008 KReeves TR 1:30-3:18pm
Name
________________________________________
Seat #____________
Circle
Lab Day/Time: Thur 3:30pm Fri 1:30pm
Instructions:
·
Up to a maximum of -3 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
Most people have to get glasses sometime during their lifetime. They can be expensive, but the benefits definitely outweigh the negatives. You have several orders for glasses that you are working on. Of course, the lists will change as customers come and go. Just a small portion of the data is shown on the given worksheets. A description of each worksheet is given below, however, it will be helpful to know some vocabulary before moving on.
Ø Nearsightedness is the inability of the eye to focus on distant objects.
Ø Farsightedness is the inability of the eye to focus on near objects.
Ø The sphere value is the amount of long or short sightedness. This value specifies the optical power of a lens in diopters. The value will be minus for nearsightedness and positive to designate farsightedness.
Ø Diopter is a value that specifies the optical power of a lens. A zero values designate perfect vision that needs no correction.
Ø Astigmatism (it's one word) is when the cornea of the eye is shaped like a rugby ball rather than being spherical. It causes the eye to have two focal points rather than one and can lead to eye strain and headaches if uncorrected.
VISION – Input data
is given in the range A4:E11 and should be self-explanatory especially given
the above vocabulary and problem description.
GLASSES – Given data
is in the ranges/cells A4:H11 (see the
FYI given below), K2:O2, as well as Q1. The data in column C is the type of
frame that each person ordered for their glasses. There are 5 different options
that can be applied to a set of glasses as given in K2:O2. Be sure to remember
that these options can be added to or deleted and your solutions should still
work. Also, you MUST assume that the order of these options in D3:H3 will
always be in the same order as the K3:O3 options. In K2:O2 is the price for each option if it
is decided based on columns D thru H that the person wants that option. The
lens options are in columns I and J. The lens options represent either plastic
(option 1 as shown in I2) or polycarbonate (option 2 which is shown in J2).
This data is associated with the same type of information as on the FRAMES
worksheet.
FYI: The name and gender are on
both the vision and glasses worksheets only because there was so much data!
Notice that the first two columns
repeat data on the glasses worksheet and the vision worksheet. Again,
this is only for the convenience of printing.
FRAMES – This
worksheet contains all given data. The type of the glasses can also be found on
the glasses worksheet. Each type of glasses has a frame associated with it and
a base price for that frame. When you pay for glasses you also pay for the
lenses that come with it to correct your vision. These lenses can be made of
plastic (option1) or polycarbonate (option2). The numeric values 1 and two in
cells C2 and D2 respectively designate the option number for the lenses.
DIOPTERS – This
worksheet contains all given data. Depending on the sphere values, eye doctors
rate the degree of near or far sightedness as seen in row 3. On this worksheet,
a range of values is assumed for each value in columns B thru F for both rows 1
and 2. Also, on this worksheet, there exists a named range called far which is defined to be diopters!$B$1:$F$3.
EXCEL
PROBLEMS
1.
(16 pts) Evaluate the following. That is, determine the result of the
function:
=COUNT(vision!A4:A11) ___________________
=MAX(vision!C4:C11) ___________________
=LARGE(vision!C4:C11,3) ___________________
=SMALL(vision!C4:C11,3) ___________________
2.
(12 pts) Write an Excel formula in cell vision!C12, which can be copied
across to cell vision!D12, to determine the number of people who need
corrective vision for their left eye.
3.
(16 pts) Write an Excel formula in cell vision!E12 to determine if only
(but not necessarily all) males on the list have an astigmatism. The data in
column B4:B11 cannot change for this question only. However, the values in the
other columns can change and the answer should still work.
4.
(20 pts) Write an Excel formula in cell vision!F4, which can be copied
down to vision!F11, to determine if John is nearsighted or not based on the
following:
·
YES, John is nearsighted if he is nearsighted in both eyes i.e. if the
sphere value in both the left and right eyes are negative values.
·
NO, John is not nearsighted if he is farsighted in both eyes i.e. if
the sphere value in both the left and right eyes are positive values.
·
NOT SURE should be the result of this function otherwise i.e. if the
sphere values are either one positive and one negative or if at least one of
the values is zero.
5.
(18 pts) Write an Excel formula in cell vision!G4, which can be copied
down and across to cell vision!H11, to determine how farsighted, if at all,
John is in his left eye. NOTE: The description of how farsighted a person is
can be found on the diopters worksheet (see description given above) or if
John’s left eye is not farsighted, then the description “none” should be given.
Reminder: a person is only farsighted if the sphere value is a non-zero
positive number. Also, don’t forget to use the named range given on this
worksheet.
6.
(8 pts) Originally, I wanted to use the data in the range
diopters!B2:F3 to give a descriptive value for the nearsightedness for each of
John’s eyes. Why won’t using a reference function work?
7.
(28 pts) Write an Excel formula in cell glasses!I4, which can be copied
down and across to cell glasses!J11, to determine the base price for glasses
with plastic (option 1) lenses (see the frames worksheet and description). The
base price for this cell (i.e. option 1) includes the price of the frames plus
the cost of plastic lenses. HINT: the 3rd argument of a reference
function does not have to be a constant numeric value, but can be a formula of
some kind that returns a single numeric value.
8.
(12 pts) Write an Excel formula in cell glasses!K4, which can be copied
down and across to cell glasses!O11, to determine how much John will pay, if
any, for glare-reduction to be added to his glasses. NOTE: the cost of the
glare-reduction is given in cell glasses!K2.
9.
(16 pts) Write an Excel formula in cell glasses!P4, which can be copied
down and across to cell glasses!Q11, to determine the cost of John’s glasses,
plus tax, with the option1 lenses as well as all the extras (glare, scratch,
etc). NOTE: The tax is given in cell glasses!Q1. You should recognize by the
format what the precision of the cell is, so be sure to take that into account
- you can’t ask us ;o)
10.
(10 pts) Given the chart below, determine the following:
a.
What type of chart is shown? ______________________
b.
What is the range used for the data shown (be specific)? ________________________
11.
(15 pts) Write an Excel formula (not shown) to determine (true or
false) if the guys spend more money total on their glasses than all the girls
do. NOTE: you can use the option1 total or the option2 total as the result will
be the same. FYI: the answer for the given data is TRUE.
12.
(10 pts) When using the ROUND function:
a. What is the second argument if you want to
round a value to the nearest hundreds place?
_________
b. If the number used is 1,234.5678, what
would be the result of the function if this number is rounded to the nearest
hundreds place? _______________
13.
(16 pts) Write an Excel formula
(not shown) to determine the monthly payment for the start-up costs of opening
your own optical store where the start-up costs are $450,000 with 10% annual
percent rate of interest compounded quarterly over 5 years. One nice thing is
that you have a CD that will be worth $5,000 at the end of the 5 years so the
$5,000 can be used as a balloon payment to help lower the monthly payments.
14.
(8 pts) What are the possible values of the 5th argument of
a financial function; explain what each value represents.
15.
(16 pts) Instead of borrowing the money to open your own optical store,
you are going to rent a small place with a friend, and invest your part of the profit
toward a bigger place of your own. Write an Excel formula (not shown) to determine how long it will take, in years,
to save the necessary start-up costs ($450,000) if you are making a profit of
$10,000 per month (i.e. this is the amount you will be investing per month) and
the annual percentage interest rate is 12% compounded monthly.
16.
(8 pts) What is the biggest difference between the What-if Analysis and
a Goal Seek?
17.
(18 pts) Write an Excel formula using the given worksheet information
to determine how many bracks you will have if you are given 20 widgets.
Name: ______________________________ Lab Day/Time
____________ Seat# ________
|
18.
Evaluate the following. That is, determine the result of the
function: Okay if put decimal places as long as zero =COUNT(vision!A4:A11) ___________________ 0 =MAX(vision!C4:C11) ___________________ 3 =LARGE(vision!C4:C11,3) ___________________ 1 =SMALL(vision!C4:C11,3) ___________________ -1 |
||||||
|
=COUNTIF(C4:C11,"<>0")
optional $ on row |
||||||
|
=AND(OR(E4,E6:E9),NOT(OR(E5,E10:E11)))
no $ allowed |
||||||
|
· =IF(AND(C4<0,D4<0),"yes",IF(AND(C4>0,D4>0),"no","not
sure")) · =IF(AND(C4<0,D4<0),"yes",IF(OR(C4=0,D4=0,OR(AND(C4<0,D4>0),AND(C4>0,D4<0))),"not
sure","no")) · =IF(AND(C4>0,D4>0),"no",IF(OR(C4=0,D4=0,OR(AND(C4<0,D4>0),AND(C4>0,D4<0))),"not
sure", "yes")) · =IF(AND(C4>0,D4>0),"no",IF(AND(C4<0,D4<0),"yes","not
sure")) · =IF(OR(C4=0,D4=0,OR(AND(C4<0,D4>0),AND(C4>0,D4<0))),"not
sure", IF(AND(C4<0,D4<0),"yes","no")) · =IF(OR(C4=0,D4=0,OR(AND(C4<0,D4>0),AND(C4>0,D4<0))),"not
sure", IF(AND(C4>0,D4>0),"no","yes")) optional $ on column |
||||||
|
=(I4+SUM($K4:$O4))*(1+$Q$1/100) =SUM(I4,$K4:$O4)*(1+$Q$1/100) = SUM(I4,$K4:$O4)*$+ SUM(I4,$K4:$O4)*$Q$1/100 |
||||||
|
||||||
|
||||||
|
||||||
|
||||||
|
||||||
|
||||||
|
||||||
|
||||||
Points: ____ /250