Need help with formula that ignores blank cells

[Russk68]

I need a formula that results in TRUE or FALSE.
If the numbers in BO167:BZ167 are in sequential order without skipping numbers then TRUE.
The formula I have only works if all cells have values. The cells with no values are "" from the result of a formula.

Distro Canelo.xlsx
	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA
166
167	876	877	878	879	880	881	882	883	884	885	886	887	TRUE
168	876	877	878	879	880	881	882	883	884	872	886	887	FALSE
169
170	879	880	881										#VALUE!
171
Untitled Worksheet-2

 

[awoohaw]

not really sure if this is what you want, but it seems to work:

Mr excel questions 35.xlsm
	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA
167	876	877	878	879	880	881	882	883	884	885	886	887		876	877	878	879	880	881	882	883	884	885	886	887	TRUE
168	876	877	878	879	880	881	882	883	884	872	886	887		876	877	878	879	880	881	882	883	884	872	886	887	FALSE
169																										FALSE
170	879	880	881											879	880	881										FALSE
171																										FALSE
Russk68

Cell Formulas
Range	Formula
BO167:BZ171	BO167	=IF(BB167=0,"",BB167)
CA167:CA171	CA167	=AND( SUM(--(IF(ISNUMBER(BO167:BY167)=FALSE,0,BO167:BY167)+1=IF(ISNUMBER(BP167:BZ167)=FALSE,0,BP167:BZ167)))=COUNTA(BO167:BY167), COUNTA(BO167:BY167)-COUNT(BO167:BY167)=0)

 

[Russk68]

Hi Awoohaw,
Very close. CA170 would be TRUE since the 3 numbers don't skip

 

[awoohaw]

Hi Awoohaw,
Very close. CA170 would be TRUE since the 3 numbers don't skip

I thought that may be what you wanted, but that was not stated as an expected outcome. So I did what you asked. Since BR = BQ+1 is false.

 

[Russk68]

Would you be so kind to make the change?

 

[awoohaw]

Of course:

Mr excel questions 35.xlsm
	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA
167	876	877	878	879	880	881	882	883	884	885	886	887		876	877	878	879	880	881	882	883	884	885	886	887	TRUE
168	876	877	878	879	880	881	882	883	884	872	886	887		876	877	878	879	880	881	882	883	884	872	886	887	TRUE
169																										FALSE
170	879	880	881											879	880	881										TRUE
171																										FALSE
Russk68

Cell Formulas
Range	Formula
BO167:BZ171	BO167	=IF(BB167=0,"",BB167)
CA167:CA171	CA167	=((ISNUMBER(MATCH(FALSE,DROP(FILTER(BO167:BZ167,ISNUMBER(BO167:BZ167)),-1)+1 = DROP(FILTER(BO167:BZ167,ISNUMBER(BO167:BZ167)),1),0))=FALSE) * (--(COUNT(FILTER(BO167:BZ167,ISNUMBER(BO167:BZ167)))>0)))>0

 

[awoohaw]

i did, but had an error.

 

[awoohaw]

ok, i think this has it:

Mr excel questions 35.xlsm
	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA
167	876	877	878	879	880	881	882	883	884	885	886	887	TRUE
168	876	877	878	879	880	881	882	883	884	872	886	887	FALSE
169													FALSE
170	879	880	881										TRUE
171													FALSE
Russk68

Cell Formulas
Range	Formula
BO167:BZ171	BO167	=IF(BB167=0,"",BB167)
CA167:CA171	CA167	=((ISNUMBER(MATCH(FALSE,DROP(FILTER(BO167:BZ167,ISNUMBER(BO167:BZ167)),,-1)+1 = DROP(FILTER(BO167:BZ167,ISNUMBER(BO167:BZ167)),,1),0))=FALSE) * (--(COUNT(FILTER(BO167:BZ167,ISNUMBER(BO167:BZ167)))>0)))>0

 

[Russk68]

Yup, that works!
Thank you very much!!!!!!!

 

[awoohaw]

your welcome. and I'm happy you found you solution here.

Best wishes!