Checking a cell has correct formula
 

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

Checking a cell has correct formula
 

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


with @tiscali.co.uk


"brownang" wrote in message
...
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

Checking a cell has correct formula
 

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.

"brownang" wrote:

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

Checking a cell has correct formula
 

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.

"Sandy Mann" wrote:

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


with @tiscali.co.uk


"brownang" wrote in message
...
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

Checking a cell has correct formula
 

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

On Sat, 26 May 2007 11:40:00 -0700, brownang
wrote:

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.

"brownang" wrote:

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

Checking a cell has correct formula
 

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" wrote:

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

On Sat, 26 May 2007 11:40:00 -0700, brownang
wrote:

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.

"brownang" wrote:

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

Checking a cell has correct formula
 

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

On Sat, 26 May 2007 12:12:01 -0700, brownang
wrote:

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" wrote:

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

On Sat, 26 May 2007 11:40:00 -0700, brownang
wrote:

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.

"brownang" wrote:

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

Checking a cell has correct formula
 

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.




brownang wrote:

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" wrote:

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

On Sat, 26 May 2007 11:40:00 -0700, brownang
wrote:

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.

"brownang" wrote:

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



--

Dave Peterson

Checking a cell has correct formula
 

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!

"brownang" wrote:

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

Checking a cell has correct formula
 

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


with @tiscali.co.uk


"brownang" wrote in message
...
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!

"brownang" wrote:

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

Checking a cell has correct formula
 

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

"Sandy Mann" wrote:

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


with @tiscali.co.uk


"brownang" wrote in message
...
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!

"brownang" wrote:

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

Checking a cell has correct formula
 

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



brownang wrote:

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

"Sandy Mann" wrote:

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


with @tiscali.co.uk


"brownang" wrote in message
...
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!

"brownang" wrote:

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





--

Dave Peterson

Checking a cell has correct formula
 

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


with @tiscali.co.uk


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



brownang wrote:

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

"Sandy Mann" wrote:

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


with @tiscali.co.uk


"brownang" wrote in message
...
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!

"brownang" wrote:

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





--

Dave Peterson

Checking a cell has correct formula
 

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.

Sandy Mann wrote:

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


with @tiscali.co.uk

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



brownang wrote:

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

"Sandy Mann" wrote:

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


with @tiscali.co.uk


"brownang" wrote in message
...
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!

"brownang" wrote:

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





--

Dave Peterson


--

Dave Peterson