How to set condition: A1=A2=A3= ... =A10

[0-0 Wai Wai ^-^]

Hi.
Is thre an easy way to set up the following testing conditions:
- if A1=A2=A3= ... =A10, do something
??

Thanks!

 

[Guest]

If I follow you correctly, then
A1=A2=A3....=A10 equates to A1 through A10 all equal A1, right?

Try this:
=IF(SUMPRODUCT(--(A1:A10=A1))=10,"They all match","Not all the same")

Does that help?

***********
Regards,
Ron

 

[0-0 Wai Wai ^-^]

Yes, that's what I want.
One supplementary question:
- how can I add more than 1 condition when I use the SUMPRODUCT
??

Eg:
Condition 1 OR Condtion 2
A1=A2...=A10, OR,
A10=0, OR,
A11=1

Condtion 1 AND Condtion 2
A1=A2...=A10, AND,
A10=0, AND
A11=1

Thanks!

 

[Bob Phillips]

=IF(SUMPRODUCT(--(A1:A10=A1),--(B1:B10="someothervalue"))=10,"They all
match","Not all the same")


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Well, without an example, I'll keep working with what I sent you:

=IF(SUMPRODUCT(--(A1:A10=A1))=10,"They all match","Not all the same")

So if you also want to know if the corresponding Col_B items are greater
than 500, then try something like this:
=IF(SUMPRODUCT(--(A1:A10=A1)*(B1:B10>500))=10,"OK","Not OK")

For each additional criteria, multiply by a self-contained expression that
equates to TRUE/FALSE.

Does that help?

***********
Regards,
Ron

 

[Aladin Akyurek]

If A1:A10 is of numeric type:

=IF(1-STDEV(A1:A10)-COUNTBLANK(A1:A10),"Yes","No")

 

[Guest]

Hi, Aladin

Usually, I can't wait to try the formulas you come up with, but I couldn't
get this one to work without changing it to:

=IF(AND(STDEV(A1:A10)=0,COUNTBLANK(A1:A10)=0),"Yes","No")

Am I missing something?

***********
Regards,
Ron

 

[Aladin Akyurek]

No, you don't miss anything...

=IF(AND(STDEV(A1:A10)=0,COUNTBLANK(A1:A10)=0),"Yes","No")

is indeed what I intended. Carried away trying to eliminate an AND call...

Hi, Aladin

Usually, I can't wait to try the formulas you come up with, but I couldn't
get this one to work without changing it to:

=IF(AND(STDEV(A1:A10)=0,COUNTBLANK(A1:A10)=0),"Yes","No")

Am I missing something?

***********
Regards,
Ron


:

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

 

[Jerry W. Lewis]

For pre 2003 versions, DEVSQ() would be safer than STDEV() (which could
be zero due to numeric cancellation). You also might want to adjust the
formula to use SUM(ISNUMBER(A1:A10))=10 instead of COUNTBLANK() since
text and boolean values are also ignored by STDEV()

Jerry

 

[Aladin Akyurek]

Good points...

=IF(COUNT(A1:A10)/ROWS(A1:A10)-DEVSQ(A1:A10)=1,"Yes","No")

would avoid array-processing.