Moving Points to Percentages

Time for another mailbag question. Jason sends the following about the non-academic behaviors workbook: I am wondering if you know of a way to account for a high number of absences on the report? Obviously, if a student is not in class, they do not receive any points. In essence, that is added in to the total points for the marking period as a zero. Is there a way to modify the point levels on the report that only accounts for days that the student is present? For example, can you put a percentage of days present to equal to a level 1, 2, 3, or 4?

Of course you can. Excel lives to serve, after all.

Let's start by revisiting the original set up. Here's a sample from the "Work Ethics" page.

For each student there are six weeks (M - F) of scores, each based on a four-point scale. Blank cells indicate that the student was absent---no points were earned.

Now, let's hide some of the columns so we can look at formulas:

The Total is a simple sum of the points for the student. The Level is determined by comparing the Total to a range of possibilities ("1" is for less than 59 points, "2" is between 60 and 79 points...).

Jason is interested in a percentage. We can do this by (1) changing the formula for "Total" to one that represents a percentage of points from when the student was present and (2) changing the cut values for the four Levels.

Here is one way to solve the first step:

We still need to keep the sum, but we want Excel to give us the percentage out of the points available, not including days absent. In this grading period, there were 30 days, each with 4 points possible. This gives us the 120 to start the formula in the denominator. Now, we tell Excel to count the number of blank cells in that range, multiply that by the four possible points, and subtract it from 120. We can also tell Excel to format the cell as a percentage.

For the second piece, we only need to update the cuts for each Level. Here is one way to do that:

Note that you will need to express each percentage as a decimal---in this example, .3, .45, .6, and .7.

Beyond this, you would also need to recalibrate the report to use the percentages, as opposed to a raw total of points. But we'll stop here for now.

Need to make other adaptations to the workbooks? Drop me a line and let me know how to help.