Advanced Function help

[akemeny]

Ok... heres what I'm working with. I have a spreadsheet that contains links
to other spreadsheets. In this linked spreadsheet I also have one column
that is not linked (column BQ) which is where the function is going.

Here's where I get stumped. The current function that I'm using is:

=IF(X230="favorable","",
IF(BN230="","No",IF(OR(BN230="",BD230="overturned",AW230="overturned",AN230="overturned",AG230="overturned"),"No",IF(BN230<"0","Yes",""))))

What I need for the function to do is:
1. Look at columns BD, AW, AN, AG for the term "Overturned"
2. Look at column BN for a balance (will be a negative balance)
3. Change the cell value that contains the function to No if 1 is true and 2
is false
4. Change the cell value that contains the function to Yes if both are true
5. Leave the cell blank if it doesn't find Overturned in 1

Any help would be greatly appreciated.

 

[Don Guillett]

Assuming no intervening columns CAN contain the text, try this untested
=if(or(x230="favorable",countif(ag230:bd230,"overturned")<1),"",if(bn230<0,"yes","no"))

 

[John C]

I also left in the part in which if X230="favorable" then leave blank as
well, otherwise, this will work, though I am sure it could be shortened.

=IF(X230="favorable","",IF(OR(BD230="overturned",AW230="overturned",AN230="overturned",AG230="overturned"),IF(BN230<0,"Yes","No"),""))

 

[akemeny]

With either of the two functions that were given to me... where would I put
the following:

If BJ230="",""

So that it won't take into account the rows that do not contain blank values
for BJ

 

[John C]

I am assuming you misspoke when you said ..."So that it won't take into
account the rows that do not contain blank values for BJ"...
I am assuming that if BJ contains a blank value, you don't want the rest of
the formula to evaluate. My formula modified would be this...

=IF(OR(BJ230="",X230="favorable"),"",IF(OR(BD230="overturned",AW230="overturned",AN230="overturned",AG230="overturned"),IF(BN230<0,"Yes","No"),""))

Also, note: When posting a question, please post entire question, rather
than back and forth follow up. No where in your original question was any
regards to BJ230.

 

[Don Guillett]

Just add it into the OR part of the formula I sent