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

Thanks!

--
Additional information:
- I'm using Office XP
- I'm using Windows XP

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 ^-^" wrote:


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

Thanks!

--
Additional information:
- I'm using Office XP
- I'm using Windows XP

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!


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 ^-^" wrote:


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

Thanks!

--
Additional information:
- I'm using Office XP
- I'm using Windows XP

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

"0-0 Wai Wai ^-^" wrote in message
...

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!


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 ^-^" wrote:


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

Thanks!

--
Additional information:
- I'm using Office XP
- I'm using Windows XP

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:B10500))=10,"OK","Not OK")

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

Does that help?

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


"0-0 Wai Wai ^-^" wrote:


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!


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 ^-^" wrote:


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

Thanks!

--
Additional information:
- I'm using Office XP
- I'm using Windows XP

If A1:A10 is of numeric type:

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

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

Thanks!

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" wrote:

If A1:A10 is of numeric type:

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

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

Thanks!

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 Coderre wrote:
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" wrote:


If A1:A10 is of numeric type:

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

0-0 Wai Wai ^-^ wrote:

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

Thanks!



--

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

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 wrote:

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 Coderre wrote:

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" wrote:


If A1:A10 is of numeric type:

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

0-0 Wai Wai ^-^ wrote:

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

Thanks!

Good points...

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

would avoid array-processing.

Jerry W. Lewis wrote:
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 wrote:

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 Coderre wrote:

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" wrote:


If A1:A10 is of numeric type:

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

0-0 Wai Wai ^-^ wrote:

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

Thanks!