Excel tutorials

How to display TRUE if there is a sequence of three consecutive zeroes in the same row

This Excel example is based on the multiple variants of numbers in the same row. Our goal is to find a string of consecutive zero nuances in the same order and return TRUE. So we have one condition. We are ignoring the zeros that occur before the first number greater than zero.

In the image below, see the situation and variants of the values in columns 'A:J' for the same row. Columns 'L:P' are different formulas that return TRUE/FALSE, depending on the order value in the same row. Each of these formulas is based on certain conditions and returns the same or different result in relation to the other formulas. To make sure that we have the correct result for all variants, we can single out multiple formulas in one formula.
Thus the formula in the 'R' column uses the OR function =OR (formula1;formula2;formula3;formula4;formula5)

In the image, mark out the color-highlighted cells that have a sequence of three consecutive zeroes and our goal is to return TRUE for each row in which an uninterrupted sequence of 3 consecutive zeroes appears, but so we ignore the sequence of zeroes located before the first value that is greater than zero.

Counts null and returns TRUE if there is a string of 3 consecutive zeroes in the same row

Excel formula that finds three consecutive zeroes in a row and returns TRUE based on one condition

 

The ARRAY formula in 'L2' cell is the following. (This CSE formula counts all values are zero and are in continuous sequence, the formula returns TRUE only if the last value in the same row is not greater than zero, So here we have one condition. Notice that the formula has nested INDIRECT/LARGE functions.These two functions ensure that the final formula finds the last column in which a value higher than zero is displayed and counts in a sequential sequence after the last cell with a value greater than zero from the same row. Specifically, the LARGE CSE formula below, finds the last column with a number greater than zero which is in the order of the seventh column and when we add CHAR(64), the result is the 'G' column. The rest of the INDIRECT formula returns the cell range 'G2:J2'. The COUNTIF function counts how much zero is in the range of the given cell range and if the same finds three or more successive zeroes in the sequence as a result, the TRUE returns. The IFERROR function returns FALSE if the Countif function returns the error or FALSE).
=IFERROR(COUNTIF(INDIRECT(CHAR(64+LARGE(IF(A2:J2>0;COLUMN(A2:J2));1))&ROW(A2)&":J"&ROW(A2);TRUE);"=0")>=3;FALSE)

Counting of a successive string of Nth values after the second last value is greater than zero

The ARRAY formula in 'M2' cell is the following. (This CSE formula is the same as the previous one and works the same way but is based on a 2nd last value greater than zero)
=IFERROR(COUNTIF(INDIRECT(CHAR(64+LARGE(IF(A2:J2>0;COLUMN(A2:J2));2))&ROW(A2)&":J"&ROW(A2);TRUE);"=0")>=3;FALSE)

How to count a sequence of 3 consecutive zero after the third last value is greater than zero

The ARRAY formula in 'N2' cell is the following. (This CSE formula is the same as the previous one and works in the same way but is based on a value greater than zero, before the 3rd last value which is different from zero)
=IFERROR(COUNTIF(INDIRECT(CHAR(64+LARGE(IF(A2:J2>0;COLUMN(A2:J2));3))&ROW(A2)&":J"&ROW(A2);TRUE);"=0")>=3;FALSE)

Count three consecutive values in continuous sequence between two values greater than zero in the same row

The ARRAY formula in 'O2' cell is the following. (This SUM CSE formula is based on the exact number of sequential cells in the sequence. Thus, this formula looks exclusively at the appearance of three zeroes in a row between two values greater than zero. When the formula finds three consecutive zeroes, between two values that are larger than zero then returns TRUE as a result. The basis of this formula is the FREQUENCY function, which uses the COLUMN function).
=SUM(IF(FREQUENCY(IF(A2:J2=0;COLUMN(A2:J2));IF(A2:J2>0;COLUMN(A2:J2)))=3;1))>0

Counting consecutive series Nth value greater than zero in the same row

The ARRAY formula in 'P2' cell is the following. (This CSE formula counts the consecutive zeroes that appear between values that are not zero. Thus MAX formula returns the expression =MAX({2;1;0;0;3})=3 in a certain calculation step. Use the Evaluate formula and the 'F9' key to see this step of calculation. Note in the figure the number of zero impressions, between two values greater than zero. And finally if the value is '=3' in the range, returns TRUE as the result).
=MAX(FREQUENCY(IF(A2:J2=0;COLUMN(A2:J2));IF(A2:J2<>0;COLUMN(A2:J2))))=3

How to return TRUE if there are three consecutive zeroes in the same row

The final ARRAY formula in the 'R2' cell is below: (This formula uses all four previous formulas to ensure multiple variants of all values in the same row.
=OR(
IFERROR(COUNTIF(INDIRECT(CHAR(64+LARGE(IF(A2:J2>0;COLUMN(A2:J2));1))&ROW(A2)&":J"&ROW(A2);TRUE);"=0")>=3;FALSE);
IFERROR(COUNTIF(INDIRECT(CHAR(64+LARGE(IF(A2:J2>0;COLUMN(A2:J2));2))&ROW(A2)&":J"&ROW(A2);TRUE);"=0")>=3;FALSE);
IFERROR(COUNTIF(INDIRECT(CHAR(64+LARGE(IF(A2:J2>0;COLUMN(A2:J2));3))&ROW(A2)&":J"&ROW(A2);TRUE);"=0")>=3;FALSE);
SUM(IF(FREQUENCY(IF(A2:J2=0;COLUMN(A2:J2));IF(A2:J2>0;COLUMN(A2:J2)))=3;1))>0;
MAX(FREQUENCY(IF(A2:J2=0;COLUMN(A2:J2));IF(A2:J2<>0;COLUMN(A2:J2))))=3
)

Please note that you must correctly set absolute and relative addresses in formulas, especially if you copy the formula.
Finally, in the attachment of this tutorial, you have the Download file option on which I did this tutorial.

Pay attention, you may need as a delimiter of arguments formula use a comma (,) instead of a semicolon (;).
Copy formula to Notepad and with Find/Replace change semicolon to comma.