Gerald's right, that formula is a bit of a mess!

However, it's entirely possible to fix it up, if you want to spend the time doing it. But quite often it's easier to step back and look at what you really want to do. As far as I can tell from your description, you are looking for certain patterns of numbers. Specifically, you want to find a zero between 2 non-zero values. So my first approach was to use a function to find the first non-zero (or 1 for ease of description), then find the last 1, then do a COUNTIF to see if there are any zeros between them.

| A | B | C | D | E | F | G | H | I |
---|

1 | A | B | C | D | Returning? | | Returning | Patterns | Decimal equivalent |

2 | 1 | 0 | 1 | 1 | TRUE | | TRUE | 0000 | |

3 | 1 | 0 | 1 | 0 | TRUE | | TRUE | 0001 | |

4 | 1 | 1 | 0 | 1 | TRUE | | TRUE | 0010 | |

5 | 0 | 1 | 1 | 0 | FALSE | | FALSE | 0011 | |

6 | 0 | 1 | 1 | 1 | FALSE | | FALSE | 0100 | |

7 | 0 | 0 | 1 | 0 | FALSE | | FALSE | 0101 | 5 |

8 | | | | | FALSE | | FALSE | 0110 | |

9 | | | | | FALSE | | FALSE | 0111 | |

10 | | | | | FALSE | | FALSE | 1000 | |

11 | | | | | FALSE | | FALSE | 1001 | 9 |

12 | | | | | | | | 1010 | 10 |

13 | | | | | | | | 1011 | 11 |

14 | | | | | | | | 1100 | |

15 | | | | | | | | 1101 | 13 |

16 | | | | | | | | 1110 | |

17 | | | | | | | | 1111 | |

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>

</thead><tbody>

</tbody>

**Sheet17**

**Array Formulas**
Cell | Formula |
---|
E2 | {=IFERROR(COUNTIF(OFFSET(A2,0,MIN(IF(A2:D2>0,COLUMN(A2:D2)))-1):OFFSET(A2,0,MAX(IF(A2:D2>0,COLUMN(A2:D2)))-1),0)>0,FALSE)} |
---|
G2 | {=ISNUMBER(MATCH(SUM((A2:D2>0)*(2^(4-COLUMN(A2:D2)))),{5,9,10,11,13},0))} |
---|
<thead>
</thead><tbody>
</tbody> **Entered with Ctrl+Shift+Enter.** If entered correctly, Excel will surround with curly braces {}.
**Note: Do not try and enter the {} manually yourself** |

<tbody>

</tbody>

That's the formula in E2, which must be confirmed with Control+Shift+Enter. It's quite a bit shorter than your original formula, but depending on how comfortable you are with the additional functions, and array formulas in general, it might be harder to understand.

Approach 2 yields an even shorter formula, but might be even more arcane if you're not familiar with binary counting. In column H I listed all the possible combinations of values in A:D. In column I, I picked out the ones that match your requirements and put the decimal equivalent. Then in G2 I created a formula that converts the pattern in A:D to the decimal equivalent, then check to see if that number is one of the numbers I found from column I. Also an array formula.

One final option. Not an array formula, no complicated formulas:

E2: =OR(AND(A2>0,D2>0,OR(B2=0,C2=0)),AND(A2=0,B2>0,C2=0,D2>0),AND(A2>0,B2=0,C2>0))

Using the patterns from column H, I looked for the patterns from the matching values, and came up with this version.

Lots of options, let me know if any of them work for you. These will all be a bit longer once you add your sheet references.