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

0

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!
 
R

Ron Coderre

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-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!
 
B

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)
 
R

Ron Coderre

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
 
A

Aladin Akyurek

If A1:A10 is of numeric type:

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

Ron Coderre

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
 
A

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...

Ron said:
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


:
Click to expand...

--

[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.
 
J

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
 
A

Aladin Akyurek

Good points...

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

would avoid array-processing.
 
Top