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


Board Regular
Mar 2, 2018
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

11Seed Value
22Formula in A2: =A1*2
34Formula in A3: =A2*2
48Formula in A4: =A3*2


Correct Answers
51Formula in A5: =A1ScoreTest Formula in Col D
62Formula in A6: =A5*21=IF(A6=A2,1,0)
74Formula in A7: =A6*21=IF(A7=A3,1,0)
88Formula in A8: =A7*21=IF(A8=A4,1,0)


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
11Seed Value
21Formula in A2: =A1/A2
32Formula in A3: =A2*2
44Formula in A4: =A3*2


51Formula: =A1ScoreTest Formula in Col D
62Formula in A6: =A5*20=IF(A6=A2,1,0)
74Formula in A7: =A6*20=IF(A7=A3,1,0)
88Formula in A8: =A7*20=IF(A8=A4,1,0)


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.


Forum statistics

Latest member

Some videos you may like

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...