CSE200 SP07 KREEVES QUIZ#2 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.
· 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:
FDA worksheet – FDA stands for Food and Drug Administration. They set the daily healthy nutritional values for the American public. The values defined by the FDA for the average person are listed as input data in row 6 (colored in gray) and include the maximum caloric intake of 2000 calories, the maximum grams of fat, the maximum grams of carbohydrates and the minimum grams of fiber for a healthy diet. Other input data includes factual information of 9 calories per gram of fat in B2, 4 calories per gram of carbohydrate in C2 and 4 calories per gram of fiber D2.
CEREAL worksheet – This worksheet specifies nutritional values about certain cereals. The data in gray is given; the rest of the information will be determined by answering the below given problems.
1. (7 pts) Write an Excel formula in cell fda!B7, which can be copied across to cell fda!D7, to determine the percent of total calories that 65 grams of fat contributes to the average person’s diet.
=B6*B2/$A6
Optional $ on row
2. (6 pts) Write an Excel formula in cell cereal!E4, which can be copied down and across to cell cereal!F9, to determine if the fat % of calories for cookie crisp cereal falls within the FDA approved nutritional guidelines (as determined in the above problem). NOTE: this problem is NOT copied over to column G because that value for fiber is the minimum recommended daily amount of fiber whereas fat and carbohydrates are designated as the maximum recommended daily allowance.
=B4<=fda!B$7
No extra $ signs allowed
3. (5 pts) Write an Excel formula in cell cereal!H4, which can be copied down to cell cereal!H9, to determine if cookie crisp cereal is healthy. It is considered healthy if fat, carbs and fiber are all within the FDA nutritional guidelines.
=AND(E4:G4) – best solution :o)
=countif(e4:g4,true)=count(b4:d4)
Optional $ on column
4. (7 pts) Write an Excel formula in cell cereal!H11 to determine how many of the cereals on this list are considered healthy cereals.
=COUNTIF(H4:H9,TRUE)
No $ allowed
5. (8 pts) Write an Excel formula in cell cereal!B12, which can be copied across to cereal!D12, to determine the average percent of calories for healthy cereals. NOTE: remember that your answer should still work when the input data is updated/changed.
=SUMIF($H4:$H9,TRUE,B4:B9)/$H11
=SUMIF($H4:$H9,TRUE,B4:B9)/COUNTIF($H4:$H9,TRUE)
Optional $ on row
6. (7 pts) Write an Excel formula in cell cereal!H13 to determine the percent of cereals that are healthy cereals.
=H11/COUNT(D4:D9)
– best solution :o) columns B and C also work on the count function
=h11/(countif(h4:h9,true)+countif(h4:h9,false))
No $ allowed
7. (9 pts) Write an Excel formula in cell cereal!H14 to determine if only healthy cereals have the right amount of fiber. NOTE: this problem is not updatable.
***this is a tricky problem! It is not updatable, so anything that logically works, should be counted correct. I’ve come across some interesting solutions and I have to count them as correct ;o)
=COUNTIF(G4:G9,TRUE)=H11
=AND(G4=H4,G5=H5,G7=H7,G8=H8)
=AND(OR(G6,G9),NOT(OR(G4:G5,G7:G8)))
=AND(G6,G9,NOT(OR(G4:G5,G7:G8))
=NOT(OR(G4:G5,G7:G8))
=AND(G4=FALSE,G5=FALSE,G7=FALSE,G8=FALSE)
No $ signs allowed
SCORE _____________/50