Hi all,

I'm trying to set up some spreadsheet exercises for some students who are
studying Excel at an intermediate level.

One of the problems that they have to solve involves entering an IF statement
in cells F3 through to F10

What I want to do is to have the adjoining cells (G3 - G10) show Correct or
Try again, depending on whether the formula they have used is right. Is there
a way of entering the formula required into the logical argument of an IF
statement in cells G3 - G10?

Thanks,

Tazzy

--
Message posted via http://www.officekb.com

How do we or Excel know if the formula in F3 is correct?

If it returns a particular result would it be considered correct?

In F3 =IF(A1*B1=302,TRUE,FALSE)

In G3 =IF(F3=TRUE,"OK","NOT OK)


Gord Dibben MS Excel MVP

On Sat, 01 Mar 2008 19:06:51 GMT, "Tazzy via OfficeKB.com" <u26845@uwe wrote:

Hi all,

I'm trying to set up some spreadsheet exercises for some students who are
studying Excel at an intermediate level.

One of the problems that they have to solve involves entering an IF statement
in cells F3 through to F10

What I want to do is to have the adjoining cells (G3 - G10) show Correct or
Try again, depending on whether the formula they have used is right. Is there
a way of entering the formula required into the logical argument of an IF
statement in cells G3 - G10?

Thanks,

Tazzy

If I understand what you are asking, I think you can use this worksheet
Change event code to do what you want. Just set the correct formulas in the
various Case statements.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Correct As Boolean
If Target.Count 1 Then Exit Sub
If Not Intersect(Target, Range("F3:F10")) Is Nothing Then
Select Case Target.Row
Case 3
Correct = (Target.Formula = "=SUM(A3:C3)")
Case 4
Correct = (Target.Formula = "=LEN(B4)")
'cases 5 thru 10 go here
End Select
If Correct Then
Target.Offset(0, 1).Value = "Correct"
Else
Target.Offset(0, 1).Value = "Try again"
End If
End If
End Sub

Rick

"Tazzy via OfficeKB.com" <u26845@uwe wrote in message
news:808499f0fa3db@uwe...
Hi all,

I'm trying to set up some spreadsheet exercises for some students who are
studying Excel at an intermediate level.

One of the problems that they have to solve involves entering an IF
statement
in cells F3 through to F10

What I want to do is to have the adjoining cells (G3 - G10) show Correct
or
Try again, depending on whether the formula they have used is right. Is
there
a way of entering the formula required into the logical argument of an IF
statement in cells G3 - G10?

Thanks,

Tazzy

--
Message posted via http://www.officekb.com

Thanks for the prompt reply on this one. I don't think I was clear enough for
what I wanted.

I am looking for a specific formula to be entered in cell F3, i.e.

=IF(D3+D3<E3,"URGENT","").

No other formula would be acceptable.

So in G3 I'm trying to enter an IF statement of my own that would comment
Correct or Try Again depending on the formula entered by the student. Does
that make sense? A number of students will have this exercise to work through
and I'm trying to make it a bit more interactive so that they can see if they
are correct. (Also helps to reduce my marking time :-) ).

Tazzy

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200803/1

On Mar 1, 11:53*am, "Tazzy via OfficeKB.com" <u26845@uwe wrote:
I am looking for a specific formula to be entered in cell F3, i.e.
=IF(D3+D3<E3,"URGENT","").
No other formula would be acceptable.

Why not any of the following:

=if(E3D3+D3,"URGENT","")

=if(D3+D3=E3,"","URGENT")

=if(E3-D3D3,"URGENT","")

...etc etc etc...

Not to mention that "D3+D3" can be written "2*D3"; and if you meant
"D2+D3", it can be written "D3+D2". And not to mention adding
whitespace at their discretion.

If you want your students to simply parrot what you think
(incorrectly) is the only or best answer, you should give them a
multiple choice, rather than ask them to be creative. Perish the
thought!

Rick,

Thank you for taking the time to give a constructive answer to my question.
Had I wished to ramble on for ages, then I could have explained at length why
the formula had to in a certain format (yes, it should have been D2 + D3.
although I accept that it could be D3 + D2), however I chose to be concise
and to concentrate on what I wished to achieve.

Tazzy

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200803/1

