Earlier in this chapter, you added a formula to a worksheet to calculate a car payment. You can also add any formula that you write yourself. Adding formulas to the grade book worksheet increases your productivity by doing the tedious grade averaging for you. The formulas, once in place, automatically calculate the grades as you type the scores. The following section teaches you how to add formulas to worksheets.

ORDER OF OPERATIONS IN EXCEL 2000

There are many formulas 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 2000 starts calculating from the left to the right according to the following order of operations:

For Reference, the Comparison Operators Are:

= Equal to

< Less than

<= Equal to or less than

> Greater than

>= Equal to or greater than

<> Not equal to

ADDING FORMULAS TO YOUR GRADE BOOK WORKSHEET

Adding formulas to your grade book worksheet allows Excel 2000 to do the most difficult part of assigning student grades. You can add formulas that add the point totals, calculate percentages, and assign letter grades. It is all done with simple mathematical formulas.

In order to see the formula work, you must have numbers in the cells representing student points earned on tests, quizzes, and assignments.

ADD A FORMULA FOR TOTALS

1. Open the Grade Book worksheet if it is not already open. First you want to get a total for the points earned.

2. Click cell S6 and type total points.

3. Click cell S7.

4. Click the Edit Formula button (the = sign).

5. Click the Name Box drop-down list and then click SUM.

6. In the Number 1 box, type D7:R7 (the colon indicates a series).

7. Click OK.

Notice that the formula itself shows in the formula bar when the cell is selected. To modify or change the formula, simply click in the formula bar, make the changes, and then press ENTER.

8. You have the total for the points possible, but you need the sum for the students’ columns. You could type the formulas in cells D13 through H13. But that would take some time and you might make some mistakes. Excel 2000 can make it very easy for you.

To fill all of the other cells, select cell S7, place the cursor on the handle in the lower-right corner of the cell, and drag to the last cell in the row. Excel 2000 not only copies the formula into each cell, but also adjusts the formula for each column so that it is correct for that column of numbers.

ADD A FORMULA FOR PERCENTAGES

1. Click cell T6 and type percentage.

2. Click cell T7. You need a formula here that divides the number of points earned by the number of possible points. Then you'll convert it to a percentage.

3. Type =S7/$S$7 (the cell with the total divided by the constant total in cell S7).

4. Press ENTER. The number 1 appears in cell S7.

5. To change the number to a percentage, click cell T7 and then on the Format menu, click Cells.

6. Click the Number tab. In the Category box, click Percentage.

7. In the Decimal Places box, type 1.

8. Click OK. You should now see 100.0% in cell T7.

9. Now, just as you copied the formulas earlier, you drag the fill handle to copy the formula in T7 to the rest of the column. Because you used $S$7 instead of S7, the number does not adjust to the total in the column in which you are working. To find out more about constants, ask the Office Assistant.

10. Save your work.

CREATING LETTER GRADES FROM PERCENTAGES

Now that you have calculated averages, you can easily figure letter grades from scores using Excel 2000. For this type of formula, Excel 2000 compares data using a formula that evaluates the data in relation to a table that you create. The formula interprets the range of data and assigns the value, or letter grade, you have associated with that number. So, you will have a formula that looks up the value you have assigned, and places it in the cell you want. This is called a lookup table. You will first need to create the table that the formula looks at, then assign the formula to the letter grade cells.

Create Letter Grades From Percentages

1. Click cell X7, or any cell outside the range of your grades, and then type Percentage.

2. Click cell Y7, or the cell next to the one you just typed in, and then type Letter Grade.

3. In cell X8, type 0. The range for the lookup table should always start with the lowest number.

4. In cell Y8, type F. This represents the letter grade associated with values greater than zero. When you add the next value, it will become the lower cutoff for the next letter grade.

5. In cell X9, type .599. Any value greater than this will be assigned the next letter grade.

6. In cell Y9, type D.

7. In cell X10, type .699 and then type C in cell Y10. In cell X11, type .799 and then type B in cell Y11. In cell X12 type .899 and then type A in cell Y12. This completes your lookup table. Continue to add the formula to your grade sheet that calculates the letter grade for each percentage.

8. Click cell U6 and type Grade.

9. Click cell U7 and then click Edit Formula (the = sign).

10. Click the Name Box drop-down list and then select LOOKUP. If LOOKUP is not available, click More Functions and then choose LOOKUP from the Function Name list.

11. In the Select Arguments dialog box, select lookup_value,lookup_vector,
result_vector and then click OK. Use this type of formula when you have more than one column in your lookup table. The LOOKUP formula window appears with the cursor in the Lookup_value field.

12. Click cell T7. This assigns the percentage earned as the value compared to the lookup table.

13. Click in the Lookup_vector field and then drag across cells X8 through X12. This assigns your grade cutoffs to be compared to the percentage in cell T7.

14. Click in the Result_vector field and then drag across cells Y8 through Y12. This assigns the letter grade to the corresponding percentages and will be the result found in cell T7.

15. Click OK. The letter “A” should now be in cell U7.

16. In order to keep the cells in the lookup table as constants for the rest of the column, you need to add “$” before each of the values in the formula for the lookup table. When you are done the formula should look as follows:

=LOOKUP(T7,$X$11,$X$15,$Y$11,$Y$15)

17. Click the fill handle in cell U7 and drag to the bottom of the column. The assigned letter grades appear in each of the cells.

18. Save your work.