Formulaic Solution Required to differentiate between numeric and formulaic entry!

[Phil_r_c]

I am trying to write a self-marking assessment for classes of young school pupils I teach and I have struggled to find a solution to a key issue:

How can I automatically detect if a student has used a formula or worked out the correct values to posed problems rather than solved it in their head (or on a calculator!) and simply typed the numeric value in?

With over 154 scripts to mark each time the test runs a foolproof way to show that pupils are using Excel effectively is essential; as it shows they are working at a higher academic level which in turn has to be reported back to senior managers and parents.

Due to security restrictions on our school network any workbooks used can't have macros embedded into them as the macro functionality is blocked to all but a very privileged few!</SPAN>

Any help/guidance gratefully appreciated! </SPAN>

 

[mikerickson]

An alternate test would be to test for both "=" and any letter.
(note the relative referencing, select a cell in row 1 and define)

Name: formula RefersTo: =GET.CELL(6,Sheet1!$A1)
Name: maxCode RefersTo: =MAX(CODE(MID(formula&REPT(" ",255),ROW(Sheet1!$1:$100),1)))
Name: nonConstantNumericFormula RefersTo: =AND(CODE(formula&" ")=61,64<maxCode)

=nonConstantNumericFormula would return True for =SUM(B2:C2) and false for =34 or 34.

(If a student is smart enough to figure out that =34+A1000-A1000 will "fool" the test, then the student is smart enough to use the desired approach.)

 

[mikerickson]

An alternate test would be to test for both "=" and any letter.
(note the relative referencing, select a cell in row 1 and define)

Name: formula RefersTo: =GET.CELL(6,Sheet1!$A1)
Name: maxCode RefersTo: =MAX(CODE(MID(formula&REPT(" ",255),ROW(Sheet1!$1:$100),1)))
Name: nonConstantNumericFormula RefersTo: =AND(CODE(formula&" ")=61,64<maxCode)

=nonConstantNumericFormula would return True for =SUM(B2:C2) and false for =34 or 34.

(If a student is smart enough to figure out that =34+A1000-A1000 will "fool" the test, then the student is smart enough to use the desired approach.)

 

[Rick Rothstein]

<maxcode)
(If a student is smart enough to figure out that =34+A1000-A1000 will "fool" the test, then the student is smart enough to use the desired approach.)

The problem is that more than likely the student will ask this forum how to get around the test the OP has imposed and someone here will post your above quoted formula as the solution for them.</maxcode)

 

[Aladin Akyurek]

Hi Phil

Remark: I don't think it's enough to know if the cell has a formula. If the students find out about this they will enter the constant result with something like:

=3

Excel will view this as a formula.

When A2 houses:

=3

=EXTCELL("hasformula" A2)

would return 1 (affirmative of a formula). I tend to agree with such an evaluation.

 

[Phil_r_c]

@Pgc01:
Though I agree =2 would fool any formulae checking setup, I would doubt any Primary School pupil would have be taught to enter data in this manner. The assessment that I am running is a Baseline test to assess what pupils may or may not know in Excel as they enter my school. So there are several worksheets that require data to be entered from a drop down list, by typing in values that are validated, then as they work though the worksheets they are tasked with writing simple formulae that are meant to mulitply two cells together by 7, a cell by 52, calculate the total value from a column of data etc etc. they are not told inadvance that this is a self marking workbook, they are simply told to work through it as best they can.

Ideally I would ultimatly want to differentiate between =sum(a2*a3) and =(a2*a3) or other variants!

@mikerickson
Have found that =GET.CELL(48,INDIRECT("RC[-1]",FALSE)) works just as effectively at finding formulae but is not cell specific. Thank you for putting me onto this line of enquiry, I think it might be the way forward. Time to now to recode my worksheet and try it on the "secured" school network!

 

[Snakehips]

Define a Name: hasFormula RefersTo: =(Left(Get.Cell(6, Sheet1!$A$1), 1)="=")

