CSE200  SP08   KREEVES                                   MIDTERM                                         SEAT#  ______________

 

NAME _______________________________________________________________________ TR lecture 1:30-3:18pm

 

Lab Time (check one):          _________ R 3:30-5:18pm       ________ F 1:30-3:18pm

Instructions:                                                                                                                                      

·         Put everything away except a picture ID and writing utensils. 

·         Turn off all beepers and cell phones.  Put hats on backwards.

·         Read each question carefully and fill in the answer in the space provided.  Answers must be legible or they will be marked incorrect.

·         Only one answer should be provided per question; if multiple answers are given, a score of 0 will be given.  You may circle the formula to show which answer is your final answer.

·         Be sure to determine the “best” answer.  That is, if the worksheet cannot be updated with your answer and there is another answer that works that can be updated automatically, you will have points deducted.

·         Do not put $ in formulas where the result is NOT being copied!

·         Whenever using a TRUE or FALSE value, remember it is assumed to be a boolean value, not a label or text.

·         Use cell references and named ranges whenever possible.

·         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 put your name, lab and seat# on this page and the answer sheet page.

·         You must turn in ALL of the pages of this exam!

 

PROBLEM DESCRIPTION

 

The coffee phenomenon has taken off and today’s drinking public is on board, but what are the results?!  The given workbook has put together facts about a small group of customers, which will be added to in the future, to check out some interesting information about the coffee drinking preferences from a particular store location.  The following worksheet information is given:

 

COFFEE – Input data about each individual is given in gray.  The data includes name, age, gender (male or female), drink type, milk options (defined more on the CALORIES worksheet), number of syrup servings (serving size is on the UNITS worksheet), if they like whipped cream (TRUE or FALSE) on their coffee drink and the size of the drink they like to order.  The rest of the data on this worksheet is in response to questions and concerns about the ordering habits of the customers.

 

CALORIES – This entire worksheet is considered input data.  Notice that there are 3 milk options; whole, soy and skim.  Each option has a numeric value associated with it (1, 2 or 3).  For each drink and milk type combination, calories per ounce are given. 

 

SIZE – Tied into the above CALORIES worksheet with the calories per ounce and the first worksheet with drink size, is the total ounces per size designated here.  The data in gray is given and is defined by a named range called sizes and is defined to be size!B1:D2.  The remaining rows are created in response to questions and concerns about the ordering habits of the customers. 

 

UNITS – All of the information here is considered input data and may be used for help in determining new data about each person.  The abbreviation “tbs” stands for tablespoon and “oz” for ounce.

 

NOTE:  The current problem assumes that milk will be used for all coffee drinks.  This is not necessarily true and would make a great update at some future time!

 

(2 pts)  Name: ___________________________    Lab:  _______ R3:30p    _______ F1:30p    Seat# _______

 

Answer Sheet CSE 200 – Midterm SP08 – TR 1:30-3:18p KReeves

PAGE 1

 

 (-)

Answer:

 

1.  (15 pts) Write an Excel formula to determine if only females (but not necessarily all the females) like whipped cream on their drinks.  For this problem, the only possible solution will NOT work if the gender is changed or rows are added (i.e. not updatable).  The answer for the given data, since a cell is not given or shown, is FALSE. 

 

 

 

 

 

 

2.  (12 pts) I want to determine if (true or false) everyone enjoys at least one serving of syrup in their drink.  Which of the following Excel formulas will correctly solve this problem?

YES          NO             =MIN(F4:F10)>=1  

YES          NO             =COUNTIF(F4:F10,0)=0

YES          NO             =AND(F4:F10>0)

YES          NO             =LARGE(F4:F10,COUNT(F4:F10))<>0

 

 

3.  (8 pts)  Write an Excel formula in cell Coffee!I4, which can be copied down, to determine if Allie likes both whip and syrup in her drink.

 

 

 

 

4.  (15 pts)  Write an Excel formula in cell Coffee!J4, which can be copied down, to determine how many ounces are in Allie’s drink. NOTE: Some coffee houses use small, medium and large sizes instead of tall, grande and venti. Be sure to choose a solution that will allow the information in cells size!B1:D1 to be changed and have the solution still work when the data in coffee!H4:H10 matches the new size descriptions. REMINDER: Use the given named range for this problem.

 

 

 

 

 

 

 

