With Excel 97 you will:

• Practice using the Sort function.
• Create a one-input data table.
• Hide columns.
• Calculate grades with mathematical formulas.
• Create a budget for a school function.
• Explore Excel 97 functions to calculate grades.
• Practice Roman numeral conversions.

You can create worksheets to record and calculate grades using Excel 97. The power of Excel 97 is great. Not only can Excel 97 calculate student grades, weighted scores, and program-planning budgets, but it also can maintain the financial records of an entire school. For this lesson, you will explore some basic functions that you can use in your classroom.

Recording names and addresses of your students can be a full-time job. Just as soon as you have everyone in the correct order, you have a new student whose last name starts with the letter C. With Excel 97, sorting the names by last name, by the enrollment date, or even by birthday is quick and easy.

There are many details to track in a classroom, such as student data, grades, project progress, student participation, homework, and other completed assignments. Using Excel 97, you can track all this information for each student, and then organize it according to importance or category.

Using Excel 97, you and your students can learn the costs associated with making payments on purchases. You can easily calculate monthly payments based on interest and balance due on purchases. In the next exercise, you can calculate monthly payments based on interest and total cost of a car, showing how the loan term and interest rates affect monthly payments.

You have just discovered one easy formula in Excel 97. There are many others that help you calculate or analyze everything from finances to statistical sampling plans. When you combine several mathematical steps in a formula, they are performed in a specific order. Excel 97 will start calculating from the left to the right according to the following order of operations:

• () Parentheses
• - Negative number if used with one operand
• % Percentage
• ^ Exponentiation
• * and / Multiplication and division
• + and - Addition and subtraction
• & Connects two text values to produce one
• continuous text value
• =,<,<=, >, >=, <> Comparison operators

For reference, the Comparison operators are:

• = Equal to
• < Less than
• <= Equal to or less than
• > Greater than
• >= Equal to or greater than
• <> SNot equal to

Using Excel 97 to maintain all of your student records in one file is handy but sometimes awkward. Hiding columns is an easy technique to “fold” away columns you may need, but not need to see for a particular process such as recording test scores and grades. You will find this technique very useful in maintaining complete records in one file.

1. Open the Student List workbook.
2. Click the column C header to select the column.
3. On the Format menu, position the pointer on Column, and click Hide.
4. To unhide your columns, on the Format menu, position the pointer on Column, and click Unhide.
5. Close the workbook without saving.

When all tests are equal, you can easily add the raw data, calculate the average, and determine the grade. Assume that you are grading on a specific range of points for each letter grade. The scale is 90–100=A, 80–89=B, 70–79=C, and 60–69=D. Any score less than 60 is an F.

In this exercise you have given your students equal weekly tests over two months and want to calculate their average grades.

Once you have created a formula, you can quickly and easily copy it into adjacent cells to calculate student records that contain the same type of data.

1. Using the worksheet from the previous lesson, click the L4 cell to select it.
2. Click and drag the fill handle at the lower-right corner of the cell to the bottom of the column, and release the pointer.
3. Save your file with the name Student List.

Now that you have recorded test scores and calculated averages, you can easily figure letter grades from numbered scores using Excel 97.

For this type of formula, Excel 97 compares data with a formula that evaluates the data to a logical value of true (1) or false (0). Using Excel 97 to calculate grades is quick and easy. The formula interprets a range of data such as >89 (which means 90 or more) and assigns a letter grade if the data meets the criteria (score is >89—true or false). Therefore, you have a conditional calculation commonly known as an If-Then statement for equally weighted tests.

1. Open the file named Student List that was saved from the previous exercise.
2. Position the pointer in cell M3, type Grade, and then reclick M3.
3. On the Formatting toolbar, click the Bold button to make the heading bold.
4. Click cell M4, and enter the following, very carefully: =IF(L3>89,"A",IF(L3>79,"B",IF(L3>69,"C",IF(L3>59,"D","F"))))
5. Click and drag the fill handle in cell M4 to cell M10.
6. Save the workbook.

Sometimes the tests and assignments you give are not equal in importance, making the grading process more complex. Excel 97 can help you analyze these types of test scores.

Calculating mean, median, and standard deviation for each test

1. Continuing in your Student ist workbook, click cell B12, and type Average.
2. Click cell B13, and type Median.
3. Click cell B14, and type Standard Deviation.
4. Click cell D12, and on the Standard toolbar click Paste Function.
5. In the Function category, make sure Statistics is selected, and in the Function type, click Average, and then click OK.
6. If D4:D10 is not in Number1, type it in.
7. Click OK.
8. Click cell D13.
9. On the Insert menu, click Function.
10. In the Function category, click Statistics, and in the Function type, click Median, and then click OK.
11. If D4:D10 is not in Number1, type it in.
12. Click OK.
13. Click cell D14, and type =STDEVP(D4:D10).
14. Select cells D12, D13, and D14.
15. Click and drag the fill handle to column L.
16. Right-click the row header 12, and click Format Cells.
17. In Decimal places, type 1.
18. Adjust the decimal to 2 places for the Standard Deviation.
19. In Decimal places, type 2.
20. Save the workbook.

