I am a college professor who has students complete applied exams in Excel. I have created a "scorecard" that compares the output of formula in a cell to what I know to be the correct output, and if they're equal, assigns them points. For example, imagine the following simplified example:
Student's Work
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]Seed Value[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[TD]Formula in A2: =A1*2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]4[/TD]
[TD]Formula in A3: =A2*2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]8[/TD]
[TD]Formula in A4: =A3*2[/TD]
[/TR]
</tbody>[/TABLE]
Correct Answers
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1[/TD]
[TD]Formula in A5: =A1[/TD]
[TD]Score[/TD]
[TD]Test Formula in Col D[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2[/TD]
[TD]Formula in A6: =A5*2[/TD]
[TD]1[/TD]
[TD]=IF(A6=A2,1,0)[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]4[/TD]
[TD]Formula in A7: =A6*2[/TD]
[TD]1[/TD]
[TD]=IF(A7=A3,1,0)[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]8[/TD]
[TD]Formula in A8: =A7*2[/TD]
[TD]1[/TD]
[TD]=IF(A8=A4,1,0)[/TD]
[/TR]
</tbody>[/TABLE]
This works fine in all cases for testing the value in cell A2 because A2 is a constant and the input for the student's formula and the scorecard comparison formula will always be the same.
However, problems emerge in the scorecard for evaluating cells A3:A4, because they are dependent on the formula in cell A2 returning the correct result. The reason this is a problem is because the formulas in A3:A4 could be the correct formulas (which is what I care about from a testing perspective), but return the wrong values because the formula in cell A2 is wrong:
Student's Work
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]Seed Value[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]Formula in A2: =A1/A2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]Formula in A3: =A2*2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4[/TD]
[TD]Formula in A4: =A3*2[/TD]
[/TR]
</tbody>[/TABLE]
Scorecard
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1[/TD]
[TD]Formula: =A1[/TD]
[TD]Score[/TD]
[TD]Test Formula in Col D[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2[/TD]
[TD]Formula in A6: =A5*2[/TD]
[TD]0[/TD]
[TD]=IF(A6=A2,1,0)[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]4[/TD]
[TD]Formula in A7: =A6*2[/TD]
[TD]0[/TD]
[TD]=IF(A7=A3,1,0)[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]8[/TD]
[TD]Formula in A8: =A7*2[/TD]
[TD]0[/TD]
[TD]=IF(A8=A4,1,0)[/TD]
[/TR]
</tbody>[/TABLE]
Even though the formulas in cells A3 and A4 are correct, they return the wrong value because the precedent in A2 is wrong.
What I would like to do is, first test if the values in the two cells are equal (e.g., A6=A2), and if so assign points; but if they're not equal, then compare the results of the student's formula if given the right inputs with the correct answer. In the second example above, I would replace the cell reference A2 in the formula residing in cell A3 with the cell reference A6 and evaluate, then compare to the correct answer.
So, long story short, what I need is a way either via formula/function/VBA of extracting the students formula from a cell, replacing the cell references with corresponding cell references in the comparison table and calculating the result. I don't know if this is even possible, but if anyone has an idea on how this nut might be cracked, it's the folks here.
Student's Work
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]Seed Value[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[TD]Formula in A2: =A1*2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]4[/TD]
[TD]Formula in A3: =A2*2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]8[/TD]
[TD]Formula in A4: =A3*2[/TD]
[/TR]
</tbody>[/TABLE]
Correct Answers
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1[/TD]
[TD]Formula in A5: =A1[/TD]
[TD]Score[/TD]
[TD]Test Formula in Col D[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2[/TD]
[TD]Formula in A6: =A5*2[/TD]
[TD]1[/TD]
[TD]=IF(A6=A2,1,0)[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]4[/TD]
[TD]Formula in A7: =A6*2[/TD]
[TD]1[/TD]
[TD]=IF(A7=A3,1,0)[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]8[/TD]
[TD]Formula in A8: =A7*2[/TD]
[TD]1[/TD]
[TD]=IF(A8=A4,1,0)[/TD]
[/TR]
</tbody>[/TABLE]
This works fine in all cases for testing the value in cell A2 because A2 is a constant and the input for the student's formula and the scorecard comparison formula will always be the same.
However, problems emerge in the scorecard for evaluating cells A3:A4, because they are dependent on the formula in cell A2 returning the correct result. The reason this is a problem is because the formulas in A3:A4 could be the correct formulas (which is what I care about from a testing perspective), but return the wrong values because the formula in cell A2 is wrong:
Student's Work
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]Seed Value[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]Formula in A2: =A1/A2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]Formula in A3: =A2*2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4[/TD]
[TD]Formula in A4: =A3*2[/TD]
[/TR]
</tbody>[/TABLE]
Scorecard
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1[/TD]
[TD]Formula: =A1[/TD]
[TD]Score[/TD]
[TD]Test Formula in Col D[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2[/TD]
[TD]Formula in A6: =A5*2[/TD]
[TD]0[/TD]
[TD]=IF(A6=A2,1,0)[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]4[/TD]
[TD]Formula in A7: =A6*2[/TD]
[TD]0[/TD]
[TD]=IF(A7=A3,1,0)[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]8[/TD]
[TD]Formula in A8: =A7*2[/TD]
[TD]0[/TD]
[TD]=IF(A8=A4,1,0)[/TD]
[/TR]
</tbody>[/TABLE]
Even though the formulas in cells A3 and A4 are correct, they return the wrong value because the precedent in A2 is wrong.
What I would like to do is, first test if the values in the two cells are equal (e.g., A6=A2), and if so assign points; but if they're not equal, then compare the results of the student's formula if given the right inputs with the correct answer. In the second example above, I would replace the cell reference A2 in the formula residing in cell A3 with the cell reference A6 and evaluate, then compare to the correct answer.
So, long story short, what I need is a way either via formula/function/VBA of extracting the students formula from a cell, replacing the cell references with corresponding cell references in the comparison table and calculating the result. I don't know if this is even possible, but if anyone has an idea on how this nut might be cracked, it's the folks here.
