CSE200 SP11 KREEVES MIDTERM PICKUP# ____________

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

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

Instructions:

· Turn off all beepers, cell phones and anything else that you can turn off that might make noise.

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

· Put away all books, papers, calculators, cell phones and music devices. There WILL BE deduction of 50 points minimum with the possibility of a score of ZERO for not adhering to these directions.

· When time has run out you will be told to put all pens/pencils down. There WILL BE a point penalization with the possibility of a score of ZERO for not adhering to these directions.

· Please stay in the answer box for each question i.e. do not write an essay for a short answer problem!

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

· Do not use a $ if NOT copying.

· You are only allowed to use the given functions.

· Do NOT put quotes around Boolean values.

· Do not use unnecessary functions, for example =average(sum(a1:a5)). If you use an extra function that is not needed (the “average” function in this example), it’s assumed that you thought it was necessary thus will be marked incorrect.

· Caveat/Reminder to above: Do not use =IF(cond, TRUE, FALSE) as the IF structure in this case is unnecessary.

· All of the values are formatted as shown unless otherwise specified. That is, do NOT use a ROUND function unless you are specifically asked to round the value(s).

· Do NOT use a nested IF function when a reference function will work since the reference function is a better more updatable less complex solution.

· Your answer should update correctly when additional input data is added to the problem or when input data is changed – unless otherwise noted.

Math Reminder… when determining a discount i.e. say there is a 20% off sale, you can calculate the discounted price with the following equation where the below price is the original cost of the item:

PRICE – PRICE*DISCOUNT… which is equivalent to… PRICE * (1 - DISCOUNT)

There is an assumption as well that the discount is a value less than one (per what percent usually means). You might find this equivalent philosophy useful. Although you can use either calculation, as they are equivalent, you might find that the second option ends up being a little easier.

Name: ______________________________ Lab: _______ 1:30pm _______ 3:30pm Pickup# _______

PROBLEM DESCRIPTION

You have always enjoyed reading and collecting comic books yourself and would like to set up collections from your friends to help them determine the value of their comic books in the hopes that you can one day soon open your own comic book store. You have decided to computerize some of the data you have collected (just a portion of it) and have set up the following worksheets:

COLLECT - The following information is given as input data on this worksheet: A4:E22, F2, and H2:J3. Names of people who own comic books, the comic books they own (i.e. the TAG), whether or not they are first edition issues, the CGC scale and the condition number are designated in the first 5 columns of the worksheet.

Comic books are graded on a scale from 0 to 10, and the higher the Comics Guaranty Company (CGC) rates the comic, the more valuable it is. Additional information is given on the PRICES worksheet (more description given below) for help in determining the price of the comic book based on the title and the CGC rating. The condition number is assigned by a comic book evaluator with experience in the field to determine the defects and problems with the physicality of the comic book (fading, rips, stains, etc). The values range from 1 to 10 and the higher the value, the better the condition the book is in; see the description of the CONDITION worksheet for more information. A comic book is rated even higher if the issue is a first edition; see the price addition percent in cell F2 for a first edition. The price or worth of a particular comic book is related to these three factors: how valuable it is, the condition it is in, and if it is a first edition or not.

Last but not least is the currency converter information in H2:J3. Each row 3 column specifies the amount of dollars is equivalent to the currency given in row 2. For instance, the conversion factor from dollars to euros is $1.50 for each euro.

PRICES – This worksheet has input data specified in ranges A4:M15, as well as, A19:A21. The use of the A19:A21 will be obvious when the problem is given that is associated with cell C19. The larger section of input data starting with row 4 associates the TAG (or comic book identification) with the CGC rating. For instance, the Batman comic books have a TAG of 2A and a CGC rating of 4 for this comic book has a price of $1.99 whereas the CGC rating of 5 for this same comic book has a price of $3.00; with a maximum value with a CGC rating of 10 that is worth (i.e. can sell for) $25.00.

CONDITION – This worksheet has only input data on it. Each value in B3:G3 represents a range of values. That is, a condition number from 0 to less than 3, has a description of “bad” and a reduction or worth/price of 50%; a condition number from 3 to less than 5 has a “poor” description and a reduction of 30%; a condition number from 5 to less than 7 has a “fair” description and a reduction of 20%; a condition number from 7 to less than 9 has a description of “ok” and a reduction of 15%; a condition number of 9 has a description of “near mint” and a reduction of 5%; and a condition number of greater than or equal to 10 has a “mint” description and no reduction (i.e. a reduction of 0%). REMEMBER:

