Grading With Custom Formulas

Instead of a fixed answer key value, you can instead have Flubaroo use a formula as an answer key. This formula will be copied into the grades sheet upon grading and evaluated there.

There are 2 things to know when using this approach.

1. Formulas entered in the answer key row in the Student Submissions sheet must start with "%=" rather than "=".

2. Use the token FLB_RESPONSE as a placeholder for the actual student's response, which will be replaced/expanded when the formula is copied into the Grades sheet for that student's graded submission. There are six additioinal tokens to point to the three columns before and after the student response column. FLB_RESPONSE_PREV1, FLB_RESPONSE_PREV2, FLB_RESPONSE_PREV3, FLB_RESPONSE_NEXT1, FLB_RESPONSE_NEXT2 and FLB_RESPONSE_NEXT3.

Here is a simple example that will simply compare the student's response against a known answer key value, assigning it 1 point if correct, and 0 if not:

%=if(FLB_RESPONSE = "Bart", 1, 0)

Upon grading (whether done via the menu, or through autograde), this formula will be expanded and evaluated in the Grades sheet for each graded submission. Here is what the formula above looks like for the very first graded submission in the Grades sheet:

The $J$40 in this example actually points to a hidden cell in the Grades sheet that contains a copy of the student's original submission value. Similarly, the next row down would expand to $J$41, then $J$42, etc.

Entering Formulas into the Grades sheet:

If you prefer, you can enter your formulas directly into the Grades sheet, which makes it easier to test them. To expand the FLB_RESPONSE token in this case, just highlight the cells that contain your formula (one column at a time), and select Expand Custom Formulas from Flubaroo's Advanced menu. Flubaroo will then replace FLB_RESPONSE in all of your formulas with the correct cell reference.

Here are some possible uses for custom formulas:

  • Use a "search" function to match against certain words or phrases in student responses, and determine an appropriate score.

  • Use a vlookup formula to match many possible answers against whatever score you want to assign them.

A bit more advanced:

  • You can reference not only the student's response to the current question (FLB_RESPONSE), but also up to the previous 3 questions. For this, use FLB_RESPONSE_PREV1, FLB_RESPONSE_PREV2 and FLB_RESPONSE_PREV3.

  • Example: Say question 1 asked the student to specify an 'x', question 2 asked for a 'y', and question 3 asked "What is x + y?". For your custom answer key formula to question 3, you could enter:

  • %=IF(EQ(FLB_RESPONSE, FLB_RESPONSE_PREV1 + FLB_RESPONSE_PREV2), 1, 0)

  • Which says: "if the current answer is equal to the sum of the previous 2 answers, assign 1 point. Otherwise assign 0 points."

Video Tutorials:

Our super-star volunteer Joe Schmidt (aka "Flubaroo Joe") was kind enough to put together these demo videos showing what is possible with Custom Formulas in Flubaroo:

Formula used in the Search example.

%=IF(ISERROR(SEARCH("capitalize",FLB_RESPONSE)),FLB_RESPONSE,1)

Formulas used in the Vlookup example. The user must adjust the Lookup table.

%=vlookup(FLB_RESPONSE,Lookup!$D$2:$E$100,2,FALSE)

Use when answer is required.

%=if(isblank(FLB_RESPONSE),-1,vlookup(FLB_RESPONSE,Lookup!$D$2:$E$100,2,FALSE))

Use when response can be blank.

Consider using the Vlookup in the Answer Cell of the Student submissions.