Nested Ifs in 2007

[NHMcCormick07]

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!

 

[John C]

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(COUNTIF(D47,"Yes"),Sheet2!$A$2:$B$6,2,FALSE)

If you run into trouble, look for help on the VLOOKUP function, or post back.

 

[Shane Devenshire]

Hi,

Try this:

=VLOOKUP(COUNTIF(A1:A4,"no"),Sheet2!$K$1:$L$5,2,FALSE)

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]

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'

 

[Ron Rosenfeld]

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