In technical problem solving, spreadsheets provide much more than a display and calculating medium. The result of solving a problem is a stored model of the solution. The model contains a solution procedure, which allows the solution of similar problems by little more that changing the input or "GIVEN" data.

Working in Workbooks

Microsoft EXCEL 7, 95, and 97 are similar and the diagrams and step by step instructions will apply to each. EXCEL 4 is a single sheet version but has most of the capabilities of the newer versions and will do everything required in this course.. The "MENUS", however, are significantly different in that many functions are under different headings than the newer versions. EXCEL 5 has the best tutorials under the Help Menu.

Before you continue, examine the Help capability of your version. Use the Help and tutorials to learn how to open a workbook and save a file. At the bottom of the Tools Menu is an "Options" item. Click this and examine the control you have over the setup. Under the General Tab, you may want to reduce the "Number of sheets in a new workbook" to 3.

There is little value in trying to remember the steps to do everything EXCEL can do or everything you will do in this course. The Help menu will remind you of most things when needed. For the steps that you will do often enough make remembering worthwhile, you will have remembered through repetition.

The work sheet below which calculates the area of circles demonstrates most of the formatting tools used in spreadsheet calculations. The solution shows the appropriate units on each value. The box on the right shows the calculation formulas in the AREA column. The formula uses the cell name, "RADIUS". This makes the formulas more recognizable than the default "= 3.14 * A3^2". The * causes multiplication. The ^ raises the preceding value to the value to the right of the ^.

Create the EXCEL calculation shown below as follows:

• Select cell A1 by clicking it with the mouse or using the keyboard arrow keys to move the box selection.
• Type CIRCLE AREA
• Select cell A2. Type RADIUS
• Select cell B2. Type AREA
• Select cell A3. Type 2.
• Select cell A4. Type 4.
• Select cell A5. Type 7.
• Select cell A6. Type 9.
• Select cell B3. Type = 3.14 *, click cell A3, type ^2. The cell now reads =3.14*A3^2. Hit return and the cell reads 12.56.
• Move the cursor to the lower right corner of B3. The cursor will become a small black cross. Hold down the left mouse button and drag the cross from cell B3 down to cell B6. The calculation is now complete.

• Select cells A2 thru A6. From menu, Insert - Name - Create. Select Top Row. Click OK.

Select cells A3 thru A6. The name RADIUS now shows in the Name Box.

• Select cells B3 thru B6. From menu, Insert - Name - Apply. Select RADIUS. Click OK. The formulas now read =3.14*RADIUS^2. Hit return and the cell shows 12.56.

• Select cells A1 and B1. Click the "Center Across Columns" symbol to center heading.
• Select cells A2 to B6. Click the center alignment symbol.

Adding Units

Select cell A3. Click right mouse button and get: Click Format Cells.

In the Format Cells Number Menu click Custom and click the Code or Type: Box and type 0.00"in" as shown below from EXCEL 7. Earlier EXCEL Format boxes are similar.

• Select cell B3.
• In the Format Cells Number Menu click the Code: box and Type 0.00 "in hold alt key and type 0178 on keypad". 0178 is the character code for a 2 superscript. 0179 is the code for a 3 superscript and 0176 is the code for the degree symbol °. Portable PC s have special strokes to make the number keys act as a keypad. Special characters can also be copied from the Character Map shown below and pasted into the Format code using the [Ctrl V] keystroke.

• Complete formatting the cells either by using the Custom formats you have just added to the Number Format menu or by using the button on the menu bar. To use the , select cell A3, move the cursor to click the button and drag the cursor, which now looks like a brush ,over cells A4-A6.

• Repeat the process for cells B3-B6 and get:

In the example above, a group of cells was given the name "RADIUS". Formulas are much easier to check when the variable have familiar names than when they contain cell references. Names also provide absolute references for values that are used in several formulas on a sheet. Names cannot be repeated on a sheet. They can be repeated on different sheets of the same book if referenced to a sheet. It is safest to use the Create Name option when naming cells. This will automatically reference the name to the current sheet and will correctly format names (e.g. "H1" is not allowed, there already is cell H1, "H1_" is allowed).

Naming Sheets

Formulas in cells using cell references are more difficult to understand than formulas with recognizable cell names [ = 3.14 * A3 ^ 2 vs. = 3.14 * RADIUS ^ 2] . Cell names cannot be repeated on a sheet. Assignments with multiple problems are best done with one problem on a sheet. The sheet name then becomes part of the cell name definition.

In EXCEL 97, the name is typed directly on the Tab.

Note that the Effects box in the lower left provides superscripts and subscripts.

EXCEL has sufficient drawing tools to provide the sketch. Many Microsoft applications include Microsoft Equation, which can write mathematical equations in their customary format. The data entry and calculation are similar to the circle area calculations above.

The drawing tools shown in this section are from EXCEL 7 and 95. EXCEL 97 includes these plus many others and are easier to use. A general rule for formatting is click the right mouse button and see the options offered. A second rule is to double click the item to be edited.

• Select the graphics button on the Menu Bar. This brings up the Drawing Toolbar.

• Select the Rectangle tool and drag a rectangle. (The shaded one on EXCEL 95) This provides a white (or color fill of your choice) background. When the cursor gets over this area, it becomes a drawing cursor. Without the background, the cursor is repeatedly changing function as it moves around the spreadsheet and graphics.
• Select the Oval tool and draw a circle. Holding down the shift key while drawing keeps the circle round. It also keeps rectangles square.
• Select the line tool and draw the three straight lines. Holding the shift key keeps the lines vertical or horizontal.
• In EXCEL 97 select the vertical line then click the double arrow line on the Drawing Toolbar.

EXCEL 97 Toolbar

• In EXCEL 95, double click the vertical line to bring up the Format Object Menu Box and choose the double arrow option from the line Style options.

• In EXCEL 95 double clicking the text box brings up a Format Object box that allows changing text alignment, text font and size, and the text box format including removing the text border. NOTE: ALL FORMAT OBJECT OPTIONS STAY IN EFFECT UNTIL CHANGED.

• Select Object under the Insert Menu item. It will provide a list of the available objects.

• Select Microsoft Equation 1.0, 2.0, or 3.0. It will provide an equation writing tool bar.

• Type D, Pi, and A in three cells and center align

• Select these three cells and the three below them.
• Name the cells, Insert-Name-Create-Top Row.

• Type 6, 3.14, =pi*d^2/4 in the appropriate cells. In EXCEL 97 clicking a named cell inserts the name, not the cell column-row reference, in formulas.

• Format the cells D and A to add the units.
• Select Cell A and the one below it and click the B on the tool bar to format them Bold Type to highlight the answer.

Put each problem on a separate sheet. Name all cells used in calculations. Include a dimensioned sketch similar to that shown in the problem above. Send the solutions to your instructor.

1. A businessman is looking for an office. A Realtor shows him a room 29 feet by 56 feet. What is the area of this room? A = L x W

2. What is the area of a triangle with a base of 22 feet and a height of 11 feet?

3. A triangular piece of metal, 5 inches by 3 inches has a 1.2 inch diameter hole punched through it. What is the area of the remaining metal. ( Area of metal = Area of the triangle - Area of hole) (to draw a filled triangle use the Free Form Tool.. Click for a vertex and let go of the left button. Moving the mouse will cause straight lines between clicks. Holding the shift key will constrain the angle of the lines.)

4. The container shown is made of aluminum. What is its maximum holding capacity? What is the volume of aluminum? V =( Volume of the material = total volume - capacity) (Remember-Each dimension must have a unique name.)