Pass-Fail Course Grade Algorithm

I routinely use a spreadsheet program to calculate my course grades. It’s just easier for me than to use the software supplied by our state which isn’t quite so user friendly. I currently use Microsoft Excel 2003 and the algorithms below are written to work with it.

Variables and Conventions

  • S1 = Attendance Status as either “LOST CREDIT”, “CONCERN”, or “OK”
  • T1 = Appeal Status and Conditions for loss of credit as either empty (blank) or containing written conditions
  • Z1 = Course Grade in the range 0 to 100 with rounding active and 70 being the lowest passing score.
  • LC is short for Lost Credit
  • CR is short for Credit Restored

Algorithm

=IF(S1<>”LOST CREDIT”,IF(Z1<69.5,”FAILING”,”PASSING”),IF(T1=””,IF(Z1>=69.5,”LOST CREDIT PASSING”,”LOST CREDIT FAILING”),IF(Z1<69.5,”CREDIT RESTORED FAILING”,”CREDIT RESTORED PASSING”)))

Now, since the only practical categories are either Passing, Failing, and Lost Credit Passing (LC Passing) and the other results fall within these categories, we can simplify the algorithm like so:

=IF(S1<>”LOST CREDIT”,IF(Z1<69.5,”FAILING”,”PASSING”),IF(T1=””,IF(Z1>=69.5,”LC PASSING”,”FAILING”),IF(Z1<69.5,”FAILING”,”PASSING”)))

This has an additional benefit in Microsoft Excel. Having only 3 categories allows Conditional Formatting to be applied to emphasize the contrast. I changed the background color on cells in these categories to be either green, yellow, or red depending on the category.

Invalid Formula Error

If you copy and paste the above algorithm into Excel, you may get an invalid formula error even if you correctly swap out the variables (Z1, T1, etc.) for cell references. I am assuming that the version above gets pasted in as smart quotes and excel only recognizes straight quotes. The problem was fixed once I typed over the pasted double quotes using the keyboard.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s