Excel - check for formula entered, not number 1

[MarcLodge]

Hi All,
A teacher at the local primary school has a spreadsheet that he is getting the class to complete which tests their knowledge of spreadsheets. One of the many little tests gets them to write a very simple formula in a cell, according to the instructions given. This is then checked with an IF C1 = 24.7 type of statement, and a message displayed.

What some of the little chreubs have discovered is that instead of typing in the formula, they can work out the answer by multiplying adding etc. and then just type the answer into the cell they can get away without actually having to format the command correctly.

Now whereas I've got to admire their sneaky intelligence, it does defeat the object of the exercise. So....is there a function or statement that checks for the existence of a formula being entered rather than a figure?

Marc

 

[hisbadbanana]

go to Intert/Name/Define

in the Names in workbook box type "isAformula"
in the Rferes to box type "=GET.CELL(48,INDIRECT("rc",FALSE))"

then just set the condition formatting for the cells you want to check as Formula is "=isAformula

 

[MarcLodge]

I have a feeling that the teacher is doing this with a cell rather than conditional formatting. Can this idea be incorporated into an IF statement?

 

[AnotherHiggins]

->So....is there a function or statement that checks for the existence of a formula being entered rather than a figure?

I question the validity of this approach.

Let's say that you check for the value, 24.7 AND you check to see that a formula was used.

What's to stop the little buggers from typing in [COLOR=blue white]=24+0.7[/color] ?

That would fulfill both requirements, but I'd bet it's not what you are looking for.

Is there one and only one formula that the children should type in?

- If so, you could check for that exact formula (via code is the only way I know of).

- If not, then I think you are out of luck.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[hisbadbanana]

this gives you a clear visible indicator.

any cell that is a formula as apposed to a number will be highlighted by whatever format you put in the conditional formatting.

i'm unsure of whatelse you want.

 

[MarcLodge]

I'll have to go back to the teacher and ask him what he wants as John's point is pretty valid. That said, if they are starting it with an '=' then I guess that's at least something.

bad, your suggestion has got definite possibilities but I don't understand the line in your reply:

in the Rferes to box type "=GET.CELL(48,INDIRECT("rc",FALSE))"

What should I be doing here, sorry?

Marc

 

[hisbadbanana]

when you have the Define Name box open, at the bottom there is a 'Refers to' field. in that field you should type "=GET.CELL(48,INDIRECT("rc",FALSE))" exactly as ive written it.

let me know how you get on.

 

[xlbo]

Marc - if you know the formula that should be used, then this can be copied into a standard module in excel VBE and used as a functon on the worksheet:

Function IsItTheRightFormula(rng As Range)

Const strForm = "=this is the formula to test for

If UCase(rng.Formula) <> UCase(strForm) Then
  IsItTheRightFormula = "No"
Else
  IsItTheRightFormula = "Yes"
End If
End Function

To use it, enter

=IsItTheRightFormula(A1)

where the cell to be checked is A1

This is not the most memory efficient way of doing this but I wouldn't expect the data to be particularly enormous so should be alright and will give you a definite yes no as to whether the correct formula has been used

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[MarcLodge]

Bad,
now that I've typed it in correctly in the right place, that's working brilliantly well.

Using this type of approach, what I would like to do is to have a cell that contains the message "Stop cheating you little toads" or something similar and have it set to white on white and have the conditional formatting change the colour.

Is there anyway I can easily utilise your method with this approach.

By the way, what on earth does "=GET.CELL(48,INDIRECT("rc",FALSE))" mean, or where can I find some info on it?

Marc

 

[xlbo]

MArc - you could of course change the output of my UDF as it suits:

Function IsItTheRightFormula(rng As Range)

Const strForm = "=this is the formula to test for

If UCase(rng.Formula) <> UCase(strForm) Then
  IsItTheRightFormula = "Stop Cheating You Little Toads"
Else
  IsItTheRightFormula = "Well done - have a star"
End If
End Function

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[MarcLodge]

Geoff,
That's definitely got huge potential as the teacher will know exactly what formula should be typed.

I've tried using it though by dropping intp VB editor, clicking on sheet1 and copying your function straight in. When I try to use it in the spreadsheet I get #NAME?

I've ensured I've not typoed by copying and pasteing the function name into the cell.

Have I copied the function into the wrong place?

Apologies for all these questions.

Marc

 

[AnotherHiggins]

Marc,
Paste it into a standard module, not the sheet.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[MarcLodge]

Geoff,

That's working perfect now. Very many thanks.

Marc

 

[hisbadbanana]

The only problem with

IsItTheRightFormula = "Stop Cheating You Little Toads"

is.. if they try to get it right, i mean they really really try, and they get it wrong, you're still caling them a cheat.

-hisbad

 

[TommyP]

Why not use CTRL-~ (Control + Tilde)

This shows the formulas, not the results.

 

[MarcLodge]

Tommy,

[pretending he'd heard of CTRL~]
How would I use the CTRL~ in this context?
[/pretend]

Marc

 

[AnotherHiggins]

Marc,

[CTRL]+[~] is a bit misleading - at least on an American QWERTY keyboard. The `/~ key is to the left of the 1 at the top of the letters on your keyboard. I say [CTRL]+[~] is misleading because Tilda (~) is the result of this key along with shift. When using this keyboard shortcut, no SHIFT is needed. That having been said....

[CTRL]+[`] is the keyboard shortcut for Tools > Options > View > Formulas. It basically shows all formulas instead of the values they return. (In addition, it shows the serial number of stored dates, but that's beside the point.)

To use this, your teacher friend would have to press [CTRL]+[`] on each students workbook and review the formula for himself.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[MarcLodge]

I think that Geoff solution seems to be the best, although hisbad has a very valid point regards a pupil trying to beat the system, and a pupil getting it incorrect.

The best way I can see round it is to check:
a) for 1st character not '=' (message:format error)
else
b) text does not contain alpha (message: format error)
else
c) text equals correct formula, or derivative of it (message: well done)
else
d) message: not quite right, try again

 

[KenWright]

You might also want to post the exact formula and variables that the teacher believes there is only one solution for, because I'm betting...........

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[xlbo]

lol - yes - too true Ken although if the problem is assigned as:

"Using the X formula, calculate the wossname"

then not as much of an issue

Marc - that logic would certainly seem the way to go - are you comfortable using the Left / Right / Mid functions to split the formula up for your tests ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question