CSE200 AU10 KREEVES MIDTERM SEAT# ____________

NAME _______________________________________________ Lecture: MW 8:30-10:18am

Lab section (check one): _______ R 9:30-11:18am ________ R 11:30-1:18pm

Instructions:

· Filling out your name, correct seat# and lab section on both answer sheets is worth 4 points.

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

Worksheets on other side ==========è

Name: ______________________________ Lab: _______ 9:30am _______ 11:30am Seat# _______

PROBLEM DESCRIPTION

You really enjoy studying photography and would like to learn more about different types of cameras so have collected information about the cameras some of your friends own. You have decided to computerize some of the data you have collected (just a portion of it) and have set up the following worksheets:

PHOTO - The following information is given as input data: A4,H17, I2:L2 and O2:P2. In the first range, the names of the owners of the camera, the camera company, the cost, the number of megapixels (the higher the number of megapixels, the better the resolution – or sharpness – of the picture), then additional optional accessories. For the additional accessories, a Boolean value of TRUE is given if an accessory is requested and FALSE if it is not. The values in the second range designate the cost of each different type of accessory. The column information in D3:H3 will always the in the same order as the column information in I3:L3. Also, remember that you should be able to include additional accessories and your solutions should still work. The value in O2 and P2 designate the difference between the length option and the width option. This information will be further reviewed in the appropriate problem.

COMPANY – All of the possible different camera companies are given in column A. This is the only input data.

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

The defined range is COMPANY!$A$2:$B$9 and the name is COMPERC

COST – This worksheet will be explained in the problem that uses it.

RESOLUTION – This worksheet has two sets of iput data: B2:F4 for camera resolution values and H8:J10 for monitor resolution values. Remember that the greater the number of pixels per inch, the greater the resolution. By the way, in the PHOTO worksheet, there is a column designating megapixels as well (hint hint). In the first set of input data, each value in B2:F2 represents a range of values with the first one being 0 to less than 5 with a length of 1280 pixels and a width of 960 pixels; the second value designates a range from 5 to less than 8 with a length of 1600 pixels and a width of 1200 pixels; etc. The second range(the monitor resolution values) specifies the length and width in inches for each monitor (see cells A8:A10) as well as the number of pixels per inch.

SCRATCH:

Name: ______________________________ Lab: _______ 9:30am _______ 11:30am Seat# _______

Answer Sheet CSE 200 – Midterm AU10 – MW 8:30-10:18am KReeves

PAGE 1

(-)

Answer:

1. (16 pts) Write an Excel formula in cell company!B2, which can be copied down to company!B9, to determine what percent of cameras on the list are canon cameras.

=COUNTIF(photo!B$4:B$17,A2)/COUNT(photo!C$4:C$17) can also use column D instead of C

Optional $ on column

2. (10 pts) I wrote the formula =ROUND(B2,2) in cell company!C2 and copied it down to cell company!C9. NOTE: do not use more room than given on this problem… you don’t need it!

PART A: Why don’t the values in column B add up to 100%?

The actual/precise values do add up to 100%; the formatted don’t.

PART B: Why don’t the values in column C add up to 100%?

Because the ROUND function changes the precision of a value.

3. (12 pts) Write an Excel formula in cell photo!E18 to determine if all of the photographers requested none of the accessories. NOTE: The answer is FALSE for the given data.

