How to test if a specified cell contains a formula?

[lovallee]

Hello

Which formula can I use to test if a specified cell contains a formula?

Louis

 

[DBrownBear]

Hi,
I'm using Excel 2007.
In my student quizzes:
When cell A2 contains:

3, the result in cell D2 should be "Need formula".
=3, the result in cell D2 should be "Need formula".
=3^2, (e.g. any student attempt to write a correct formula, the result in cell D2 is from an IF statement that looks to a hidden worksheet and checks to see if it is the right answer: if it is "Correct", if not "Incorrect".
nothing (the cell is empty or blank), the result in cell D2 should be "Need formula".

 

[T. Valko]

Hi,
I'm using Excel 2007.
In my student quizzes:
When cell A2 contains:

3, the result in cell D2 should be "Need formula".
=3, the result in cell D2 should be "Need formula".
=3^2, (e.g. any student attempt to write a correct formula, the result in cell D2 is from an IF statement that looks to a hidden worksheet and checks to see if it is the right answer: if it is "Correct", if not "Incorrect".
nothing (the cell is empty or blank), the result in cell D2 should be "Need formula".

Ok, in the case where the cell contains a valid formula like =3^2 then you'd have to do a lookup to the other sheet.

Here's the formula:

=IF(IsFormula(A2),IF(COUNT(-SUBSTITUTE(GetFormula(A2),"=","")),"Need Formula","Lookup"),"Need Formula")

You'd have to replace the "lookup" argument with the lookup function.

Here's a small sample file that demonstrates this without the lookup (since I have no idea what type of data this uses).

CheckFormula.xlsm 13kb

http://cjoint.com/?BHAqcBe9IGs

Of interest...

I'm not much of a programmer. I'm thinking that a more experienced programmer can probably do this with a single UDF.

 

[DBrownBear]

Biff,
You've been so kind so far, I hesitate to ask any further on this.
The sitiuation is this:
I give a math problem (here's a simple example):
[TABLE="width: 571"]
<COLGROUP><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><COL style="WIDTH: 48pt" span=4 width=64><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" width=85><COL style="WIDTH: 48pt" span=3 width=64><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><TBODY>[TR]
[TD="class: xl65, width: 79, bgcolor: transparent"]Q1.1: 1[/TD]
[TD="class: xl67, width: 192, bgcolor: transparent, colspan: 3"]0.3467 + 0.0675 =[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 74, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 85, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl66, width: 64, bgcolor: #ffffcc"]0.3467[/TD]
[TD="class: xl66, width: 74, bgcolor: #ffffcc"]0.0675[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent, colspan: 8"](Use Excel's ROUND(_,_) function, absolute and relative referencing).[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, colspan: 3"]a. rounded to thousandths[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: #ffffcc"]3[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]

and ask the student to solve it with the given numerical information (e.g. the numbers in red above).
Here they are simply asked to add to given numbers and round them to 3 decimal places, and put the answer in the dashed cell (here, F9).
I've given them the answers on a separate worksheet in the same workbook (sometimes I leave it unhidden, sometimes I hide it). The answer sheet is a copy of the problem worksheet, where I've simply copy/paste_values the answer.
My objective is to teach them simple formulas using Excel.
I have answer column(s) hidden on the problem worksheet that I unhide on grading time.
Before asking your help I had the following "grade" cell formula:
=IF(ISformula(F9),IF(F9-'Q1 Numerical Answers'!F9<>0,"INCORRECT","CORRECT"),"Need Formula")
So, sometimes a student will answer this above problem with the answer, 0.414, or =0.414, which is not doing it with Excel, but rather with handwriting and just entering the answer.

I want them to solve it using Excel, with any valid formula (here that uses addition and the round() function), for instance, =ROUND($J8+$K8,I10)...
I'm not picky about how they solve it, if they use absolute or relative referencing, have extra spaces, parentheses, etc. So if they get the right answer with ANY valid Excel formula that uses built-in functions, I'm glad to grade it "Correct". If it's just numerical answers, or a numerical answer with an equal sign, I grade it "Incorrect".

Thanks for all you've suggested. If you have further thoughts I'd sure appreciate them.

Dave

 

[mikerickson]

Consider the student that answers the round question with =INT(1000*(J8+K8)+0.5)/1000

I can see value in highlight cells with a constant function (like =.414), but programming languages, including Excel formulas, are languages. Like English, one needs to actually read the answer to determine if it is correct.

In my youth, my solutions to the introductory FORTRAN course were always at least one card thinner than everyone else's. I would have hated to have those solutions graded by algorithm.

 

[T. Valko]

Biff,
You've been so kind so far, I hesitate to ask any further on this.
The sitiuation is this:
I give a math problem (here's a simple example):
[TABLE="width: 571"]
<TBODY>[TR]
[TD="class: xl65, width: 79, bgcolor: transparent"]Q1.1: 1
[/TD]
[TD="class: xl67, width: 192, bgcolor: transparent, colspan: 3"]0.3467 + 0.0675 =
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 74, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 85, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl66, width: 64, bgcolor: #ffffcc"]0.3467
[/TD]
[TD="class: xl66, width: 74, bgcolor: #ffffcc"]0.0675
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent, colspan: 8"](Use Excel's ROUND(_,_) function, absolute and relative referencing).
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, colspan: 3"]a. rounded to thousandths
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: #ffffcc"]3
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]

and ask the student to solve it with the given numerical information (e.g. the numbers in red above).
Here they are simply asked to add to given numbers and round them to 3 decimal places, and put the answer in the dashed cell (here, F9).
I've given them the answers on a separate worksheet in the same workbook (sometimes I leave it unhidden, sometimes I hide it). The answer sheet is a copy of the problem worksheet, where I've simply copy/paste_values the answer.
My objective is to teach them simple formulas using Excel.
I have answer column(s) hidden on the problem worksheet that I unhide on grading time.
Before asking your help I had the following "grade" cell formula:
=IF(ISformula(F9),IF(F9-'Q1 Numerical Answers'!F9<>0,"INCORRECT","CORRECT"),"Need Formula")
So, sometimes a student will answer this above problem with the answer, 0.414, or =0.414, which is not doing it with Excel, but rather with handwriting and just entering the answer.

I want them to solve it using Excel, with any valid formula (here that uses addition and the round() function), for instance, =ROUND($J8+$K8,I10)...
I'm not picky about how they solve it, if they use absolute or relative referencing, have extra spaces, parentheses, etc. So if they get the right answer with ANY valid Excel formula that uses built-in functions, I'm glad to grade it "Correct". If it's just numerical answers, or a numerical answer with an equal sign, I grade it "Incorrect".

Thanks for all you've suggested. If you have further thoughts I'd sure appreciate them.

Dave

Ok, I think this will do what you want. Just need to use the correct cell references.

F9 = where the user enters their answer.
X1 = answer key cell

=IF(IsFormula(F9),IF(ISERROR(-SUBSTITUTE(GetFormula(F9),"=","")),IF(F9=X1,"Correct","Incorrect")),"Incorrect")

 

[DodgerDave88]

You can use a VBA UDF (user defined function):

Function IsFormula(cell_ref As Range) 
    IsFormula = cell_ref.HasFormula 
End Function

Then you'd use it just like any other worksheet function:

=IsFormula(A1)

This will return either TRUE or FALSE.

I have used this code and it works fine until I refresh the data that the cells with formulas link to. Then I get a #VALUE! error. If I click in the formula bar and then out again, the code works again. Any advice? Thanks Dave

 

[T. Valko]

I have used this code and it works fine until I refresh the data that the cells with formulas link to. Then I get a #VALUE! error. If I click in the formula bar and then out again, the code works again. Any advice? Thanks Dave

Maybe add this line:

Function IsFormula(cell_ref As Range)
    Application.Volatile
    IsFormula = cell_ref.HasFormula
End Function

 

[DodgerDave88]

Sorry Biff, no such luck.

 

[T. Valko]

Sorry Biff, no such luck.

I don't have any other suggestions.

Maybe start a new thread?

 

[SSN651]

Really appreciate Biff adding in the additional information to define UDF and how to create and use the UDF. With that additional information I got it work Great. Thank you for providing more than a precise answer. The explanations raised the solution from some data to the level of being a knowledge nugget.