5.  (26 pts)  Write an Excel formula in cell Coffee!K4, which can be copied down and across to cell Coffee!M10, to determine the number of calories per ounce for the milk option chosen by Allie.  NOTE:  Only one of the three milk options will have calories greater than 0 per customer. Also, you can assume that the columns “whole”, “soy”, and “skim” will always be in the same order on both the coffee and calories worksheets.

 

 

 

 

 

 

 

 

 

6.  (14 pts)  Write an Excel formula in cell Coffee!N4, which can be copied down, to determine the total number of calories for the milk that is in Allie’s drink.  Remember to check the UNITS worksheet for the percent of milk per drink.

 

 

 

 

 

 

 

 

 

7.  (16 pts)   Write an Excel formula in cell Coffee!O4, which can be copied down, to determine the total number of calories for Allie’s entire drink.  The total calories include the milk option calories, the calories from the whipped cream, and the calories from the syrup (see the units worksheet for whip and syrup calories).

 

 

 

 

 

 

 

 

8.  (12 pts)  Write an Excel formula in cell Coffee!P4, which can be copied down, to determine the order of the types of drinks given on the list if the preference is to have the least number of calories. For example, there is a 1 for the first preference of Allie’s drink because it has only 60 calories which is the least number of total calories on the list; there is a 2 for Greg’s drink because it has the second smallest number of calories on the list; etc.

 

 

 

 

 

 

 

 

9.  (15 pts)  Write an Excel formula in cell Coffee!Q4, which can be copied down, to determine the number of ounces of syrup in Allie’s drink.

 

 

 

 

 

 

 

 

 

 

 

 

Points Deducted = ___________


(2 pts)  Name: ___________________________    Lab:  _______ R3:30p    _______ F1:30p    Seat# _______

 

Answer Sheet CSE 200 – Midterm SP08 – TR 1:30-3:18p KReeves

PAGE 2

 

 (-)

Answer:

 

10. (18 pts)  Write an Excel formula in cell Coffee!R4, which can be copied down, to determine what type of drinker Allie is based on the following:

·         A serious drinker is someone who likes to drink regular types of drinks.

·         A cool drinker is someone who likes to drink lattes.

·         A trendy drinker is someone who likes to drink either cappuccinos or frappacinos.

 

 

 

 

 

 

 

11. (12 pts)  Write an Excel formula in cell Size!B3, which can be copied across, to determine the number of tall drinks ordered by the customers.

 

 

 

 

 

 

 

 

 

12. (18 pts)  Write an Excel formula in cell Size!B4, which can be copied across, to determine the percent of tall drinks ordered by the customers rounded to the nearest percent.

.

 

 

 

 

 

 

 

 

 

13. (15 pts)  Write an Excel formula in cell Size!B5, which can be copied across, to determine the average number of calories for a tall drink as ordered by the customers.

 

 

 

 

 

 

 

 

14. (18 pts) You are going to open a coffee house on campus with a startup loan of $20,000. You are going to use some of your college fund money which comes in each quarter to make the quarterly payment of $1500. Write an Excel formula to determine the number of quarters you will pay on the loan with 8% (annual percentage rate) compounded monthly.

 

 

 

 

 

 

 

15. (10 points) What are the values of the 5th argument of a financial function and what do they mean?

 

 

 

 

 

16. (11 pts)  For the chart given below, what is the type of chart?  What is the range used to create it?  Be sure to include the worksheet name for the range.

TYPE:

 

 

 

RANGE:
 
       

 

17. (6 pts) In creating this test, I was playing with the percent of milk (see units!B6) for each drink to try to get simpler values for the results of the given equations (as solved for above).  Is this considered a “goal seek” or a “what-if analysis”?

 

 

 

 

18. (5 pts) When do you use a TRUE value as the 4th argument of a reference function?

 

 

 

 

 

 

 

 

 

Points Deducted = ___________                                                

 

 

Score ____________ / 250