Checking a cell has correct formula

[Guest]

I want to put values in cells A1 & B1. I then want to type a formula into
cell C1 to add up A1 & B1. I want cell D1 to check that the formula in cell
C1 is correct and to display a relevant message, e.g. correct or incorrect.
What is the easiest way around this? Thanks

 

[Sandy Mann]

I don't understand your question. If you don't believe the formula in C1
why would you believe the formula in D1?

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Guest]

Obviously I know how to do the calculation formula but I'm unsure as to
whether to use an if statement for the checking of the formula.

 

[Guest]

It's a worksheet that I am trying to create. It's easy enough to write an if
statement to check that the value is correct but when I try to check if the
formula is correct it keeps returning an error.

 

[Gord Dibben]

To know if something is correct means you have to know beforehand what the
answer will be.

If, in this case =A1+B1 "must" be 20 then in D1 enter =IF(C1=20,OK","Not
OK")


Gord Dibben MS Excel MVP

 

[Guest]

What I want to check is someone's understanding of the formula and this is
why I need to do a check on the actual formula and not the result of the
calculation.

 

[Gord Dibben]

What do you think the formula in C1 should look like and what value should it
return?

We have no idea without more details.


Gord

 

[Dave Peterson]

And there can be lots of right formulas.

=a1+b1
=n(a1)+n(b1)
=sum(a1:b1)
=sum(a1,b1)
=if(iserror(sum(a1,b1)),"",sum(a1,b1))
=if(count(a1:b1)<2,"Not enough numbers",sum(a1:b1))

And nutty ones, too:
=(a1+3)-b1+(2*b1)-3

I'd say any of those in the top are right.

 

[Guest]

Of course, I hadn't considered that there could be lots of alternatives for
the correct formula. The main thing is to check understanding of formula. I
have used an if statement to check if the value is correct but this can't
check whether they have used a formula or just typed in the correct value!

 

[Sandy Mann]

How about a UDF? Soe=mething like:

Function TestFormula(here As Range)
If here = 20 Then
If here.HasFormula = True Then
TestFormula = "Correct"
Else: TestFormula = "No Formula!"
End If
Else: TestFormula = "Wrong Answer!"
End If

End Function

Paste the code into a Standard Module and enter in the spreadsheet:

=TestFormula(C1)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Guest]

Thank you, I will give this a try and see how it goes.

 

[Dave Peterson]

If you have 4 and 5 in a1 and b1, then
=20
in C1 will return "Correct".

 

[Sandy Mann]

Hi Dave,

You are quite right it does. I assume, ( I seem to have been doing a lot of
assuming lately), that HasFormula checks to see if the cell contents starts
with an equals sign and returns TRUE if it does. Obviously it would have to
do that because =100/5 is a legitimate formula therefore =20 must also be a
legitimate formula.

mmmmmmm.......

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Dave Peterson]

I think that the best solution for the OP is to come up with a list of
acceptable formulas--then just check for one of those.

If the user types in a different formula--even though it evaluates correctly,
then the response becomes not right or wrong, but "not what I was looking for".

Or maybe instead of letting the user type in a formula from scratch, some sort
of data|validation that offers a couple of right formulas and a few wrong, er,
unwanted formulas.

==
I would include these in the unwanted category:

=sum(a1+b1)
=+a1+b1
=+sum(a1:b1)

All those things that irritate me <vbg>.