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

 

[MarcLodge]

Geoff,
Having seen my questions on here, you probably can guess that the answer to that is no. I'll give it a try though especially as you've pointed me in the right direction. One day I must buy a VBA book!
Marc

 

[xlbo]

Use LEFT to get 1st character
Not sure what "text does not contain alpha" means but to check for specific text strings within another text string, have a look at the INSTR function

If you have specific queries on splitting the string up, post back in this thread

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

 

[KenWright]

LOL

Using the SUM formula:-

=SUM(A1,B1,C1,D1)
=SUM(A1:B1,C11)
=SUM(A11)

Using the SUMPRODUCT formula:-

=SUMPRODUCT(--(A1:A10="xyz"),B1:B10)
=SUMPRODUCT((A1:A10="xyz")*(B1:B10))

Using the SUMIF formula:-

=SUMIF(A1:C11,">"&A1)
=SUMIF(A1:C11,">5")

etc etc



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

 

[xlbo]

yerriknow

maybe need to check the 1st x characters and the result....

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

 

[AnotherHiggins]

Marc,

Do you know/can you find out what the formula your teacher friend is expecting to get from the students and what question he will pose to elicit the formula?

If we know exactly what he is looking for we can better advise on how to check the answer automatically.

[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]

John,
I will atempt to get the information. The question was asked of me on Thursday evenig. After consultation with yourselves, I informed said teacher that an answer was in the offing and that he needed to send the already built spreadsheet with problematical questions therein.

As of yet, nothing. I will send another email tomorrow. Watch this space.........

Ps. Once again everybody, thanks for your help.

Marc