CSE200 MIDTERM
Spring 2006 KReeves MW 8:30-10:18am
Name
_________________________________________________ Seat #____________
Circle Lab
Day/Time: Thur 9:30am Thur 11:30am
Instructions:
· -1 points 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. For example IF(cond,true,false) when the condition would be the correct answer.
· Do not use a complex answer 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 data that has not been solved for yet.
· Please do not use functions not covered in class.
· Use cell references and named ranges whenever possible.
DESCRIPTION
The input data is colored in gray. The original cells for the solutions are also shaded.
UNITS – The units worksheet has conversion data on it. Cells B1:B5 are considered input data.
SNACK – Input data about snacks are given on this worksheet in columns A thru F. In cell B3 are the number of calories per gram of fat. In cell C3 are the number of calories per gram of carbohydrate. Column D specifies the total calories per serving and column E designates the amount of serving measurement as shown in column F. So carrots have no fat, a serving is 1 cup which has 20 calories and 4 grams of carbs.
EXERCISE – This worksheet is all input data and defines the calories burned for different types of exercise.
MUNCHING – On this worksheet are the names of people, all the snacks they eat, ad the number of servings of each snack. Notice that names appear more than once. The first three columns are considered input data.
PEOPLE – This worksheet lists the names of the people – uniquely – and starts with exercise data i.e. the input data includes columns A, B and C.
EXCEL PROBLEMS
1. (15 pts) Write an Excel formula in cell
units!B8 to determine the number of grams there are in an ounce rounded to the
nearest tens place said whole number but changing for future. OOOPs! Cell
reference had -1 but announcement made in class to follow problem directions.
=ROUND(B4/B3,0)
No $ allowed (i.e. error; not
copying)
2.
(30 pts)
Write an Excel formula in cell snack!G5, which can be copied down to snack!G11,
to determine the number of grams based on the serving measurements of cups as given
in column F. That is, based on column F,
you need to:
·
Convert
the number of cups to grams, or
·
Convert
the number of ounces to grams, or
·
Convert
the number of widgets to grams
=IF(F5="cups",E5*units!$B$2*units!B$8,IF(F5="ounces",E5*units!B$8,E5*units!B$5))
E5 can be outside IF
Multiple solutions; be sure to
check that the correct math conversion goes with the correct text reference
(i.e. cups, ounces or widgets)
$ ok on column
3. (15 pts) Write an Excel formula in cell snack!H5, which can be copied down and across to snack!I11, to determine the number of fat calories in carrots.
=B5*B$3
No extra $ allowed
4.
(10 pts) I want to use a named range for snack!B3
called FATCAL and use it in the above solution. Will this work? Explain why or
why not.
Depends on explanation! Named ranges default to absolute with
respect to column and row. The explanation MUST mention something to do with $
or absolute addressing in some way
NO, since the above is a copy down and across, extra $ are
not allowed. B$3 cannot have a $ on the column, which is automatically assigned
when a named range is defined. However,
they can say YES if and only if they explain that they have to change the
default range to be b$3 instead of $b$3.
5. (15 pts) Write an Excel formula in cell snack!J5, which can be copied down to cell snack!J11, to determine the percent of fat calories as compared to the total number of calories for carrots. HINT: be careful of the divide by zero error.
=IF(D5=0,0,H5/D5)
=IF(D5>0,H5/D5,0)
$ ok on column
6. (12 pts) Write an Excel formula in cell snack!K5, which can be copied down to cell snack!K11, to determine if carrots are healthy. Carrots are healthy if one of two things happen: either (1) they have less than 30% fat and at least 2 grams of fiber – was changed to CARBS during the exam OR (2) they have 3 grams of fat or less.
=OR(AND(J5<0.3,C5>=2),B5<=3)
Watch for NOT equivalents ex. NOT(B5>3) ok i.e. same as
B5<=3
0.3 or 30%... same thing
$ ok on column
7. (8 pts) Write an Excel formula in cell snack!K14 to determine the number of snacks on the Munch’N’Burn list.
=COUNT(B5:B11)
Any column with numeric values will work (C, D, E also work;
G-J are ok, too)
No $ allowed (i.e. error; not
copying)
8. (6 pts) Write an Excel formula in cell snack!K15 to determine the average number of calories for a snack on the Munch’N’Burn list.
=AVERAGE(D5:D11)
No $ allowed (i.e. error; not
copying)
9. (6 pts) The actual value for cell snack!K15 is 112.857142857143. Explain why the value seen in this cell is a different value.
Because the cell is FORMATTED/DISPLAYED to have no decimal
places
10. (12 pts) Write an Excel formula in cell snack!K16 to determine the percent of snacks considered to be healthy snacks.
=COUNTIF(K5:K11,TRUE)/K14
No $ allowed (i.e. error; not
copying)
11. (10 pts) Choose which chart better shows the calories for each snack by defining the type of that chart and what data range in the workbook was used to create it.
Column or Bar chart – worth 5 pts
Range: =snack!$A$5:$A$11, snack!$D$5:$D$11
Ok if no $ or worksheet names
12. (25 pts) In trying to eat better snacks, I’ve decided that working out on an elliptical machine (my favorite) is another good way to stay healthy (ya know, diet AND exercise). I can either obtain a loan to buy the elliptical machine or get a membership to a fitness center. Given the following information, write an Excel formula to determine, true or false, if after 4 years, it is cheaper to use the elliptical machine at the fitness center than to obtain a loan to buy it and use it at home. The term for both the membership and the loan is 4 years. The fitness center membership fee is $250/year. The cost of the elliptical machine is $700 with a 7.5% interest rate compounded monthly. HINT: You need to determine how much you are spending at the fitness center after four years and compare that to how much you actually paid for the loan 4 years later.
=FV(7.5%/12,4*12,,-700)<250*4
No $ allowed (i.e. error; not
copying)
Can have zero as 4th
argument
13. (20 pts) Write an Excel formula in cell munching!D2, which can be copied down to munching!D10, to determine the number of calories John gains when eating carrots. The data can be found on the snack worksheet and includes calories (column D) as well as the number of servings on the current worksheet.
=VLOOKUP(B2,snack!A$5:D$11,4,FALSE)*C2
$ ok on column
14. (15 pts) Write an Excel formula in cell people!D2, which can be copied down to cell people!D5, to determine the calories burned by walking (cell people!B2) twice (cell people!C2).
=HLOOKUP(B2,exercise!B$1:E$2,2,FALSE)*C2
$ ok on column
15. (12 pts) Write an Excel formula in cell people!E2, which can be copied down to cell people!E5, to determine the total number of snack calories that John consumes.
=SUMIF(munching!A$2:A$10,A2,munching!D$2:D$10)
$ ok on column
16. (6 pts) Write an Excel formula in cell people!F2, which can be copied down to cell people!F5, to determine the calories John has gained/lost based on calories burned through exercise and calories consumed by snacks.
=E2-D2
$ ok on column
17. (12 pts) Write an Excel formula in cell people!F7 to determine, true or false, if anyone has lost weight. This formula should still work if a person is added to the list i.e. this formula should be updatable.
=MIN(F2:F5)<0
=COUNTIF(F2:F5,”<0”)>0
=COUNTIF(F2:F5,”>=0”)<COUNT(C2:C5)
No $ allowed (i.e. error; not
copying)
18. (15 pts) Write an Excel formula in cell people!F8 to determine, true or false, if John is the only person who lost weight i.e. is the only one with a negative calorie gain. NOTE: this solution is not updatable.
=AND(F2<0,NOT(OR(F3<0,F4<0,F5<0)))
=AND(F2<0,NOT(F3<0),NOT(F4<0),NOT(F5<0))
=AND(F2<0,MIN(F2:F5)>=0)
Instead of NOT(cell<0) can be
cell>=0
No $ allowed (i.e. error; not
copying)
Name: ________________________ Lab Day/Time ______________ Seat# ________
Points: ____ /250