CODEWITHSUNDEEP
arraysexcel
ArthurV
ArthurV

Reputation: 123

Exclude zero values from array comparison

I've got this formula, which checks if there are any consecutive numbers within the range:

{=IF(SUM(--(ABS(C31:BI31-TRANSPOSE(C31:BI31))=1)),"Yes","No")}

It works perfectly, but I want to exclude zero values from conparison process.

4 6 2 8 0 9 - Result: "Yes"

0 1 7 5 9 2 - Result: "Yes", but it should be "No", since 0 and 1 should not be counted as consecutive numbers.

Upvotes: 0

Views: 85

Answers (2)

XOR LX
XOR LX

Reputation: 7762

=IF(SUMPRODUCT(COUNTIFS(C31:BI31,1+1/(1/C31:BI31))),"Yes","No")

Regards

Upvotes: 1

user4039065
user4039065

Reputation:

I'm not 100% sure that your original array formula is 'working perfectly'; you might want to double-check the results. Try this standard formula using the newer AGGREGATE function¹ to provide cyclic processing.

=IF(AGGREGATE(15, 6, ABS(C31:BH31-D31:BI31)/((C31:G31<>0)*(D31:BI31<>0)), 1)=1, "yes", "no")

¹ The AGGREGATE function was introduced with Excel 2010. It is not available in earlier versions.

Upvotes: 1

Related Questions