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

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

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

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

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

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

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

=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

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

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

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

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!

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

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!

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

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.

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

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!

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

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!