If statement/and statement with 4 possible results with numeric range

[ramosca]

Hi, I am working on a report and need some assistance. I am working with Windows XP Professional and Excel 2007.

My report is looking for a formula that will reflect if the goal is in jeopardy of being missed.

If cell A2 = "COMP" the result should be "NO"
If cell A2 = "PENDING" and cell B2 is any number between 0 thru 9 the result should be "PENDING"
If cell A2 = "PENDING" and cell B2 is any number between 10 thru 15 the result should be "YES"
If cell A2 = "PENDING" and cell B2 is greater or equal to 16 the result should be "FAIL"

Any help would be GREATLY appreciated!!

 

[Jeff Meyers]

Here's an approach:

Note that you did not specify if Col A equals anything else (i.e. other than COMP or PENDING) so this approach does not test for that. It assumes that Col A will only be COMP or PENDING and so the formula only tests for COMP, and if it isn't COMP then it assumes it to be PENDING by default.

Sheet5[TABLE="class: html-maker-worksheet"]
<tbody>[TR]
[TH][/TH]
[TH]A
[/TH]
[TH]B
[/TH]
[TH]C
[/TH]
[/TR]
[TR]
[TH]1
[/TH]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]2
[/TH]
[TD]COMP
[/TD]
[TD="align: right"]5
[/TD]
[TD]NO
[/TD]
[/TR]
[TR]
[TH]3
[/TH]
[TD]PENDING
[/TD]
[TD="align: right"][/TD]
[TD]PENDING
[/TD]
[/TR]
[TR]
[TH]4
[/TH]
[TD]PENDING
[/TD]
[TD="align: right"]11
[/TD]
[TD]YES
[/TD]
[/TR]
[TR]
[TH]5
[/TH]
[TD]PENDING
[/TD]
[TD="align: right"]15
[/TD]
[TD]YES
[/TD]
[/TR]
[TR]
[TH]6
[/TH]
[TD]PENDING
[/TD]
[TD="align: right"]16
[/TD]
[TD]FAIL
[/TD]
[/TR]
[TR]
[TH]7
[/TH]
[TD]PENDING
[/TD]
[TD="align: right"]25
[/TD]
[TD]FAIL
[/TD]
[/TR]
[TR]
[TH]8
[/TH]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]9
[/TH]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]10
[/TH]
[TD]----------------
[/TD]
[TD]----------
[/TD]
[TD]----------------
[/TD]
[/TR]
[TR]
[TH]11
[/TH]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Excel 2010

Cell	Formula
C2	=IF(A2="COMP","NO",LOOKUP(B2,{0,10,16},{"PENDING","YES","FAIL"}))
C3	=IF(A3="COMP","NO",LOOKUP(B3,{0,10,16},{"PENDING","YES","FAIL"}))
C4	=IF(A4="COMP","NO",LOOKUP(B4,{0,10,16},{"PENDING","YES","FAIL"}))
C5	=IF(A5="COMP","NO",LOOKUP(B5,{0,10,16},{"PENDING","YES","FAIL"}))
C6	=IF(A6="COMP","NO",LOOKUP(B6,{0,10,16},{"PENDING","YES","FAIL"}))
C7	=IF(A7="COMP","NO",LOOKUP(B7,{0,10,16},{"PENDING","YES","FAIL"}))

<tbody>
[TD="bgcolor: #FFFFFF"]
Worksheet Formulas [TABLE="class: html-maker-worksheet"]
<tbody>[TR]

</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Maybe

=IF(A2="COMP","No",IF(A2="PENDING",LOOKUP(B2,{0;10;16},{"PENDING";"YES";"FAIL"}),""))

M.

 

[ramosca]

Thanks guys so much, both work amazingly!!

 

[Jeff Meyers]

Happy to help. Glad that it worked out for you and thank you for the feedback.