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>

 

[venkat1926]

click FUNCTION key F5-specialcells-formulas

the cells containing formulas will be heighlightged. will this not help????

 

[Phil_r_c]

But this then requires an interrogation of every worksheet that each pupil has been required to use. At present the assessment coves five worksheets within a single workbook. By adding a single character to cell A1 of the first worksheet I am currently given the pupils mark and level, though it then requires me to go and look at three separate worksheets to see if they have used formulae correctly or bodged (eg =SUM(C5*D5) ) or simply typed in the mathematically correct answer to the questions posed. I then make a judgment on their ability to use Excel. This then becomes time consuming and something I am trying to reduce in terms of workload and also to increase the automation of computational assessment of the students Excel working ability.

I am working with secondary school age pupils so the formulae they are being asked to demonstrate in this baseline assessment are simple hence their ability to "do the maths in their head". </SPAN>

 

[venkat1926]

post a small extract of your file and explain with refrene to the data in that sheet.l

 

[T. Valko]

But this then requires an interrogation of every worksheet that each pupil has been required to use. At present the assessment coves five worksheets within a single workbook. By adding a single character to cell A1 of the first worksheet I am currently given the pupils mark and level, though it then requires me to go and look at three separate worksheets to see if they have used formulae correctly or bodged (eg =SUM(C5*D5) ) or simply typed in the mathematically correct answer to the questions posed. I then make a judgment on their ability to use Excel. This then becomes time consuming and something I am trying to reduce in terms of workload and also to increase the automation of computational assessment of the students Excel working ability.

I am working with secondary school age pupils so the formulae they are being asked to demonstrate in this baseline assessment are simple hence their ability to "do the maths in their head". </SPAN>

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

 

[T. Valko]

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

If you'll be using Excel 2013 there is the new ISFORMULA function:

http://office.microsoft.com/en-us/excel-help/isformula-function-HA102753291.aspx

 

[Phil_r_c]

Shame I am only using 2010 and my institution will unlikely upgrade until 2013 has been live and become "established".

Is there any way of creating a User Defined Function to do the same thing? This maybe a way around the Macro blocking security of my pupils access to our network.

 

[Aladin Akyurek]

Shame I am only using 2010 and my institution will unlikely upgrade until 2013 has been live and become "established".

Is there any way of creating a User Defined Function to do the same thing? This maybe a way around the Macro blocking security of my pupils access to our network.

You might want to have a look at Harlan Grove's ExtCell...

http://www.mrexcel.com/forum/excel-...-count-cells-meet-more-than-one-criteria.html

 

[mikerickson]

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.

 

[pgc01]

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.