Using If to combine answers including text and numbers

Hi

I am writing a brief questionnaire about the ages at which children are
allowed to do certain things, buy cigarettes etc, and have a simple problem.

If the person doing the test puts in the right answer, I've written the
formula to return "correct" "but it can vary" (or whatever the words are).
This is based on whether the answer they put in the cell matches the correct
answer on the second page of the worksheet.

SO, I'm fairly confident that the formula =IF(C4=Answers!B1,(Answers!C1+",
"+Answers!E1),(Answers!D1+", "+Answers!E1)) works, but it's return the error
expected for mixing text and numbers (C4 will be 16 for instance, the answer
being yes, but it can vary), coming from two fields.

Can anyone make this work, to incorporate the both responses?

Liam, Somerset England

Hi,

I'm not sure I fully understand but does this help?

=IF(C4=Answers!B1,Answers!C1&", "&Answers!E1,Answers!D1&", "&Answers!E1)

Mike

"Tablespider" wrote:

Hi

I am writing a brief questionnaire about the ages at which children are
allowed to do certain things, buy cigarettes etc, and have a simple problem.

If the person doing the test puts in the right answer, I've written the
formula to return "correct" "but it can vary" (or whatever the words are).
This is based on whether the answer they put in the cell matches the correct
answer on the second page of the worksheet.

SO, I'm fairly confident that the formula =IF(C4=Answers!B1,(Answers!C1+",
"+Answers!E1),(Answers!D1+", "+Answers!E1)) works, but it's return the error
expected for mixing text and numbers (C4 will be 16 for instance, the answer
being yes, but it can vary), coming from two fields.

Can anyone make this work, to incorporate the both responses?

Liam, Somerset England

Let me explain a little further, or at least try to.

Page 1 = Questions
Page 2 = Answers (same file, second worksheet, page2)

In page 1, I pose the question,
"At what age can a child get married?"

The next cell is for the respondent to complete their answer, and I'm
expecting "17" (without the quotes from them)

The next cell along (so if the question is in B2, their answer C2 and the
formulised answer will auto fill D2) will return the information sought with
the forumula.

On the next page/worksheet column 1 (A1) contains the question number - 1
thru 14, (B1) contains "17". (C1), "Correct" (D1) "Incorrect", lest they get
an answer not equal to 17, the fourth column (E1) would give "the answer is
17 and might have some more information".

SO, they enter, 18, and it returns "incorrect" AND "The answer is 17, but 16
in Scotland"

A reply from them of "17", would result in "correct" AND "The answer is 17
but 16 in Scotland"

Does that make any more sense?

A4 = Question
B4 = Response
C4 = Formula response

Worksheet 2

A1 = 1 (question number)
B1 = 17 (the right number, or age)
C1 = correct
D1 = incorrect
E1 = The answer is 17, but 16 in Scotland

Your guidance would be much appreciated

Liam



"Mike H" wrote:

Hi,

I'm not sure I fully understand but does this help?

=IF(C4=Answers!B1,Answers!C1&", "&Answers!E1,Answers!D1&", "&Answers!E1)

Mike

"Tablespider" wrote:

Hi

I am writing a brief questionnaire about the ages at which children are
allowed to do certain things, buy cigarettes etc, and have a simple problem.

If the person doing the test puts in the right answer, I've written the
formula to return "correct" "but it can vary" (or whatever the words are).
This is based on whether the answer they put in the cell matches the correct
answer on the second page of the worksheet.

SO, I'm fairly confident that the formula =IF(C4=Answers!B1,(Answers!C1+",
"+Answers!E1),(Answers!D1+", "+Answers!E1)) works, but it's return the error
expected for mixing text and numbers (C4 will be 16 for instance, the answer
being yes, but it can vary), coming from two fields.

Can anyone make this work, to incorporate the both responses?

Liam, Somerset England

The important thing that Mike advised you to do was to change the +
symbols in your formula (used for arithmetic) to &, which is used for
joining text together. Did you try Mike's suggestion?

Pete

On Sep 26, 8:48*pm, Tablespider
wrote:
Let me explain a little further, or at least try to.

Page 1 = Questions
Page 2 = Answers (same file, second worksheet, *page2)

In page 1, I pose the question,
"At what age can a child get married?"

The next cell is for the respondent to complete their answer, and I'm
expecting "17" (without the quotes from them)

The next cell along (so if the question is in B2, their answer C2 and the
formulised answer will auto fill D2) will return the information sought with
the forumula.

On the next page/worksheet column 1 (A1) contains the question number - 1
thru 14, (B1) contains "17". (C1), "Correct" (D1) "Incorrect", lest they get
an answer not equal to 17, the fourth column (E1) would give "the answer is
17 and might have some more information".

SO, they enter, 18, and it returns "incorrect" AND "The answer is 17, but 16
in Scotland"

A reply from them of "17", would result in "correct" AND "The answer is 17
but 16 in Scotland"

Does that make any more sense?

A4 = Question
B4 = Response
C4 = Formula response

Worksheet 2

A1 = 1 (question number)
B1 = 17 (the right number, or age)
C1 = correct
D1 = incorrect
E1 = The answer is 17, but 16 in Scotland

Your guidance would be much appreciated

Liam



"Mike H" wrote:
Hi,

I'm not sure I fully understand but does this help?

=IF(C4=Answers!B1,Answers!C1&", "&Answers!E1,Answers!D1&", "&Answers!E1)

Mike

"Tablespider" wrote:

Hi

I am writing a brief questionnaire about the ages at which children are
allowed to do certain things, buy cigarettes etc, and have a simple problem.

If the person doing the test puts in the right answer, I've written the
formula to return "correct" "but it can vary" (or whatever the words are). *
This is based on whether the answer they put in the cell matches the correct
answer on the second page of the worksheet.

SO, I'm fairly confident that the formula =IF(C4=Answers!B1,(Answers!C1+",
"+Answers!E1),(Answers!D1+", "+Answers!E1)) works, but it's return the error
expected for mixing text and numbers (C4 will be 16 for instance, the answer
being yes, but it can vary), coming from two fields.

Can anyone make this work, to incorporate the both responses?

Liam, Somerset England- Hide quoted text -

- Show quoted text -