Having a successful school function often depends on having a budget in place. For this exercise you will create a budget and test to see what happens to the results when you change any of the components.

Creating a budget involves entering the categories and specific items into the worksheet. The first step to creating a budget in Excel 97 is to determine the budget categories and enter them in the worksheet.

1. Open a new workbook, and save it with the name Budget.
2. Click cell A2, type the title Budget: Awards Dinner, click Budget, and then, on the Formatting toolbar, click the Bold button.
3. Increase the width of column A to 23.
4. Click Budget, drag it to cell D2, and then click the Merge and Center button.
5. Click cell B3, and type Items; click cell C3, and type Budget; and in cell D3, type Actual.
6. Click cell A4, and type Supplies; click cell B4, and type Paper Products; and then click cell B5, and type Decorations.
7. Double-click between columns B and C to fit the text to the cell.
8. Click cell A7, and type Food; click cell B7, and type Meals; and click cell B8, and type Beverages.
9. Click cell A10, and type Awards; click cell B10, and type Certificates.
10. Click cell A13, and type Miscellaneous; and then click cell B13, and type Custodial.
11. Click in cell A16, and type Totals; on the Formatting toolbar, click the Align Right button.
12. Click cell B3, drag it to cell D3, and then click the Bold button on the Formatting toolbar.
13. Click cell A4, drag it to cell A16, and then click the Bold button on the Formatting toolbar.

The next step in preparing a budget for the Awards Dinner is to determine budget amounts and place them with formulas in the worksheet.

1. In the Budget workbook from the previous lesson, click cell C4 and type 50.00.
2. Click cell C5, and type 25.00.
3. Click cell C7, and type 250.00.
4. Click cell C8, and type 30.00.
5. Click cell C10, and type 75.00.
6. Click cell C13, and type 65.00.
7. Click cell C4, drag it to cell C16, and then click Currency Style ($).
8. Click cell C16, click the Edit Formula button (the = sign), and on the Formula bar, click Sum.
9. In Sum Number1, type C4:C13, and click OK.

The final step in using Excel 97 to budget the Awards Dinner is to record the actual amounts spent. Using Excel 97 to budget an event helps track current expenses and makes it easy to plan future events using the same budget process. As you receive and pay the invoices for your Awards Dinner, enter the values into your budget worksheet.

1. In the Budget workbook from the previous lesson, click cell D4, and type 43.00.
2. Click cell D5, and type 30.00.
3. Click cell D7, and type 238.00.
4. Click cell D8, and type 27.00.
5. Click cell D10, and type 60.00.
6. Click cell D13, and type 60.00.

7. Click cell D4, drag it to cell D16, and then click the Currency Style button ($).
8. Click cell C16, drag it to cell D17, and then press enter.
9. Save the workbook.

As you become more comfortable with worksheets, you will discover many applications for them. Using Excel 97 you can easily and quickly maintain student records. Students can reinforce basic mathematical processes using Excel 97 to produce charts and tables. Furthermore, by adding well-designed charts and tables to reports, students can enhance both their understanding of the content and their presentation of the information. In addition, you can also check student math problems.

Converting Roman numerals is an exercise in logic. Explain to your students that if we were still using Roman numerals, the year 2000 would be easy to represent with the letters MM.

1. Open a new workbook.
2. In cell A1, type Year.
3. Tab to cell B1, type Equivalent, and then click enter.
4. Make cells A1 and B1 bold, and then double-click between columns B and C.
5. Click cell A2, type 1984, and then press enter.
6. In cell A3, type 1985, and then press enter.
7. Select cells A2 and A3, and drag the fill handle to cell A19.
8. Click cell B2.
9. On the Standard toolbar, click the Paste Function button.
10. Click Math & Trig, and in the Function name, click Roman.
11. Click cell A2.
    – or –
    Type A2 in the Number window.
12. Type 0 in the Form window (for Classical Roman numerals), and then click OK.
13. Select cell B2, and drag the fill handle to cell B19.
14. Close the workbook without saving changes.

In this chapter you explored and practiced:

• Using the Sort function.
• Creating a one-input data table.
• Hiding columns and showing hidden columns.
• Calculating grades with mathematical formulas.
• Applying Excel 97 formulas to calculate grades for equally weighted

• Analyzing test scores.
• Creating a budget for a school function.
• Converting Arabic numerals to Roman numerals.