Well, you could always do this instead...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Correct As Boolean
If Target.Count 1 Then Exit Sub
If Not Intersect(Target, Range("F3:F10")) Is Nothing Then
Select Case Target.Row
Case 5
Correct = InStr("*" & "=IF(D2+D3<E3,""URGENT"","""")" & "*" & _
"*" & "=IF(D3+D2<E3,""URGENT"","""")" & "*" & _
"*" & "=IF(E3D2+D3,""URGENT"","""")" & "*" & _
"*" & "=IF(E3D3+D2,""URGENT"","""")" & "*" & _
"*" & "=IF(D2+D3=E3,"""",""URGENT"")" & "*" & _
"*" & "=IF(D3+D2=E3,"""",""URGENT"")" & "*" & _
"*" & "=IF(E3<=D2+D3,"""",""URGENT"")" & "*" & _
"*" & "IF(E3<=D3+D2,"""",""URGENT"")" & "*", _
"*" & Target.Formula & "*")
'cases 4 thru 10 go here
End Select
If Correct Then
Target.Offset(0, 1).Value = "Correct"
Else
Target.Offset(0, 1).Value = "Try again"
End If
End If
End Sub

The asterisks that are concatenated on the front and back of each formula
variation and the Target.Formula are there to make sure the student doesn't
get a false match by inputting only a part of the formula.

I realize this is a lot of work to catch all the rearrangements of your
formulas (especially if you have to account for the doubled-up quote marks
needed to satisfy VB's use of the quote mark as a meta-character); but I
worry that a simple check of results of your actual formula against the
results generated by the formula entered by the student may report a false
match... the student could easily have an incorrect formula that happens to
generate a correct value for a specific condition without the entered
formula being correct for the general case.

Rick


"Tazzy via OfficeKB.com" <u26845@uwe wrote in message
news:80854d2684e82@uwe...
Rick,

Thank you for taking the time to give a constructive answer to my
question.
Had I wished to ramble on for ages, then I could have explained at length
why
the formula had to in a certain format (yes, it should have been D2 + D3.
although I accept that it could be D3 + D2), however I chose to be concise
and to concentrate on what I wished to achieve.

Tazzy

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200803/1

On Mar 1, 12:27*pm, "Tazzy via OfficeKB.com" <u26845@uwe wrote:
Thank you for taking the time to give a constructive answer
to my question. Had I wished to ramble on for ages, then I
could have explained at length why the formula had to in a
certain format

I presume you are alluding to my response, and you are implying that
it was not constructive.

On the contrary, it was my intent for it to be constructive. I was
trying to show you the complexity of the problem and why, at the very
least, there cannot be any "formulae" that could solve your problem,
if by that you meant something other than a VB UDF.

Even Rick's attempt to exhaustively test for some of the possible
student formulas that I suggested is not adequate, IMHO, even if it
worked. (Note: Rick's sub returns "correct" if the target cell has
no formula. I think that defect can be fixed easily.) It fails to
account for variable whitespace, which is not unreasonable to expect.

Ideally, we would use a function that accepts regular expressions. I
presume there is none, even in the Windows API, because a google
search reveals solutions offered for a price. In the absence of
RegExp function, I would write a parser. Even then, I think you would
have to limit the possible student formulas arbitrarily.

Arguably, you could express the problem to the student as follows:
``Write a formula in F3 that uses "<" to compare the sum of D2 and D3,
in that order, with E3 and return "URGENT" if true and "" if false.
Do not include any extra spaces in your formula. Use relative
references, and copy the formula into F4 through F10.``

Yeah, I guess that would "explain at length why the formula had to in
a certain format" ;-).

Sorry if that is not the simplistic solution you wanted, and you
consider anything to be "non-constructive".

Thanks for taking the time to reply.

I took your reply to insinuate that I was adopting a blinkered and immovable
stance on how I wished my students to answer a particular question, and that
I did not wish them to adopt any form of constructive thinking.

Nothing could be further from the truth; having given them a wide scope for
answering previous exercises that I had provided them with, the time had come
to move on to answering questions for a nationally recognised qualification.
Unfortunately these tend to be somewhat regimented and require an answer in a
particular format (that I don't necessarily agree with as there is almost
always more than one way of expressing a formula that will give the correct
result).

Having seen the length of the replies and coding that is involved with
solving my problem, I guess that it will be back to paper and pencil to mark
any answers.

I hope that this clears up why I responded to your comments in the manner
that I did as unfortunately plain text in a message can rarely convey the
point that you wish to make in the manner that you would like.

:-)

Tazzy

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200803/1