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):

=IF(B2="P",(IF(M2<70,"fail","pass")),(IF(m2<70,"f",(IF(m2<80,"c",(IF(m2<93,"b","a")))))))>

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:

=IF(AG2<70,"fail",(IF(ag2<79,"minimal",(if(ag2<92.5,"satisfactory","superior")))))

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?