# I need to test if a formula returns the correct result if given the correct input values.

#### Indystick

##### Board Regular
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

 A B 1 1 Seed Value 2 2 Formula in A2: =A1*2 3 4 Formula in A3: =A2*2 4 8 Formula in A4: =A3*2

<tbody>
</tbody>

 A B C D E 5 1 Formula in A5: =A1 Score Test Formula in Col D 6 2 Formula in A6: =A5*2 1 =IF(A6=A2,1,0) 7 4 Formula in A7: =A6*2 1 =IF(A7=A3,1,0) 8 8 Formula in A8: =A7*2 1 =IF(A8=A4,1,0)

<tbody>
</tbody>

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
 A B 1 1 Seed Value 2 1 Formula in A2: =A1/A2 3 2 Formula in A3: =A2*2 4 4 Formula in A4: =A3*2

<tbody>
</tbody>

Scorecard
 A B C D 5 1 Formula: =A1 Score Test Formula in Col D 6 2 Formula in A6: =A5*2 0 =IF(A6=A2,1,0) 7 4 Formula in A7: =A6*2 0 =IF(A7=A3,1,0) 8 8 Formula in A8: =A7*2 0 =IF(A8=A4,1,0)

<tbody>
</tbody>

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.

1,081,574
Messages
5,359,704
Members
400,545
Latest member
Damntheman30

### This Week's Hot Topics

• VBA (Userform)
Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
• List box that changes fill color
Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
• Remove duplicates and retain one. Cross-linked cases
Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
• VBA Copy and Paste With Duplicates
Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
• Macro
is it possible for a macro to run if the active cell value is different to the value above it
• IF DATE and TIME
I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...