Help with multiple ISBlank and OR Statements

[tmpollard]

Hello,
I am hoping someone can help me with this formula, I can find a lot of info on the internet but not exactly what i'm looking for. Seems like only part of the formula works but not all of it. Thanks for any help.

I'm trying to put a formula for this in cell F2
If E2 is Blank and B2=2 then "Available" or if E2 is Blank and B2=3 then "Not Available" or if E2 is NOT Blank and B2=3 then "Available"

Not sure if I need and Else statement since the results should be either Available or Not Available.

This is what I came up with, but doesn't work
=IF(AND(B2=2,(ISBLANK(E2))),"Available",OR(AND(B2=3,(ISBLANK(E2))),"Not Available",OR(AND(B2=3,NOT(ISBLANK(E2))),"Available")))

Excel 2012

	A	B	C	D	E	F
1	System	Lvl	Top	Main Number	2nd Number	Status
2	Boiler	2	X	781402136191	781402136192	#VALUE!
3	Boiler	3	X	781402444391		#VALUE!
4	Boiler	3	X	781402731902	781402731997	#VALUE!
5	Boiler	2	X	781402731903		Available
6	Boiler	2	X	781402732002		Available
7	Boiler	3	X	781402732003		#VALUE!
8	Boiler	2	X	781402732602		Available
9	Boiler	3	X	781402732603	781402732698	#VALUE!
10	Boiler	2	X	781402912391		Available

<colgroup><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

 

[jtakw]

Hi,

This will do what you described. However, the first line of data, Row 2, doesn't match any of your criteria, so my formula leaves the F cell blank, what would you like happened in these cases?

Excel 2010
	A	B	C	D	E	F
1	System	Lvl	Top	Main Number	2nd Number	Status
2	Boiler	2	X	781402136191	781402136192
3	Boiler	3	X	781402444391		Not Available
4	Boiler	3	X	781402731902	781402731997	Available
5	Boiler	2	X	781402731903		Available
6	Boiler	2	X	781402732002		Available
7	Boiler	3	X	781402732003		Not Available
8	Boiler	2	X	781402732602		Available
9	Boiler	3	X	781402732603	781402732698	Available
10	Boiler	2	X	781402912391		Available
Sheet19

Cell Formulas
Range	Formula
F2	=IF(OR(AND(E2="",B2=2),AND(E2<>"",B2=3)),"Available",IF(AND(E2="",B2=3),"Not Available",""))

 

[Scott R]

=CHOOSE(B2 - 1, IF(E2 = "", "Available", ""), IF(E2 = "", "Not ", "") & "Available")

 

[tmpollard]

Thank you. In the case of Row 2 if you could put "I Don't Know" in the cell that would be an indicator that I need to investigate. Thanks

 

[jtakw]

Thank you. In the case of Row 2 if you could put "I Don't Know" in the cell that would be an indicator that I need to investigate. Thanks

You're welcome, updated formula below. You can change the "I Don't Know" to whatever you want:

Excel 2010
	A	B	C	D	E	F
1	System	Lvl	Top	Main Number	2nd Number	Status
2	Boiler	2	X	781402136191	781402136192	I Don't Know
3	Boiler	3	X	781402444391		Not Available
4	Boiler	3	X	781402731902	781402731997	Available
5	Boiler	2	X	781402731903		Available
6	Boiler	2	X	781402732002		Available
7	Boiler	3	X	781402732003		Not Available
8	Boiler	2	X	781402732602		Available
9	Boiler	3	X	781402732603	781402732698	Available
10	Boiler	2	X	781402912391		Available
Sheet19

Cell Formulas
Range	Formula
F2	=IF(OR(AND(E2="",B2=2),AND(E2<>"",B2=3)),"Available",IF(AND(E2="",B2=3),"Not Available","I Don't Know"))

 

[tmpollard]

Thank you all, I appreciate the quick help.

 

[jtakw]

You're welcome.