Monday, May 18, 2009

An excuse ... and an Excel tidbit

As blogs go, sometimes there is time to write and sometimes not. I'm in the midst of having finished the semester, getting grading done, have done a number of presentations, and trying to sell and buy a house. It will still be bit before I'm regularly blogging...

UPDATE (2009.05.20): Thanks to Dick and James who contacted me off-blog, I have learned new and wonderful Excel tricks. In particular, what I describe below does work, but it is using the brute force approach. A more elegant method is to use named ranges. Very nice. To see an example, check out this XLS file. I also learned the $cellname trick to refer to an absolute cell. Thanks again to Dick and James!

In the meantime, I'm bragging a bit here for having figured out a neat little Excel trick to help me as I report grades. At our seminary, students have the option of choosing to take a course as Pass/Fail or as a letter grade. What I usually do is assign number values to assignments throughout the semester. Then at the end, I come up with a total score based on the percentage values for each component. What I then need to do is either assign a P or F if they chose pass/fail or assign a letter grade if that is what they want. I figured out how to make Excel assign the correct value in a single step by using this heavily nested IF statement (which is set in cell N2 in my example):


In this statement, B2 is where I've pasted in the Registrar's listing of the students' choice for a letter grade (the value is "L") or pass/fail (the value is "P"). So, the formula first checks if B2 is a "P," and if it is, then it looks at M2 where the total grade score is entered. If M2 is less than 70, then a "Fail" is entered in N2. If that is not the case, then a "Pass" is entered. If B2 does not have a "P," then I know they want a letter grade, and so the last set of nested IFs works through my grading ranges to assign a grade in cell N2. (Note that it starts with looking at less than 70 is an F, if not but less than 80 a C, if not but less than 93 a B, otherwise it is an A.

We also need to have established desired outcomes for our courses and listed how those outcomes will be evaluated. For me, this is a combination of scores of various quizzes, exercises, classroom participation, etc. So, for each desired outcome, I can come up with a number value based on the various assignments and their percentage values. What I also need to do, however, is assign a level of competency: Fail, Minimal, Satisfactory, or Superior. Here's the IF formula I use to run that through:


AG2 is the cell with the score total, and the correct value gets entered in.

I drag/copy these formulas into the neighboring cells, and so I get all the values I want without much more effort. Now I can compose my Descriptive Reports in Microsoft Word and use this spreadsheet as the mail merge source to enter in all the values. In the past I had manually entered the grades and values, but from now on I can use these formulas to speed up the process considerably. You will doubtless have to change the cell names, but I hope this trick can help someone else. For someone who knows Excel well, this is probably pretty basic, but how many seminary teachers have the time to figure out the intricacies of Excel?


  1. Great to see you using Excel to speed up the grading task. The nested IFs are, however, anathema to Excel users. They are difficult to audit if something is wrong. Far better to use a VLOOKUP. I could send an example if you like.

  2. I'm completely self-taught in Excel, and I probably used the blunt-force approach. I have no idea what a VLOOKUP is, but I'd love to see an example. Thanks!

  3. Mark

    In order to keep this from becoming an Excel forum, I will send an example to your school address. Feel free to contact me with any questions. If anyonw else would like to see how this works, just ask.

  4. Wow what a great blogpost sites, i really amazed. Alot of information is available at a single places. This is very informative type of blogspot and hope every one get the benefit of it.

    TExES exam practice