New uses for old functions

[Peter_SSs]

"I needed to determine if all the values ... are the same... I don't really care what the value is, so long as they're all equal or empty"

None of the suggestions worked correctly for me.
(A1:A9 = "a", A10 = "b")

So maybe not what the thread is about, but for this job I would propose another possibility
=COUNTA(A1:A10)=COUNTIF(A1:A10,LOOKUP(2,1/(1-ISBLANK(A1:A10)),A1:A10))

This seems to work for numeric, text and also error values.

To try to add something that is relevant to the topic, perhaps this for checking if a value (given the above I better specify numerical ) lies within a given range (including endpoints) or not

=MEDIAN(A1,100,200) = A1

 

[DonkeyOte]

Similar to Peter's but not as robust (underlying errors)

=SUMPRODUCT((SUBSTITUTE(A1:A10,A1,"",1)="")+0)=ROWS(A1:A10)

I'm interested in finding new ways to use functions in ways possibly not intended.

SUMPRODUCT is the classic case - compare Help File examples to 95% of on line examples.

 

[Peter_SSs]

Similar to Peter's but not as robust (underlying errors)

=SUMPRODUCT((SUBSTITUTE(A1:A10,A1,"",1)="")+0)=ROWS(A1:A10)

It also gives the wrong answer (False) if A1 is blank and any non-blank cells are all equal.

 

[DonkeyOte]

Fair dinkum...

=SUMPRODUCT((SUBSTITUTE(A1:A10,LOOKUP(2,1/(A1:A10<>""),A1:A10),"",1)="")+0)=ROWS(A1:A10)

but obviously this is even more ridiculous than before and still doesn't account for errors

=SUM((FREQUENCY(IF(A1:A10<>"",MATCH(A1:A10,A1:A10,0)),ROW(A1:A10)-ROW(A1))>0)+0)<2

but again would not account for errors and is arguably being used as intended

note to self: don't post within 15 minutes of waking up...

 

[Peter_SSs]

note to self: don't post within 15 minutes of waking up...

It's over half an hour so you're fair game again now!

The first one also errors when the range is empty (refer post #10).

The second seems to error if A1 is empty no matter what is in the rest of the range.

 

[DonkeyOte]

I failed to point out that the second is an Array though still not as robust as your earlier suggestion.

 

[UniMord]

=COUNTA(A1:A10)=COUNTIF(A1:A10,LOOKUP(2,1/(1-ISBLANK(A1:A10)),A1:A10))

Peter,

That is one cool and robust formula! I'm popping that one straight into my bag of tricks!

Is there a special reason why you chose 2 as LOOKUP's first argument, rather than 1?

 

[Jon von der Heyden]

Ignore...

 

[DonkeyOte]

1 would suffice but 2 is conventional - more "transparent"

 

[PaddyD]

"How does that improve on the formula using VAR(), ..."

Apologies. I simply read the thread title, and must somehow have missed that you intended this to be a competition.