Simplify formula

[Iridium]

Its Monday morning and I haven't drunk enough coffee to get my brain functioning yet. My formula does what I want it to but its a bit unwieldy - any ideas to simplify?

=IF(OR(N20="",N21="",P20="",P21="",N26="",N27="",P26="",P27=""),"",IF(AND(N20="Pass",N21="Pass",P20="Pass",P21="Pass",N26="Pass",N27="Pass",P26="Pass",P27="Pass"),"Pass",IF(OR(N20="Second level testing",N21="Second level testing",P20="Second level testing",P21="Second level testing",N26="Second level testing",N27="Second level testing",P26="Second level testing",P27="Second level testing"),"Second level testing","Fail")))

 

[Andrew Poulsom]

You can use this normal formula:

=IF(COUNTIF(N20:P21,"")+COUNTIF(N26:P27,""),"",IF((COUNTIF(N20:P21,"Pass")+COUNTIF(N26:P27,"Pass"))=12,"Pass",IF(COUNTIF(N20:P21,"Second level testing")+COUNTIF(N26:P27,"Second level testing"),"Second level testing","Fail")))

or this array formula:

=IF(COUNT(IF(N20:P21,N26:P27="")),"",IF((COUNT(IF(N20:P21,N26:P27="Pass")))=12,"Pass",IF(COUNT(IF(N20:P21,N26:P27="Second level testing")),"Second level testing","Fail")))

remembering to press Ctrl+Shift+Enter not just enter.

 

[Iridium]

Thanks for that - knew there had to be an easier way. Cheers

 

[rsainath]

My query hopefully exlained well here:

Want to create a simple table for stock market calculations. As I progress creating formulas from one column to the next each formula is in some cases (not surprisingly) dependent on the results of the previous formula. That's OK if it's a simple table with few formulas. But as the "dependent" formulas get more complex that could potentially slow down the calculations. Regardless, after viewing the formulas, I replaced these formulas so they contain ONLY the INDEPENDENT variables and don't have to depend on "chain" results. But this replacement process could take forever in a large spreadsheet having complex formulas. Is there an easier way to do this replacement? Thanks people.
My google link for this simple example excel sheet is https://spreadsheets.google.com/ccc...uVEp1LS1Nbnp6TXc&hl=en&authkey=CLmlx_8B#gid=0

and shows three tables - the original "dependent" formulas, values and the revised formulas with INDEPENDENT variables. Hope you are able to see it.