then put =IF(hasFormula, "A1 has a formula", "it doesn't")

Of concern to the OP, =2 is counted as a formula.

I was going to suggest dealing with the =2 concern by changing the

Refers To:

=AND((LEFT(GET.CELL(6,Sheet1!$A$1:$A$6),1)="="),(NOT(ISNUMBER(IFERROR(VALUE(SUBSTITUTE(GET.CELL(6,Sheet1!$A$1),"=","",1)),"x")))))

but then I realised that there were further responses on page 2 that have already addressed it.

However, if you do find that you are able to use code by way of a UDF then this may help.
It assumes only numeric answers to the test questions.

Function IsFormula(MyCell As String) As Boolean
IsFormula = Left(Range(MyCell).Formula, 1) = "=" And _
Not (IsNumeric(Application.WorksheetFunction.Substitute(Range(MyCell).Formula, "=", ""))) And _
Not Right(Range(MyCell).Formula, 1) = """"
End Function

Excel 2007
	A	B	C
1	Test Answer	Entered As	Is Formula?
2	0	=E2+F2	TRUE
3	7	=7	FALSE
4	7	7	FALSE
5	7	="7"	FALSE
Sheet1

Cell Formulas
Range	Formula
C2	=isformula(CELL("Address",A2))
C3	=isformula(CELL("Address",A3))
C4	=isformula(CELL("Address",A4))
C5	=isformula(CELL("Address",A5))

Still can be 'fooled' by =34+A1000-A1000 !!

 

[Phil_r_c]

The get.cell is great (on my machine) placing immediatly after a cell that I am interested in but how can I send it to look at a non-adjacent cell? How do i redefine the "HasFormula" so that it would be written as =HasFormula(1) to look at the cell to its left, =HasFormula(2) for 2 to its left and so on.

I am trying to avoid hiding columns as pupils will be able to (and need to see column and row headings and a missing letter will confuse and as I wont be on hand to explain that it is not a mistake in Excel as they are prone to suggest when faced with hidden rows/columns and not been previously taught about it.

Here is one of the instances that I would be looking to check carefully:</SPAN>
[TABLE="width: 503"]
<TBODY>[TR]
[TD][TABLE="width: 500"]
<TBODY>[TR]
[TD]Col A
[/TD]
[TD]Col B
[/TD]
[TD]Col C
[/TD]
[TD]Col D
[/TD]
[TD]Col E
[/TD]
[TD]Col F
[/TD]
[TD]Col G
[/TD]
[TD]Col H
[/TD]
[/TR]
[TR]
[TD]Number</SPAN>
[/TD]
[TD]Bought</SPAN>
[/TD]
[TD]Cost per day for ONE animal</SPAN>
[/TD]
[TD]Cost per week for the animals you bought</SPAN>
[/TD]
[TD]Cost per year for the animals you bought</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Goats</SPAN>
[/TD]
[TD]£0.25</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Sheep</SPAN>
[/TD]
[TD]£0.40</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Donkeys</SPAN>
[/TD]
[TD]£0.55</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Pigs</SPAN>
[/TD]
[TD]£0.25</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Chickens</SPAN>
[/TD]
[TD]£0.10</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Ducks</SPAN>
[/TD]
[TD]£0.15</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Geese</SPAN>
[/TD]
[TD]£0.15</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
</TBODY>[/TABLE]

The first column (A) brings data from a previous sheet, Columns D and E need to have formulae entered by the pupil, D=A*C*7 and E=D*52

Col F was used to check the numeric accuracy of the values appearing in D and E but this can be easily moved to H to make way for the HasFormula but at the moment the HasFormula could only identify the Col E content but not the Col D formulae, unless I can set the indirect function variable values as I call the get.cell function. So my question is how can I do this?</SPAN>

And yes it it straight forward for many Excel users but my goal is to reduce the time I need to look at their work to get an accurate measure of their abilities.

Thanks again to those who have taken up this challenge and pointing me in the right direction.

</SPAN>

 

[mikerickson]

If you want to make the cell being looked at variable, you could do something with OFFSET

It would be easier to use a fixed lookup offset to create a helper column.

 

[T. Valko]

It is not possible to differentiate between a formula and a constant entry without some form of VBA.

Define a Name: hasFormula RefersTo: =(Left(Get.Cell(6, Sheet1!$A$1), 1)="=")

then put =IF(hasFormula, "A1 has a formula", "it doesn't")

Of concern to the OP, =2 is counted as a formula.

Although technically not VBA, GET.CELL is a macro function and requires that macros be enabled in Excel versions 2007 and later.

 

[T. Valko]

It is not possible to differentiate between a formula and a constant entry without some form of VBA.

The get.cell is great (on my machine) placing immediatly after a cell that I am interested in but how can I send it to look at a non-adjacent cell? How do i redefine the "HasFormula" so that it would be written as =HasFormula(1) to look at the cell to its left, =HasFormula(2) for 2 to its left and so on.

I am trying to avoid hiding columns as pupils will be able to (and need to see column and row headings and a missing letter will confuse and as I wont be on hand to explain that it is not a mistake in Excel as they are prone to suggest when faced with hidden rows/columns and not been previously taught about it.

Here is one of the instances that I would be looking to check carefully:</SPAN>
[TABLE="width: 503"]
<TBODY>[TR]
[TD][TABLE="width: 500"]
<TBODY>[TR]
[TD]Col A
[/TD]
[TD]Col B
[/TD]
[TD]Col C
[/TD]
[TD]Col D
[/TD]
[TD]Col E
[/TD]
[TD]Col F
[/TD]
[TD]Col G
[/TD]
[TD]Col H
[/TD]
[/TR]
[TR]
[TD]Number</SPAN>
[/TD]
[TD]Bought</SPAN>
[/TD]
[TD]Cost per day for ONE animal</SPAN>
[/TD]
[TD]Cost per week for the animals you bought</SPAN>
[/TD]
[TD]Cost per year for the animals you bought</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Goats</SPAN>
[/TD]
[TD]£0.25</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Sheep</SPAN>
[/TD]
[TD]£0.40</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Donkeys</SPAN>
[/TD]
[TD]£0.55</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Pigs</SPAN>
[/TD]
[TD]£0.25</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Chickens</SPAN>
[/TD]
[TD]£0.10</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Ducks</SPAN>
[/TD]
[TD]£0.15</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Geese</SPAN>
[/TD]
[TD]£0.15</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

[/TD]
[/TR]
</TBODY>[/TABLE]

The first column (A) brings data from a previous sheet, Columns D and E need to have formulae entered by the pupil, D=A*C*7 and E=D*52

Col F was used to check the numeric accuracy of the values appearing in D and E but this can be easily moved to H to make way for the HasFormula but at the moment the HasFormula could only identify the Col E content but not the Col D formulae, unless I can set the indirect function variable values as I call the get.cell function. So my question is how can I do this?</SPAN>

And yes it it straight forward for many Excel users but my goal is to reduce the time I need to look at their work to get an accurate measure of their abilities.

Thanks again to those who have taken up this challenge and pointing me in the right direction.

</SPAN>

To make the GET.CELL function able to use relative referencing...

Create this defined named formula:

Name: IsFormula
Refers to:

=GET.CELL(48,TEXTREF(SUBSTITUTE(MID(GET.FORMULA(TEXTREF("rc")),17,100),")","")))

Then, use this formula on the worksheet:

=IF(1,IsFormula,cell_reference)

Where cell_reference is the cell reference of the cell you want to test for the presence of a formula. For example, to test cell D10:

=IF(1,IsFormula,D10)

This formula can be entered in any cell (except the cell it references of course!).