Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using If to combine answers including text and numbers
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using If to combine answers including text and numbers
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using If to combine answers including text and numbers
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Novice user trying to use variables including text and numbers | Excel Worksheet Functions | |||
How can I count numbers without including the repeated values? | Excel Discussion (Misc queries) | |||
how to combine including blank cells | New Users to Excel | |||
Converting Formula answers to numbers | Excel Discussion (Misc queries) | |||
Counting numbers in a column without including others | Excel Worksheet Functions |