CSE200 AU07 KREEVES                                  QUIZ#2                     SEAT# ____________

NAME _______________________________________________        Lecture:  MW 1:30-3:18pm 

Lab section (check one):            _______ W 3:30-5:18pm             ________ R 1:30-3:18pm

Instructions:

· Filling in all the above data is worth 1 point.

· 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. 

· 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.

PROBLEM DESCRIPTON:

This problem starts where Quiz1 left off, so at this point, the input data is in columns A thru G on the PARKS worksheet. The park name, state and distance in miles is given in columns A-C; the regular ticket price per day is given as well as the discount amounts for senior and child tickets in columns D-F; and column G contains the “rank” of the listed parks from the closest distance designated as a 1 to the farthest away which is designated with a 6.

The INFO worksheet has conversion data on it that will be necessary for unit problems given below.

INFO

PARKS

*** The ONLY functions you can use on this test are given on the next page (AND, OR and NOT).

	FUNCTIONS AND(logical1, logical2,…) OR(logical1, logical2,…) NOT(logical)
INFO
		PARKS

1. (5 pts) Write an Excel formula in cell H3, which can be copied down to H8, to determine if Hershey park is one of the top 3 closest parks (as determined in column G). FYI: you have decided that you will visit the parks that are the top 3 closest parks.

=G3<=3 also =G3<4 best solutions

=OR(G3=1,G3=2,G3=3) also =NOT(G3>=4) also =NOT(G3>3) also AND(G3>=1,G3<=3) also = AND(G3>0,G3<4) acceptable solutions

If put AND(G3<=3) marked wrong but no deduction… unnecessary function.

Optional $ on column

2. (9 pts) Write an Excel formula in cell H9 (not shown), to determine if you will visit parks located only in Ohio. FYI: This solution assumes that the data in column B only will not change; all other data can change and the answer should still work. NOTE: the answer for the current data is FALSE.

=AND(OR(H4,H6,H7),NOT(OR(H3,H5,H8)))

=AND(OR(H4=TRUE,H6=TRUE,H7=TRUE),H3=FALSE,H5=FALSE,H8=FALSE)

No $ allowed

Can use NOT(H3) for those listed as =FALSE

Cannot use column G since data can change

3. (8 pts) In cell I3, I wrote a formula to determine if I will NOT visit Hershey Park, then I copied that formula down to I8. I tried the following formulas. Check YES or NO to designate if each of these formulas will work correctly or not:

YES NO

a. =NOT(G3<=3) yes _________ _________

b. =NOT(H3) yes _________ _________

c. =G3>4 no _________ _________

d. =H3=FALSE yes _________ _________

4. (6 pts) Write an Excel formula in cell J3, which can be copied down to J8, to determine the distance in kilometers to travel to Hershey park.

=C3/info!B$1

Optional $ on column

5. (9 pts) Write an Excel formula in cell K3, which can be copied down and across to M8, to determine the cost in becaws for a regular priced 1-day ticket to Hershey park.

=D3*info!$B$2/info!$B$3

no extra $ signs allowed

6. (7 pts) Write an Excel formula in cell N3, which can be copied down and across to P8, to determine if the cost of a regular ticket to Hershey park is between 100 and 200 becaws (inclusive).

=AND(K3>=100,K3<=200)

No $ allowed

Okay if have > instead of >= or < instead of <=

7. (5 pts) Write an Excel formula in cell Q3, which can be copied down to Q8, to determine if at least one of the becaw ticket costs for Hershey park is between 100 and 200 (inclusive).

=OR(N3:P3) best solution

The rest are not updatable if addition of different types of tickets – no deduction this time.

Also, way more complicated than above best solution.

=OR(N3=TRUE,O3=TRUE,P3=TRUE)

also OR(AND(K3>=100,K3<=200), AND(L3>=100,L3<=200), AND(M3>=100,M3<=200))

Optional $ on column

SCORE _____________/50