=NOT(OR(E4:H17)

=COUNTIF(E4:H17,TRUE)=0

=COUNTIF(E4:H17,FALSE)=COUNT(C4:C17)*COUNT(I2:L2) can also use column D instead of C

No $ allowed

4. (15 pts) Assume that the order of the accessories listed in photo!E3:H3 will always be in the same order as the list of accessories in photo!I3:L3. Write an Excel formula in cell photo!I4, which can be copied down and across to cell photo! L17, to determine the cost of the memory accessory for Jacob based on whether or not Jacob is requesting the accessory as designated in cell photo!E4.

=IF(E4,I$2,0) can say E4=TRUE for first argument

=IF(NOT(E4),0,I$2) can say E4=FALSE for first argument

No extra/optional $ allowed

5. (12 pts) Write an Excel formula in cell photo!M4, which can be copied down to cell photo!M17, to determine the total cost of jacob’s camera including the camera as well as accessories.

=C4+SUM(I4:L4) ok if C4 cell inside the sum function with a comma separating it from the range

Optional $ on column

Part A: What type of chart is pictured here?

line

Part B: What is the data range used to create this chart?

photo!a3:a17,photo!m3:m17

6. (10 pts)

7. (12 pts) Write an Excel formula in cell cost!B2, which can be copied down to cell photo!B6, to determine the minimum total cost value for a camera on the list given on the photo worksheet. Notice that the subsequent values are the second lowest value, followed by the third lowest value, etc.

=SMALL(photo!M$4:M$17,A2)

Optional $ on column

8. (12 pts) Write an Excel formula in cell photo!N4, which can be copied down to cell photo!N17, to determine if jacob’s total camera cost is less than the average total camera cost.

=M4<AVERAGE(M$4:M$17)

Optional $ on column

Points Deducted PAGE 1 = __________________

Name: ______________________________ Lab: _______ 9:30am _______ 11:30am Seat# _______

Answer Sheet CSE 200 – Midterm AU10 – MW 8:30-10:18am KReeves

PAGE 2

(-)	Answer:
	9. (24 pts) Based on the number of megapixels given in column D on the photo worksheet, write an Excel formula in cell photo!O4, which can be copied down and across to cell photo!P17, to determine the number of pixels for the length of a photo developed using the canon camera that jacob is using. Notice that the option number (cells photo!O2:P2) helps to designate whether to choose the length or the width value in # of pixels from the resolution worksheet. =HLOOKUP($D4,resolution!$B$2:$F$4,O$2+1) Optional TRUE value for the 4^th argument No extra//optional $ allowed
	10. (24 pts) Write an Excel formula in cell resolution!B8, which can be copied down and across to cell resolution!F10, to determine if the monitor area resolution (LxW) is better than the camera area resolution (LxW). NOTE: We cannot help you obtain the resulting data as this is “that” kind of problem. HINT: you only need the data given on the resolution worksheet to solve the problem. =$H8$I8$J8^2>=B$3B$4 =B$3/$J8B$4/$J8<=$H8*$I8 No extra/optional $ allowed
	11. (6 pts) Based on the fact that the greater the number of pixels per inch, the greater the resolution, would you use a what-if analysis or a goal seek to determine how to obtain a better resolution? WHAT-IF analysis
	12. (20 pts) Write an Excel formula based on the data in the resolution worksheet (specifically from question #10) to determine if monitor 3 is the only monitor where ALL of the resolutions are better than the camera resolutions. The answer to the problem based on the current data is TRUE. HINT: this is not like your typical “only” type of problem. =AND(B10:F10,NOT(AND(B8:F8)),NOT(AND(B9:F9))) No $ allowed NOT(AND()) is equivalent to determining if at least one is false i.e. =COUNTIF(B8:F8,FALSE)>=1 Also can be >0 for right of COUNTIF
	13. (32 pts) Based on he percent of camera types on the company worksheet as determined in a previous problem, write an Excel formula in cell photo!Q4, which can be copied down to cell photo!Q17, to determine how popular jacob’s canon camera is based on the following: Ø A camera is “very” popular if the camera type is greater than or equal to 30% Ø A camera is “not really” popular if the camera type is less than 10% Ø Otherwise, the camera is “kinda” popular REMINDER: Be sure to use the named range defined on the company worksheet. =IF(VLOOKUP(B4,comperc,2,FALSE)>=30%,"very",IF(VLOOKUP(B4,comperc,2,FALSE)<10%,"not really","kinda")) Optional $ on column
	14. (15 pts) Write an Excel formula in cell photo!R4, which can be copied down to cell R17, to determine (true or false) if jacob’s camera is one of your favorite cameras. A camera is your favorite if it is a canon or sony company camera and if the total cost is less than $500. =AND(OR(B4="canon",B4="sony"),M4<500) Optional $ on column
	15. (20 pts) You have decided to open your own camera shop. In order to do so, you have taken a loan for $100,000 at 8% per year compounded monthly to start your business. You have estimated that you can afford a monthly payment of $1500. Write an Excel formula to determine (true or false) if you can pay off the loan in less than 8 years. =NPER(8%/12,-1500,100000)/12<8 =NPER(8%/12,-1500,100000)<8*12 No $ signs; no extra commas; can put .08 instead of 8%
	16. (6 pts) Define what values the 5th argument can be on a financial function and explain each. 0 – end of the period (default) 1 – beginning of the period

Points Deducted PAGE2 = ___________ Score ____________ / 250