Monday, 19 November 2018

Excel if statements with an in between range

When marking, I try to automate as much as possible by using marking rubrics in Excel supported by formulas. All markers need do is enter an "X" in the appropriate box and the mark will pop up. It makes marking faster, more consistent and prevents addition errors.

When marking oral presentations recently, I suddenly realised that I had a mark that was not automated, but which could be.

One of the learning outcomes on my course is that students evaluate each other. Not only do they need to attend and evaluate all of their supervisor's supervisee presentations, they also have to provide quality feedback. While the quality of feedback could not be automated, the number of presentations sat through and the number fed back on could be.

For that, I needed to know two numbers: the number of students in each supervisor's group (easy) and the number of presentations each supervisee has fed back on (also easy: straight out of SurveyMonkey, where we collect the student feedback).

I decided that all we had to do was to enter those two raw marks into two unprotected Excel cells which appeared off the print area of the marking sheet, then create a formula for Excel to calculate a percentage of completion based on the numbers entered. For example, if the student was supposed to attend 15 presentations, and provided feedback on only 10, then they would just scrape a B grade for the amount of feedback they provided.

However, to do this, then I needed an Excel "if statement" which would only return an "X" if the mark fell between an A- and a B grade. An "if statement" wouldn't do that. I knew I needed an "and statement" to go with the "if statement", but had never done a combination "if" and "and". After some searching, I found what I was looking for, and the resulting formula was:
=IF(AND($L53<0 .75, $L53>0.65), "X", "")
The formula works like a charm :-)


No comments :

Post a Comment

Thanks for your feedback. The elves will post it shortly.