There is a NAMED RANGE defined on this worksheet that you MUST USE.

The defined range is CONDITION!$B$3:$G$5 and the name is REDUCT.

COMICS – This worksheet defines the name of the publisher and the series associated with each TAG value along with the original creation date of the very first issue and the number of issues for the series. The designations in G3:I3 are given per column and specify whether the problem solving information should be determined as ascending order (“asc”) or descending order (“desc”).

(-)	Answer:
	1. (30 pts) Write an Excel formula in cell collect!F4, which can be copied down to collect!F22, to determine the worth/price of Alex’s 2A comic book. The price, which is based on the CGC scale, is given on the PRICES worksheet (as explained in the descriptions given on page 3). In addition, if the comic book is a first edition, then an additional 20% (see cell collect!F2) of the CGC price is added to the worth of this comic book. For Alex’s 2A comic book with a CGC value of 10, the price is $25 and since the comic book is a first edition, 20% of $25 (i.e. $5) is added to the price giving a worth total of $30 for this comic book.
	2. (25 pts) Write an Excel formula in cell collect!G4, which can be copied down to collect!G22, to determine the new worth of Alex’s 2A comic book based on the condition of the comic book. The percent reduction in price is given on the CONDITION worksheet (as explained in the descriptions on page 3). REMEMBER to use the named range on this solution.
	3. (15 pts) Write an Excel formula in cell collect!H4, which can be copied down and across to collect!J22, to determine the worth/price of Alex’s 2A comic book in euro’s.
	4. (25 pts) Write an Excel formula in cell collect!K4, which can be copied down to collect!K22, to determine the rating for obtaining Alex’s 2A comic book from him. The rating is: Ø “high” for comic books that are first editions with a CGC value greater than 4 Ø “medium” for comic books that are not first editions but have a CGC value greater than 7 Ø “low” for all other comic books
	5. (10 pts) Write an Excel formula in cell collect!L4, which can be copied down and across to collect!N22, to determine if Alex’s 2A comic book has a “high” rating. Notice that the “high” rating is based on the current column and that “medium” and “low” are subsequent column determinations as you copy across.
	6. (15 pts) Write an Excel formula in cell collect!L23 (not shown) to determine if *only* first editions have a “high” ranking. NOTE: the solution for the given data is TRUE. Reminder: the only data you can assume that will not change for this problem is column C; however, all of the other information can change and your answer should still work.
	7. (10 pts) Write an Excel formula in cell prices!C17, which can be copied across to cell prices!M17, to determine the number comic books with a CGC rating of zero that are on the being collected (see the COLLECT worksheet).
	8. (12 pts) Write an Excel formula in cell prices!C18, which can be copied across to cell prices!M18, to determine the average price for the CGC zero ratings listed above rounded to 2 decimal places (i.e. change the precise value, not just the formatted value).

Points Deducted PAGE 1 = __________________

Name: ______________________________ Lab: _______ 1:30pm _______ 3:30pm Pickup# _______

Answer Sheet CSE 200 – Midterm SP11 – TR 1:30-3:18pm KReeves

PAGE 2

(-)	Answer:
	9. (15 pts) Write an Excel formula in cell prices!C19, which can be copied down and across to cell prices!M21, to determine the largest cost on the CGC rating for 0. When copying down, the 2^nd and 3^rd largest costs should be determined automatically; as the copy across is implemented, the 1^st, 2^nd and 3^rd largest prices for each CGC rating should be determined.
	10. (10 pts) Write an Excel formula in cell comics!F5, which can be copied down to cell comics!F16, to determine the number of years old the 1A Dark Horse Buffy the Vampire Slayer comic book series currently is. NOTE: Check the UNITS worksheet for some important information and remember to use cell references whenever possible. In addition, since you are not given the exact date, just the year that the comic book series was started, the age in years will be an approximate age.
	11. (20 pts) Write an Excel formula in cell comics!G5, which can be copied down and across to cell comics!I16, to determine the ranking for the creation date in ascending order (i.e. where the smallest value gets a ranking of 1) of the 1A Dark Horse Buffy the Vampire Slayer comic book series. Notice that the value “asc” is in G3 to designate the order of the ranking for column G with the values in H3:I3 designating the same for the other 2 columns, respectively.
	12. (15 pts) Write an Excel formula in cell comics!J5, which can be copied down to cell comics!J16, to determine the total worth/price for all of the 1A comic books as determined earlier on the COLLECT worksheet in column G.
	13. (8 pts) Explain why the value in comics!J5 is different than the value in collect!G11.
	14. (12 pts) Write an Excel formula in cell comics!K5, which can be copied down to cell comics!K16, to determine percent of worth/price for the 1A comic book series with respect to the total worth/price of all of the comic books put together.
	15. (8 pts) Given the result values just determined in the above problem (i.e. values in comics!K5:K16), what is the best type of chart to represent this information?
	16. (20 pts) You have been saving since 1996 to open your own comic book store. You put away an average of $150 per month at an annual percentage rate of 10% compounded quarterly. Write an Excel formula in cell collect!O23 (not shown) to determine how much money you will have to start up your own store today. NOTE: Check the UNITS worksheet for some important information and remember to use cell references whenever possible.

