Attempting to use SCAN() to check if consecutive cells are equal.

[Cubist]

Book3
	A	B	C	D	E	F	G
1	1	2	1	6	6	1	1
2
3	Expected	FALSE	FALSE	FALSE	TRUE	FALSE	TRUE
4	Formula	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
Sheet4

Cell Formulas
Range	Formula
B4:G4	B4	=SCAN(A1,B1:G1,LAMBDA(a,b,a=b))
Dynamic array formulas.

I'm attempting to use SCAN() to check if consecutive cells are equal but it's not giving the expected result. Perhaps I'm misusing the function. Any thoughts?

 

[ExcelToDAX]

Thats because SCAN is including all of the previous values. If you change it to a+b, you will notice that it is summing everything previously. You can use this formula instead:

=DROP(A1:G1=OFFSET(A1:G1,,1),,-1)

 

[Fluff]

You cannot use scan like that. How about

=B1:G1=A1:F1

 

[Cubist]

That's neat.

 

[Cubist]

Thats because SCAN is including all of the previous values. If you change it to a+b.

I was under the impression that SCAN acts as an iterator, not an accumulator.

Only when you specify a+b, then it is an accumulator.

 

[Fluff]

In your formula "a" starts off as the value in A1, it then becomes the result of a=b (in this case FALSE) so on the 2nd iteration a=b which is FALSE=C1

 

[Cubist]

Makes sense now. It acts as an accumulator in the sense that it stores the result of the prior iterations. Thanks.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Cubist]

@Fluff , in your other post, you have used =SCAN(,....) where leaving the first parameter empty. What's the default value? Is it 0 or "" or it automatically detect based on the data type of the array (2nd) parameter?

When entering the function, the parameter assistance doesn't have the square brackets [] to indicate it's an optional parameter. I was surprised it didn't know an error for missing parameters.

 

[Fluff]

In that post the initial value is irrelevant as the array has a value in the first cell.