Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested Ifs in 2007
I have a survey sheet where users have 4 questions to answer Yes or No to.
Depending on the combination of Yes's and No's, I need to return a certain cell value from another worksheet. There are 5 possible combinations (1=No, No, No, No/ 2=Yes, No, No, No/3= Yes, Yes, No, No /4=Yes, Yes, Yes, No /5=Yes, Yes, Yes, Yes). So, for example, if a user responds "No, No, No, No" I need the cell containing the formula to return a value that is in another worksheet. The other value is the "solution" so to speak to their responses. If they answer No to all the questions, then the answer to their problem is the text that is in a cell on the corresponding worksheet. I am a MOUS trainer and a student has asked me to help them out with this and I am stumped! Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested Ifs in 2007
Well, assuming that the 5 combinations that you list are the only
possibilities, i.e.: (in other words, YES / YES / NO / YES isn't valid, and cannot be chosen that way), you could just count the YES's in your cells, (0-4), and set up a table on your other sheet and do a VLOOKUP. Say for example, your questions are answered YES or NO in Sheet1 range D4 through D7 On sheet 2, you have in cells A2 to A6 the values 0 to 4, and in cells B2 to B6 the corresponding 'answer' for your selections (obviously B2 would be the answer for all 4 No's, and B6 would be the answer to all 4 Yes's). Then on sheet1, you could put a formula into, for example, cell D10 D10: =IF(AND(D4<"",D5<"",D6<"",D7<""),VLOOKUP(COUNT IF(D4:D7,"Yes"),Sheet2!$A$2:$B$6,2,FALSE) If you run into trouble, look for help on the VLOOKUP function, or post back. -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "NHMcCormick07" wrote: I have a survey sheet where users have 4 questions to answer Yes or No to. Depending on the combination of Yes's and No's, I need to return a certain cell value from another worksheet. There are 5 possible combinations (1=No, No, No, No/ 2=Yes, No, No, No/3= Yes, Yes, No, No /4=Yes, Yes, Yes, No /5=Yes, Yes, Yes, Yes). So, for example, if a user responds "No, No, No, No" I need the cell containing the formula to return a value that is in another worksheet. The other value is the "solution" so to speak to their responses. If they answer No to all the questions, then the answer to their problem is the text that is in a cell on the corresponding worksheet. I am a MOUS trainer and a student has asked me to help them out with this and I am stumped! Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested Ifs in 2007
Hi,
Try this: =VLOOKUP(COUNTIF(A1:A4,"no"),Sheet2!$K$1:$L$5,2,FA LSE) A1:A4 contain the responses to the four questions, K1:L5 is a lookup table set up something like this K L 0 Great job 1 Superior 2 Good 3 Fair 4 Poor If this helps, please click the Yes button Cheers, Shane Devenshire "NHMcCormick07" wrote: I have a survey sheet where users have 4 questions to answer Yes or No to. Depending on the combination of Yes's and No's, I need to return a certain cell value from another worksheet. There are 5 possible combinations (1=No, No, No, No/ 2=Yes, No, No, No/3= Yes, Yes, No, No /4=Yes, Yes, Yes, No /5=Yes, Yes, Yes, Yes). So, for example, if a user responds "No, No, No, No" I need the cell containing the formula to return a value that is in another worksheet. The other value is the "solution" so to speak to their responses. If they answer No to all the questions, then the answer to their problem is the text that is in a cell on the corresponding worksheet. I am a MOUS trainer and a student has asked me to help them out with this and I am stumped! Thanks! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested Ifs in 2007
I can see how this might work, but it is still returning only one response no
matter what the combination of Yes/No. So here's more on the setup: CS rep fills out a contact response form reporting on what happened during a call. The questions are, with drop-down lists to select the yes or no answer from: Made voice contact? Yes/No Received invoice? Yes/No Disputes? Yes/No Escalation? Yes/No Then, depending on the situation -- the combination of yes/no anwers -- the cells on sheet 2 have the "next steps" to try and resolve the situation. So if the rep answers No, No, No, No I need on sheet 1 to display in the selected cell "If the 10 day caller does not reach the contact, a one day follow up is set for the billing specialist." from cell B1 on sheet 2. Or if the answers are Yes, Yes, Yes, No I need it to show the response "If the customer has disputes, necessary adjustments will be made and the revised invoices will be sent. Follow up in 7 business days." from cell B4. (And so forth for each possible combination. The data table is set up in Sheet2!A1:B5). The responses listed above are just two of 5 that could be returned based on the correct combination of yes/no answers. This response is to be displayed in a cell labeled: Follow-Up Solution. Logically looking at the possible combinations, I see that counting the yes's "should" work, but again... only getting the same return for any combination (it is returning the value in cell B1 from sheet2 only, and not adjusting to the others). Thanks for the help on this... you're my hero. -McCormick 'Aspiring Excel Master' "John C" wrote: Well, assuming that the 5 combinations that you list are the only possibilities, i.e.: (in other words, YES / YES / NO / YES isn't valid, and cannot be chosen that way), you could just count the YES's in your cells, (0-4), and set up a table on your other sheet and do a VLOOKUP. Say for example, your questions are answered YES or NO in Sheet1 range D4 through D7 On sheet 2, you have in cells A2 to A6 the values 0 to 4, and in cells B2 to B6 the corresponding 'answer' for your selections (obviously B2 would be the answer for all 4 No's, and B6 would be the answer to all 4 Yes's). Then on sheet1, you could put a formula into, for example, cell D10 D10: =IF(AND(D4<"",D5<"",D6<"",D7<""),VLOOKUP(COUNT IF(D4:D7,"Yes"),Sheet2!$A$2:$B$6,2,FALSE) If you run into trouble, look for help on the VLOOKUP function, or post back. -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "NHMcCormick07" wrote: I have a survey sheet where users have 4 questions to answer Yes or No to. Depending on the combination of Yes's and No's, I need to return a certain cell value from another worksheet. There are 5 possible combinations (1=No, No, No, No/ 2=Yes, No, No, No/3= Yes, Yes, No, No /4=Yes, Yes, Yes, No /5=Yes, Yes, Yes, Yes). So, for example, if a user responds "No, No, No, No" I need the cell containing the formula to return a value that is in another worksheet. The other value is the "solution" so to speak to their responses. If they answer No to all the questions, then the answer to their problem is the text that is in a cell on the corresponding worksheet. I am a MOUS trainer and a student has asked me to help them out with this and I am stumped! Thanks! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested Ifs in 2007
On Thu, 20 Nov 2008 14:23:01 -0800, NHMcCormick07
wrote: I have a survey sheet where users have 4 questions to answer Yes or No to. Depending on the combination of Yes's and No's, I need to return a certain cell value from another worksheet. There are 5 possible combinations (1=No, No, No, No/ 2=Yes, No, No, No/3= Yes, Yes, No, No /4=Yes, Yes, Yes, No /5=Yes, Yes, Yes, Yes). So, for example, if a user responds "No, No, No, No" I need the cell containing the formula to return a value that is in another worksheet. The other value is the "solution" so to speak to their responses. If they answer No to all the questions, then the answer to their problem is the text that is in a cell on the corresponding worksheet. I am a MOUS trainer and a student has asked me to help them out with this and I am stumped! Thanks! You could do something like in a cell on Sheet1: =VLOOKUP(SUMPRODUCT((LEFT($A$1:$A$4)="y")*{1;2;4;8 }),Sheet2!$A$1:$B$5,2,FALSE) The SUMPRODUCT function should return a unique number depending on the combination of Yes/No in A1:A4. VLOOKUP will return an #NA error if the combination is not a valid one. Your table on Sheet2 should be set up as: 0 If the 10 day caller does not reach the contact, a one day follow up is set for the billing specialist. 1 Option for YNNN 3 Option for YYNN 7 If the customer has disputes, necessary adjustments will be made and the revised invoices will be sent. Follow up in 7 business days. 15 Option for YYYY Depending on what you want to do in the event of an error (and also if you are running Excel 2007), you can wrap the above in either an IF or an IFERROR function. For example, in Excel 2007: =IFERROR(VLOOKUP(SUMPRODUCT((LEFT($A$1:$A$4)="y") *{1;2;4;8}),Sheet2!$A$1:$B$5,2,FALSE),"Invalid Answers") --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 balks at 7 nested IFs | Excel Worksheet Functions | |||
Nested IF compatability error with Excel 2007 | Excel Worksheet Functions | |||
Excel 2007 - Outline not displaying all nested subtotals | Excel Worksheet Functions | |||
Excel 2007 nested function | Excel Worksheet Functions | |||
Excel 2007 Nested Sumif's | Excel Worksheet Functions |