Points Deducted PAGE2 = ___________ Score ____________ / 250

Name: ______________________________ Lab: _______ 1:30pm _______ 3:30pm Pickup# _______

Answer Sheet CSE 200 – Midterm SP11 –TR 1:30-3:18pm KReeves

PAGE 1

(-)	Answer:
	1. (30 pts) Write an Excel formula in cell collect!F4, which can be copied down to collect!F22, to determine the worth/price of Alex’s 2A comic book. The price, which is based on the CGC scale, is given on the PRICES worksheet (as explained in the descriptions given on page 3). In addition, if the comic book is a first edition, then an additional 20% (see cell collect!F2) of the CGC price is added to the worth of this comic book. For Alex’s 2A comic book with a CGC value of 10, the price is $25 and since the comic book is a first edition, 20% of $25 (i.e. $5) is added to the price giving a worth total of $30 for this comic book. *=VLOOKUP(B4,prices!A$4:M$15,D4+3,FALSE)IF(C4,1+F$2,1) =IF(C4,VLOOKUP(1+F$2),VLOOKUP)* *=IF(C4,VLOOKUP+VLOOKUPF$2,VLOOKUP) Optional $ on column**
	2. (25 pts) Write an Excel formula in cell collect!G4, which can be copied down to collect!G22, to determine the new worth of Alex’s 2A comic book based on the condition of the comic book. The percent reduction in price is given on the CONDITION worksheet (as explained in the descriptions on page 3). REMEMBER to use the named range on this solution. *=F4(1-HLOOKUP(E4,reduct,3,TRUE)) =F4 – F4HLOOKUP* Optional TRUE argument (i.e. can leave off the 4^th argument entirely since defaults to TRUE) Optional $ on column
	3. (15 pts) Write an Excel formula in cell collect!H4, which can be copied down and across to collect!J22, to determine the worth/price of Alex’s 2A comic book in euro’s. =$G4/H$3 No extra $ allowed
	4. (25 pts) Write an Excel formula in cell collect!K4, which can be copied down to collect!K22, to determine the rating for obtaining Alex’s 2A comic book from him. The rating is: Ø “high” for comic books that are first editions with a CGC value greater than 4 Ø “medium” for comic books that are not first editions but have a CGC value greater than 7 Ø “low” for all other comic books =IF(AND(C4,D4>=5),"high",IF(D4>=8,"medium","low")) “high” “medium” or “low” can be in any order as long as the correct condition is correct! D4>=5 same as D4>4, etc. Optional $ on column AND(C4,D4>=5) always good condition for “high” AND(NOT(C4),D4>=8) always good condition for “medium” If “low” first, then condition can be: =OR(D4<5,AND(NOT(C4),D4>=5,D4<=7)) as well as =AND(NOT(AND(C4,D4>=5)),NOT(AND(NOT(C4),D4>=8))) where the blue says it’s not “high” and the green says it’s not “medium”
	5. (10 pts) Write an Excel formula in cell collect!L4, which can be copied down and across to collect!N22, to determine if Alex’s 2A comic book has a “high” rating. Notice that the “high” rating is based on the current column and that “medium” and “low” are subsequent column determinations as you copy across. =$K4=L$3 No extra $ allowed
	6. (15 pts) Write an Excel formula in cell collect!L23 (not shown) to determine if *only* first editions have a “high” ranking. NOTE: the solution for the given data is TRUE. Reminder: the only data you can assume that will not change for this problem is column C; however, all of the other information can change and your answer should still work. =AND(OR(L4,L6,L15,L20),NOT(OR(L5,L7:L14,L16:L19,L21:L22))) No $ allowed
	7. (10 pts) Write an Excel formula in cell prices!C17, which can be copied across to cell prices!M17, to determine the number comic books with a CGC rating of zero that are on the being collected (see the COLLECT worksheet). =COUNTIF(collect!$D4:$D22,C3) Optional $ on row
	8. (12 pts) Write an Excel formula in cell prices!C18, which can be copied across to cell prices!M18, to determine the average price for the CGC zero ratings listed above rounded to 2 decimal places (i.e. change the precise value, not just the formatted value). =ROUND(AVERAGE(C4:C15),2) Optional $ on row

