Computer Science 152 – Spring 2003

04/16/03 Midterm Exam #3 Test Form A

Name:

Instructions:

Answer all questions except the hands-on computer tasks on the answer sheet provided.

Hand in TEST, and Answer Sheet, each with your name on it.

MULTIPLE CHOICE QUESTIONS MUST BE ANSWERED WITH CAPITAL LETTERS!

Remember to put the letter of your test form on your answer sheet.

Completion (3 points each)

1. A(n) ________ reference (or address) is a cell or range reference in a formula whose location is interpreted by Excel in relation to the position of the cell that contains the formula. If the formula is copied to another cell, then the reference/address in the copy is adjusted to fit the new location.

2. ________ are prewritten calculations that are provided with Excel. They can be used in a formula to produce results more complicated than simple arithmetic. There are over 400 of these provided by Excel, ranging from doing statistical calculation to date and time, to financial capabilities.

3. ________ is a tool in Excel that is used to find the value needed in one cell to attain the result you want in another cell. You specify where the result cell is, where the cell to be adjusted is, and the target value that you want to achieve in the result cell. The result cell must be related to the adjusted cell via formulas so that Excel can do the analysis. Only one cell can be adjusted using this tool.

4. Sometimes, Excel’s ________ capabilities may be useful in finding problems with formulas in spreadsheets. These allow showing graphically what cells are dependent on which other cells (precedents and dependents)

Multiple Choice (4 points each)

5. In Excel, which function is so frequently used that it has its own toolbar button?

A) MAX

B) MIN

C) MEAN

D) SUM

E) All of the above

F) None of the above

6. Which of the following series could be created in adjacent cells by dragging?

A) Mon, Tue, Wed, Thur, Fri

B) Jan, Feb, Mar, Apr

C) 3, 6, 9, 12, 15, 18, 21

D) 3/7, 3/14, 3/21, 3/28

E) All of the above

F) None of the above

7. Which of the following is true about graphs and charts using Excel

A) once you have completed the wizard, changes cannot be made. The wizard must be re-run

B) graph/chart types are limited to line, bar, scatter, and pie

C) if data to be graphed is incorrectly selected, the selection can be changed while the wizard is running

D) all of the above

E) none of the above

8. What is true about sheets in Excel?

A) a formula may refer to a cell in another sheet

B) sheets can be renamed

C) sheets can be re-ordered in the workbook

D) all of the above

E) none of the above

Short Answer (points as shown)

(6 points)

9. When using Excel’s solver capability, how can you help to ensure that the solution obtained by Excel is a reasonable, common sense answer?

(6 points)

10. How is it possible for Excel to do “what-if” analysis? (what makes it possible?)

Computer Tasks (points as shown)

(60 points – detailed below)

11. Using Microsoft Excel, download midterm3todownload.xls from the main course page on my www site, and add to it as described below. Rename the file yourlastnameMidterm3.xls.

a) “Speed up %”, “Difference from Mean,” “Absolute Value”, and “Above Threshold” headings are in one cell each (2 points)

b) “m / s at” should be in one merged cell, centered above 25 C and 1000 C columns (2 points)

c) Speed up % shows the increase in speed from 25 C to 1000 C. Formulas must be used. Formatting should show result as a percentage with one decimal place. (3 points)

d) Means are to be calculated for 25 C, 1000 C, and Speed Up %. Formulas must be used. Format data to have one decimal place. (4 points)

e) Conditionally format the 25 C, 1000 C, and Speed Up % columns so that any values greater than the respective means are colored red (3 points).

f) Difference from mean is the difference between the speed up for a particular gas and the mean speed up. Formulas using absolute (or mixed) addresses MUST be used. (3 points)

g) Absolute Value must be calculated using a formula. There is a built function (fairly obviously named) specifically designed to do this, but this could also be done using IF (3 points)

h) Threshold for Difference value is typed as shown, boxed and colored light blue (3 points).

i) Above Threshold MUST be calculated using formulas. The formula must use an IF and an absolute address for Threshold for Difference. The resulting value should be “Yes” if the Absolute Value is greater than the threshold for difference. Otherwise it should be “No”. This is made use of below. (6 points)

j) Add thick borders where necessary to be as shown (2 points)

k) Number of Gases Above Threshold value is boxed, and must be calculated using a formula (hint: there is a built-in function appropriate for this task; “COUNTIF”) (3 points)

l) Type your actual name (instead of YOUR NAME HERE) at the bottom (1 point)

m) Include headers and footers on the spreadsheet as shown – file and sheet in header, date, time, page, and total number of pages in footer, using fields so they automatically update. (3 points)

n) Ensure that the spreadsheet will print on one page (2 point)

o) After saving the document again, print it (2 points)

p) We would like to know at what threshold would only 3 gases be above the threshold. Do a Goal Seek to answer the following question (answer on answer sheet): “What would the “Threshold for Difference” have to be in order for the Number of Gases Above Threshold to 3?” (4 points)

q) Create an XY Scatter Graph of Gas Speed 25 C vs. 1000 C Temperatures, as shown on the attached page – on its own sheet. Have the main title include your actual last name. (Basic creation, with titles and points is 6 points)

r) Add a linear line fit, and show its equation and R-Squared value. (4 points).

s) Change the background color of the graph to be white (2 points).

t) Print the graph (2 point).

Make sure you save your document to a floppy disk. Hand in the disk and your print outs.