Computer Science 152 – Spring 2001

04/20/01 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, Answer Sheet, and Help Sheets, 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. In Excel, you can put a(n) ________ in a cell. These tell Excel what calculation should be done to determine the value to go in the cell. These are indicated by starting with an = and can include mathematical calculations and/or use of built-in functions.

2. The intersection of a row and column creates a(n) ________. This is the basic unit in Excel. Text, numbers, and formulas can be entered here.

3. In Excel, ________ charts display data as evenly spaced bars. The categories are displayed along the X axis and the values are shown by the height of the bar. This type of chart is good for visualizing the relative magnitude of values for different categories.

4. A(n) ________ reference (or address) is a cell or range reference in a formula whose location does not change when the formula is copied – it will always refer to the same specific cell.

5. A(n) ________ is a named set of input values that you can substitute in a worksheet to see the effects of a possible alternative course of action (or alternative assumptions). This capability is designed to help forecast the outcome of possible actions, and is hence a form of what-if analysis. The capability is forward-looking; the user specifies values for cells that other cell’s formulas use, rather than specifying target results.

6. The ________ capability in Excel allows you to keep the row and/or column headings on screen as you move down or right in the spreadsheet. This allows you to enter data more carefully or more easily tell which data belongs with what heading.

Multiple Choice (4 points each)

7. What is true about copying cells in Excel.

A) Only text and numbers can be copied, never formulas.

B) When formulas are copied, all addresses are automatically adjusted to the new location.

C) Cells must be copied one at a time.

D) Many times, the quickest way to copy is to drag a cell using the fill handle.

8. What is true about addresses used in formulas?

A) Can not refer to an address more than 50 rows away from where it is used.

B) Can refer to a cell in a different sheet of the spreadsheet file.

C) Can include a + symbol to indicate two adjacent cells.

D) Can only refer to cells in the same sheet as where it is being used.

E) None of the Above.

9. When you copy a formula with relative cell references to a cell immediately to the right of it (to the next column over),

A) Both the row references and the column references are changed in the new copy of the formula.

B) The row references change in the new copy of the formula

C) The column references change in the new copy of the formula

D) Neither the row references nor the column references are changed in the new copy of the formula.

Short Answer (points as shown)

(6 points)

10. What are three major advantages of electronic spreadsheet programs such as Excel?

(8 points)

11. What are four ways that you can perform what-if analysis in Excel? Briefly distinguish them from each other (don’t try to explain everything there is about them).

Computer Tasks (points as shown)

(56 points – detailed below)

12. Using Microsoft Excel, create the document and graph shown on the extra pages, as described below. Make sure you save your file as you go – don’t wait until the end! Name it yourlastnameMidterm3.

A) The title is Bold, Spread over 5 columns, “Global Warming and Sea Levels” (2 points)

B) Column Headings (“Year”, “Global Temp”, etc) are bold, and only take up one row of the spreadsheet. (2 points)

C) Years 1900-2000 are text (4 points)

D) Global Temp and Sea Level are numbers, typed as shown (4 points)

E) Mean Temperature and Sea Level must be calculated using formulas. (2 points). Format as shown (1 point).

F) Temperature Difference from Average shows how much each temperature differs from the mean temperature. It MUST be calculated using formulas using absolute reference. (4 points). Format as shown (1 point).

G) Above Ave? shows which years temperatures were above average. It must be calculated using a formula involving IF. A * should be shown if the temperature was above average, otherwise nothing should be shown. (4 points). Format as shown (centered) (1 point).

H) Remember “Mean” row label (1 point)

I) Thick borders are as shown (4 points)

J) “Prepared by “ then your actual name (instead of Your Name) is typed at the bottom (1 point)

K) Put the current date and time, file name and sheet name, page “of” possible pages in the header as shown – using fields so they automatically update. (4 points)

L) After saving the document again, print it (1 point)

M) The scientist is investigating the relationship between the earth’s temperature and the sea level. Create an XY Scatter Graph of Global Temp vs. Sea Level, as shown on the attached page – on its own sheet. Have the main title include your actual last name. Don’t include a legend. (Basic creation, with titles and points is (7 points).

N) Add a polynomial line fit with a cubed term as shown, and show its equation and R-Squared value. (5 points).

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

P) Print the graph (1 point).

Q) Use Goal Seek to determine what the Sea Level would have had to have been in 1910 (changing nothing else) in order for the average sea level to be 5.2. Answer on Test Answer Sheet (and Cancel so change won’t affect spreadsheet). (5 points)

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