Points Deducted PAGE 1 = __________________

Name: ______________________________ Lab: _______ 1:30pm _______ 3:30pm Pickup# _______

Answer Sheet CSE 200 – Midterm SP11 – TR 1:30-3:18pm KReeves

PAGE 2

(-)	Answer:
	9. (15 pts) Write an Excel formula in cell prices!C19, which can be copied down and across to cell prices!M21, to determine the largest cost on the CGC rating for 0. When copying down, the 2^nd and 3^rd largest costs should be determined automatically; as the copy across is implemented, the 1^st, 2^nd and 3^rd largest prices for each CGC rating should be determined. =LARGE(C$4:C$15,$A19) No extra $ allowed
	10. (10 pts) Write an Excel formula in cell comics!F5, which can be copied down to cell comics!F16, to determine the number of years old the 1A Dark Horse Buffy the Vampire Slayer comic book series currently is. NOTE: Check the UNITS worksheet for some important information and remember to use cell references whenever possible. In addition, since you are not given the exact date, just the year that the comic book series was started, the age in years will be an approximate age. =units!A$1-D5 Optional $ on column
	11. (20 pts) Write an Excel formula in cell comics!G5, which can be copied down and across to cell comics!I16, to determine the ranking for the creation date in ascending order (i.e. where the smallest value gets a ranking of 1) of the 1A Dark Horse Buffy the Vampire Slayer comic book series. Notice that the value “asc” is in G3 to designate the order of the ranking for column G with the values in H3:I3 designating the same for the other 2 columns, respectively. =RANK(D5,D$5:D$16,IF(G$3="asc",1,0)) =IF(G$3=”asc”,RANK(D5,D$5:D$16,1),RANK(D5,D$5:D$16,0)) zero optional Also can put =”desc” first and switch the RANK function 3^rd arguments No extra $ allowed Case insensitive, so okay if caps or lower case
	12. (15 pts) Write an Excel formula in cell comics!J5, which can be copied down to cell comics!J16, to determine the total worth/price for all of the 1A comic books as determined earlier on the COLLECT worksheet in column G. =SUMIF(collect!B$4:B$22,A5,collect!G$4:G$22) Optional $ on column
	13. (8 pts) Explain why the value in comics!J5 is different than the value in collect!G11. “The actual values are equal”… OKAY by itself “Just the formatted values look like they are different” doesn’t say WHY they are different; how will they say this? Need to make a difference between format and precision to get full credit i.e. G11 “rounded” is invalid unless they explain it’s a formatting issue not a precision issue.
	14. (12 pts) Write an Excel formula in cell comics!K5, which can be copied down to cell comics!K16, to determine percent of worth/price for the 1A comic book series with respect to the total worth/price of all of the comic books put together. =J5/SUM(J$5:J$16) Optional $ on column
	15. (8 pts) Given the result values just determined in the above problem (i.e. values in comics!K5:K16), what is the best type of chart to represent this information? PIE… all or nothing points
	16. (20 pts) You have been saving since 1996 to open your own comic book store. You put away an average of $150 per month at an annual percentage rate of 10% compounded quarterly. Write an Excel formula in cell collect!O23 (not shown) to determine how much money you will have to start up your own store today. NOTE: Check the UNITS worksheet for some important information and remember to use cell references whenever possible. *=FV(10%/4,(2011-1996)4,-1503,0,0)* No $ allowed 2011 = units!A1, 4 = units!A2, 3 = units!A3 OK if didn’t put units! worksheet since unclear exactly where the formula is being entered

Points Deducted PAGE2 = ___________ Score ____________ / 250