Grading with Formulas

Grading with Formulas

Open Grading Exercise

Using the “Proper” Function

Insert Blank Column after the column of names

In the first cell adjacent to the first name use the Proper function and select the cell immediately to the left [ e.g. =Proper(A2) ] This will change the format of the name to initial capitals

Use auto fill to populate all of the cells adjacent to the names with incorrect formatting

Because this column is a formula, it is not sufficient to delete the first column or just do an ordinary copy/paste of the correctly formatted names

Instead select the newly formatted names and copy them. Then do a Paste Values on top of the incorrectly formatted names

Delete the 2nd column now.

Sorting the names

Select a single cell of one of the names

Use the Sort/A-Z function to sort the list of students alphabetically

Calculations for each student

Complete the Exam totals for each student using the Sum formula.

Next complete the Average Exam score for each student. Be careful not to include the Total in the average calculation.

Calculations for each exam

Complete the formulas for each of the 3 exams at the bottom using the Average formula, the Standard Deviation formula, and the Variance formula. Calculate these using the list of exam scores in each column separately.

Calculating the Letter Grade

This is done using the If function. Depending on the granularity desired for the Letter Grade, the if statement would appear as follows:
if(F2>=90,”A”,if(F2>=80,”B”,if(F2>=70,”C”,if(F2>=60,”D”,”E”))))

Conditional Formatting for failing students

Create a conditional format to highlight students at risk of failure. Select Conditional Format, Less than, using the average score, measure if the average is less than 60, highlight the cell in red.

Creating Charts

Create a chart using the insert tab, by highlighting the desired data. For example, highlight the student names including the column header and also highlight Exam 1, 2, and 3, including the column headers.
Go to the insert tab and click on Insert Chart and select a chart type such as column.

See the new tab selections which show up when the Chart is selected.
See what happens when switching the row/column data.

Split Name Column into Last Name and First Name

Insert a blank column after the column of names

Select the names in the first column and then click on Text to Columns in the Data tab

Select delimited and check the box for comma as the delimiter

Click finish.