testing if a range of cells are the same

[stuck]

The array formula:
{=AND(EXACT(I11:I15,I11))}
is TRUE if all cells in the range are the same. So far so good but the range I want to test is not a fixed range. It could cover anything from the single cell I11:I11 to the thirteen cells I11:I23.

Any suggestion how I can do this please?

Ken

[HansV]

You don't provide any clue to what you want to test. The contiguous non-blank range from I11 down? Or something else?

[stuck]

Sorry, I mean the contiguous non-blank range from I11 down. These cells are either populated with a string or are empty (i.e. "").
Thus in the simplest case I want a formula that equates to:
{=AND(EXACT(I11:I11,I11))}
but the range could be:
{=AND(EXACT(I11:I12,I11))}
{=AND(EXACT(I11:I13,I11))}
up to:
{=AND(EXACT(I11:I23,I11))}

Ken

[HansV]

Is I24 empty? (I ask because we need to find the first blank cell)

[HansV]

Never mind, try this array formula (as always confirmed with Ctrl+Shift+Enter):

=AND(EXACT(OFFSET(I11,0,0,MAX((I11:I23<>"")*ROW($1:$13)),1),I11))

[stuck]

Sorry for the slow response. Yes, I24 is blank.

However, I've got something that (so far ) is working the array formula:
{=AND(I11=OFFSET(I11,0,0,COUNT(C11:C23),1))}

OFFSET(I11,0,0,COUNT(C11:C23),1) returns the dynamic range I want
AND(I11=the dynamic range I want) returns true if I11 is the same as all the other cells in the dynamic range

That's akin to your array formula, which one do you think is more robust?

Ken

[stuck]

To answer my own question. Your formula doesn't rely on cells outside the dynamic range, so is more self contained. That might make it 'better'.

I'll try both and see which version I can break most quickly

As ever,

Ken

[HansV]

Your original formula performed a case-sensitive comparison: "stuck" would not be considered to be the same as "Stuck".
Your new formula is case-insensitive. The equivalent version of the formula I proposed would be

=AND(OFFSET(I11,0,0,MAX((I11:I23<>"")*ROW($1:$13)),1)=I11)

[stuck]

...case-sensitive... ...case-insensitive...

again, hadn't appreciated that subtlety. Don't think it's important though in my case, he's says and thus making it inevitable that it will be important.

Ken

[HansV]

Don't think it's important though in my case

Is that lower case?

[stuck]

Is that lower case?

You know me, I get stuck in all sorts of ways.

Ken