Need Help with Or And Statement

[samsilverman]

I need my spreadsheet to return a true or false ("" or *) based whether one AND statement exists OR another AND statement exists. It really all hinges on whether cell M17 is blank or has a value placed in it. Here's my current formula that is not working. Any help is greatly appreciated.

I've color coded the two different AND statements I need excel to consider. The only difference between the BLACK and RED is whether there is a value for cell M17.

Right now, if I leave M17 blank it returns a false (*). But, I only need it to consider cell M17 if there is a value placed there. If M17 is blank, but L17 falls within the parameters, I need the formula to ignore M17 and return a true ("").

=IF(AND($I8="AIRBORNE INFECTION ISOLATION - 6",L17<=-0.001,M17<=-0.001,(F17+G17+F18+G18)<((I17+J17+I18+J18)*-1))," ",IF(AND($I8="AIRBORNE INFECTION ISOLATION - 12",L17<=-0.01,M17<=-0.01,(F17+G17+F18+G18)<((I17+J17+I18+J18)*-1))," ",IF(AND($I8="PROTECTIVE ENVIRONMENT",L17>=0.01,M17>=0.01,(F17+G17+F18+G18)>((I17+J17+I18+J18)*-1))," ",IF(AND($I8="(NEG) AII - IMMUNE COMPROMISED",L17<=-0.01,M17<=-0.01,(F17+G17+F18+G18)<((I17+J17+I18+J18)*-1))," ",IF(AND($I8="(POS) AII - IMMUNE COMPROMISED",L17>=0.01,M17>=0.01,(F17+G17+F18+G18)>((I17+J17+I18+J18)*-1))," ",IF(AND($I8="AIRBORNE INFECTION ISOLATION - 6",L17<=-0.001,M17=" ",(F17+G17+F18+G18)<((I17+J17+I18+J18)*-1))," ",IF(AND($I8="AIRBORNE INFECTION ISOLATION - 12",L17<=-0.01,M17=" ",(F17+G17+F18+G18)<((I17+J17+I18+J18)*-1))," ",IF(AND($I8="PROTECTIVE ENVIRONMENT",L17>=0.01,M17=" ",(F17+G17+F18+G18)>((I17+J17+I18+J18)*-1))," ",IF(AND($I8="(NEG) AII - IMMUNE COMPROMISED",L17<=-0.01,M17=" ",(F17+G17+F18+G18)<((I17+J17+I18+J18)*-1))," ",IF(AND($I8="(POS) AII - IMMUNE COMPROMISED",L17>=0.01,M17=" ",(F17+G17+F18+G18)>((I17+J17+I18+J18)*-1))," ","*")))))))))))

 

[henrik2h]

Having such a formula in one single cells makes it virtually impossible to follow and if you don't work on it for a month you will forget what it means.

Nevertheless, couldn't you use a simple if-statement in beginning (since you seem to like them) e.g. =if(isnumber(m17); if statement for m17 etc; if statement for L17)

 

[samsilverman]

henrik2h -

Thank you for you suggestion. I will try it. I realize my formula could possibly be replaced with a vlookup or something else, but I am a real novice and haven't progressed yet past the if statements.

Again, thank you for your suggestion.

sam

 

[samsilverman]

Couldn't get it to work. Maybe I am inserting it incorrectly. If anyone has further assistance it would be very helpful. Thank you.

 

[samsilverman]

I have since figured it out.

 

[Ruddles]

As you say you're 'a real novice', perhaps I can offer a suggestion?

Don't feel it's always necessary to do everything in a single cell. Excel has more columns than you can shake a stick at, so if you're testing multiple or complex conditions of data in columns A to Z, for example, break those multiple or complex conditions up into smaller, simpler formulae which you can place in 'helper' columns over to the right of your main block of data, then test those helper columns to get to the value you're trying to calculate.

Am I making sense?

Smaller, simpler formulae are easier to understand and therefore modify, and instead of staring at a huge block of formula code which isn't returning the value you were expecting and having no idea where to start searching, you can look at those helper columns with their shorter, simpler formulae and see which one isn't working properly, then concentrate on that.

I've been using Excel for years and whenever a formula has much more than about five or six pairs of brackets in it, I'm already looking to break it up into smaller, more manageable parts. Yours had over a hundred brackets in it: if you managed to sort that lot out on your own, well done you!

 

[samsilverman]

Thanks for the advice. Not quite sure how to do that in my current database for our company. I feel like we're somewhat bound by design issues in how the reports are produced and my lack of knowledge. BUT, I am always working on this Frankenstein. So, I appreciate the time you took to comment.