Help with multiple ISBlank and OR Statements

T

tmpollard

Board Regular
Joined
Jun 12, 2008
Messages
111
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
ABCDEF

<colgroup><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #0B64A0"]System[/TD]
[TD="bgcolor: #0B64A0"]Lvl[/TD]
[TD="bgcolor: #0B64A0"]Top[/TD]
[TD="bgcolor: #0B64A0"]Main Number[/TD]
[TD="bgcolor: #0B64A0"]2nd Number[/TD]
[TD="bgcolor: #0B64A0"]Status[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8FBFC]#F8FBFC[/URL] "]Boiler[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8FBFC]#F8FBFC[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8FBFC]#F8FBFC[/URL] "]X[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8FBFC]#F8FBFC[/URL] , align: center"]781402136191[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8FBFC]#F8FBFC[/URL] , align: center"]781402136192[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8FBFC]#F8FBFC[/URL] , align: right"]#VALUE![/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8FBFC]#F8FBFC[/URL] "]Boiler[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]X[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]781402444391[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8FBFC]#F8FBFC[/URL] , align: right"]#VALUE![/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8FBFC]#F8FBFC[/URL] "]Boiler[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8FBFC]#F8FBFC[/URL] , align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8FBFC]#F8FBFC[/URL] "]X[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8FBFC]#F8FBFC[/URL] , align: center"]781402731902[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8FBFC]#F8FBFC[/URL] , align: center"]781402731997[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8FBFC]#F8FBFC[/URL] , align: right"]#VALUE![/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8FBFC]#F8FBFC[/URL] "]Boiler[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]X[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]781402731903[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8FBFC]#F8FBFC[/URL] "]Available[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8FBFC]#F8FBFC[/URL] "]Boiler[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8FBFC]#F8FBFC[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8FBFC]#F8FBFC[/URL] "]X[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8FBFC]#F8FBFC[/URL] , align: center"]781402732002[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8FBFC]#F8FBFC[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8FBFC]#F8FBFC[/URL] "]Available[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8FBFC]#F8FBFC[/URL] "]Boiler[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]X[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]781402732003[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8FBFC]#F8FBFC[/URL] , align: right"]#VALUE![/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8FBFC]#F8FBFC[/URL] "]Boiler[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8FBFC]#F8FBFC[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8FBFC]#F8FBFC[/URL] "]X[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8FBFC]#F8FBFC[/URL] , align: center"]781402732602[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8FBFC]#F8FBFC[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8FBFC]#F8FBFC[/URL] "]Available[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8FBFC]#F8FBFC[/URL] "]Boiler[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]X[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]781402732603[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]781402732698[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8FBFC]#F8FBFC[/URL] , align: right"]#VALUE![/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8FBFC]#F8FBFC[/URL] "]Boiler[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8FBFC]#F8FBFC[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8FBFC]#F8FBFC[/URL] "]X[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8FBFC]#F8FBFC[/URL] , align: center"]781402912391[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8FBFC]#F8FBFC[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8FBFC]#F8FBFC[/URL] "]Available[/TD]

</tbody>
Sheet1​
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
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
ABCDEF
1SystemLvlTopMain Number2nd NumberStatus
2Boiler2X781402136191781402136192 
3Boiler3X781402444391Not Available
4Boiler3X781402731902781402731997Available
5Boiler2X781402731903Available
6Boiler2X781402732002Available
7Boiler3X781402732003Not Available
8Boiler2X781402732602Available
9Boiler3X781402732603781402732698Available
10Boiler2X781402912391Available
Sheet19
Cell Formulas
RangeFormula
F2=IF(OR(AND(E2="",B2=2),AND(E2<>"",B2=3)),"Available",IF(AND(E2="",B2=3),"Not Available",""))
 
Upvote 0
=CHOOSE(B2 - 1, IF(E2 = "", "Available", ""), IF(E2 = "", "Not ", "") & "Available")
 
Upvote 0
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
 
Upvote 0
tmpollard said:
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
Click to expand...

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


Excel 2010
ABCDEF
1SystemLvlTopMain Number2nd NumberStatus
2Boiler2X781402136191781402136192I Don't Know
3Boiler3X781402444391Not Available
4Boiler3X781402731902781402731997Available
5Boiler2X781402731903Available
6Boiler2X781402732002Available
7Boiler3X781402732003Not Available
8Boiler2X781402732602Available
9Boiler3X781402732603781402732698Available
10Boiler2X781402912391Available
Sheet19
Cell Formulas
RangeFormula
F2=IF(OR(AND(E2="",B2=2),AND(E2<>"",B2=3)),"Available",IF(AND(E2="",B2=3),"Not Available","I Don't Know"))
 
Upvote 0
You must log in or register to reply here.

Similar threads

Z
  • Solved
COUNTIFS to Exclude Blanks that contain formulas
Replies
3
Views
706
Alex Blakenburg
A
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
1K
montex01
M
MrDB4Excel
  • Question Question
If Find Blank Cell
Replies
0
Views
696
MrDB4Excel
MrDB4Excel
J
  • Question Question
Need proper formula for max
2
Replies
15
Views
1K
jackt05
J
BradleyS
  • Solved
Sum duration values only if -3
Replies
6
Views
1K
Oaktree
Oaktree

Forum statistics

Threads
1,223,908
Messages
6,175,304
Members
452,633
Latest member
DougMo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top