Understanding a formula

[Ian Betteridge]

Hello All,
I'm trying to understand a spreadsheet I'm working with and have come across quite a long formula. I'm sure broken down it can be explained as to what it's doing and what are the possible results. Here it is and this is how I think it should be split up..
If possible could someone please put me right and give a brief explanation on each step and its possible result.
Greatly Appreciated Ian

=IF(AND(OR(AND(O$20=TRUE,O41="x"),AND(P$20=TRUE,P41="x"),AND(Q$20=TRUE,Q41="x")),OR(AND(R$20=TRUE,R41="x"),AND(S$20=TRUE,S41="x"),AND(T$20=TRUE,T41="x"))),"Yes",IF(AND(OR(AND(O$20=TRUE,O41="z"),AND(P$20=TRUE,P41="z"),AND(Q$20=TRUE,Q41="z")),OR(AND(R$20=TRUE,R41="x"),AND(S$20=TRUE,S41="x"),AND(T$20=TRUE,T41="x"))),"Yes/No",IF(AND(OR(AND(O$20=TRUE,O41="x"),AND(P$20=TRUE,P41="x"),AND(Q$20=TRUE,Q41="x")),OR(AND(R$20=TRUE,R41="z"),AND(S$20=TRUE,S41="z"),AND(T$20=TRUE,T41="z"))),"Yes/No",IF(AND(OR(AND(O$20=TRUE,O41="z"),AND(P$20=TRUE,P41="z"),AND(Q$20=TRUE,Q41="z")),OR(AND(R$20=TRUE,R41="z"),AND(S$20=TRUE,S41="z"),AND(T$20=TRUE,T41="z"))),"Yes/No","No"))))

Broken down...........?
=IF(AND(OR(AND(O$20=TRUE,O41="x"),
AND(P$20=TRUE,P41="x"),
AND(Q$20=TRUE,Q41="x")),
OR(AND(R$20=TRUE,R41="x"),
AND(S$20=TRUE,S41="x"),
AND(T$20=TRUE,T41="x"))),"Yes",
IF(AND(OR(AND(O$20=TRUE,O41="z"),
AND(P$20=TRUE,P41="z"),
AND(Q$20=TRUE,Q41="z")),
OR(AND(R$20=TRUE,R41="x"),
AND(S$20=TRUE,S41="x"),
AND(T$20=TRUE,T41="x"))),"Yes/No",
IF(AND(OR(AND(O$20=TRUE,O41="x"),
AND(P$20=TRUE,P41="x"),
AND(Q$20=TRUE,Q41="x")),
OR(AND(R$20=TRUE,R41="z"),
AND(S$20=TRUE,S41="z"),
AND(T$20=TRUE,T41="z"))),"Yes/No",
IF(AND(OR(AND(O$20=TRUE,O41="z"),
AND(P$20=TRUE,P41="z"),
AND(Q$20=TRUE,Q41="z")),
OR(AND(R$20=TRUE,R41="z"),
AND(S$20=TRUE,S41="z"),
AND(T$20=TRUE,T41="z"))),"Yes/No",
"No"))))

 

[number kruncher]

With no context to the references, it would be impossible to explain this formulae, however, to understand it, you have already taken steps in this by breaking the formula down.

I would keep it in sections, eg.
=IF(AND(OR(AND(O$20=TRUE,O41="x"),AND(P$20=TRUE,P41="x"),AND(Q$20=TRUE,Q41="x")),OR(AND(R$20=TRUE,R41="x"),AND(S$20=TRUE,S41="x"),AND(T$20=TRUE,T41="x"))),"Yes"

IF(AND(OR(AND(O$20=TRUE,O41="z"),AND(P$20=TRUE,P41="z"),AND(Q$20=TRUE,Q41="z")),OR(AND(R$20=TRUE,R41="x"),AND(S$20=TRUE,S41="x"),AND(T$20=TRUE,T41="x"))),"Yes/No",

IF(AND(OR(AND(O$20=TRUE,O41="x"),AND(P$20=TRUE,P41="x"),AND(Q$20=TRUE,Q41="x")),OR(AND(R$20=TRUE,R41="z"),AND(S$20=TRUE,S41="z"),AND(T$20=TRUE,T41="z"))),"Yes/No",

IF(AND(OR(AND(O$20=TRUE,O41="z"),AND(P$20=TRUE,P41="z"),AND(Q$20=TRUE,Q41="z")),OR(AND(R$20=TRUE,R41="z"),AND(S$20=TRUE,S41="z"),AND(T$20=TRUE,T41="z"))),"Yes/No","No"

However, as the results for section 2, 3 and 4 give the same resuilt, it seems wholly over complicated.

HTH