CSE200 SP07 KREEVES                                         QUIZ#3                       SEAT# ____________

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

Lab section (check one):            _______ R 3:30-5:18pm             ________ F 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.

· Do NOT get up for ANY reason unless you are finished with your test. If you have a question, please raise your hand; do NOT get up to ask a question of the TAs or the instructor. You will not be allowed to leave the room until you have completed your exam.

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

· Only use quotes when necessary – do NOT use quotes around Boolean values.

· Do not use IF structures when not necessary ex. IF(B3>0,TRUE,FALSE)

· 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 DESCRIPTION:

INPUT worksheet – This worksheet has the percent of calories listed for fat, carbohydrates and fiber for a list of cereals. All of this data is input data i.e. given.

FAT worksheet – This worksheet specifies a description for each range of percent of calories from fat. That is, for 0 to less than 10%, it’s considered to be “awesome” for this range of percent of calories from fat; for 10% to less than 16%, the description is “excellent” for this range of percent of calories from fat; for 16% and less than 20%, the description is “good” for this range of percent of calories from fat, ETC.

CEREAL worksheet – Most of this worksheet information will be determined by answering the below given problems. There are a few input values given in gray and will be explained when needed in the below problems.

1. (9 pts) Write an Excel formula in cell cereal!B4, which can be copied down and across to cell cereal!D9, to determine the percent of calories contributed by fat for cookie crisp cereal. The input data for all the cereals is on the INPUT worksheet. When copying across, the values in cells B1:D1 will need to be used as a part of a formula for the column_index which should look something like B1+1 for the fat % of calories column.

=VLOOKUP($A4,input!$A$2:$D$11,B$1+1,FALSE)

No extra $ allowed

2. (7 pts) Write an Excel formula in cell cereal!E4, which can be copied down and across to cell cereal!F9, to determine whether or not the fat percent of calories, as given in E2, is good for your diet or not. The cereal fat % of calories is good for the diet if it’s less than the value given in E2.

=IF(B4<E$2,"diet","not")

=IF(B4>=E$2,"not",diet")

No extra $ allowed

3. (12 pts) Write an Excel formula in cell cereal!G4, which can be copied down to cell cereal!G9, to determine if the fat and carb values together are appropriate for the diet or not.

• If both values are good for the diet, then “best” is the result.
• If at least one of the values is good for the diet (but not both), then “okay” is the result.
• If neither of the values is good for the diet (i.e. both are not good for the diet) then “bad” is the result.

=IF(COUNTIF(E4:F4,"diet")=2,"best",IF(COUNTIF(E4:F4,"diet")=1,"okay","bad"))

Ø “best” can also be AND(E4=”diet”,F4=”diet”) or COUNTIF(E4:F4,"not")=0

Ø “okay” can also be OR(E4=”diet”,F4=”diet”) but only if ask “best” first since will be true if both are true

Ø “bad” can also be AND(E4=”not”,F4=”not”) or COUNTIF(E4:F4,”not”)=2 or COUNTIF(E4:F4,”diet”)=0

Ø If using all COUNTIFs, can put in any order; if using logical functions, BE CAREFUL of order since OR will be true if one or other is true but also if both are true!

Optional $ on column

4. (8 pts) Write an Excel formula in cell cereal!H4, which can be copied down to cell cereal!H9, to determine the description for fat health. See the FAT worksheet description above for more information, if necessary.

=HLOOKUP(B4,fat!B$1:G$2,2)

Optional $ on column

TRUE optional 4^th argument

5. (6 pts) You are going to start a “make your own cereal” store. Write an Excel formula in cell cereal!H11 (not shown) to determine the monthly payment for a loan taken out for $450,000 with a 5% interest rate compounded monthly for 5 years.

=PMT(5%/12,5*12,450000)

No $ allowed

6. (7 pts) Write an Excel formula in cell cereal!H12 (not shown) to determine the monthly payment for a loan amount of $450,000 at 5% interest rate compounded quarterly for 5 years

=PMT(5%/4,5*4,450000)/3

No $ allowed

SCORE _____________/50