Replacement of "IF(OR( ))"
 

Dear sir,
I have an excel formular which involve 3 cells, A1, B1 and C1. The value in
cell of A1, B1 and C1 is 2,6 & 2. I place this formular, IF(OR(A1=B1, A1=C1)
, A1, "-") in the cell A5 and the result shown in A5 is 2.

My question is that I want to know is there other excel formular can replace
this formula? The reason I want to know because in the real world, it may
involve more than 3 cells e.g. 5 cells. The multiple effect of the formular
will be extremely massive if I still apply this formular: IF(OR( )).

I hope you can advice me on this. Many thanks,
Wilchong

--
Message posted via http://www.officekb.com

Replacement of "IF(OR( ))"
 

You could use one of these:

=IF(COUNTIF(B1:C1,A1),A1,"-")

=IF(ISNA(MATCH(A1,B1:C1,0)),"-",A1)

--
Biff
Microsoft Excel MVP


"wilchong via OfficeKB.com" <u43231@uwe wrote in message
news:93273c20709b1@uwe...
Dear sir,
I have an excel formular which involve 3 cells, A1, B1 and C1. The value
in
cell of A1, B1 and C1 is 2,6 & 2. I place this formular, IF(OR(A1=B1,
A1=C1)
, A1, "-") in the cell A5 and the result shown in A5 is 2.

My question is that I want to know is there other excel formular can
replace
this formula? The reason I want to know because in the real world, it may
involve more than 3 cells e.g. 5 cells. The multiple effect of the
formular
will be extremely massive if I still apply this formular: IF(OR( )).

I hope you can advice me on this. Many thanks,
Wilchong

--
Message posted via http://www.officekb.com

Replacement of "IF(OR( ))"
 

Dear T. Valko,
Thanks for your advice. I have studied your formula carefully. Your
suggested formular work perfectly for IF(OR(A1=B1, A1=C1), A1, "-") situation.
I am encountering the new situation like this IF(OR(B1=A1, B1=C1), B1, "-").
Because the parameter, B1, is located in the middle of the series number,
therefore, IF(COUNTIF( )) cannot be work. I am wondering other formula can be
deal with this situation.

According to my example, the result which shown in the cell of A5 is 2, B5 is
"-" and C5 is 2. From this way, I can see the "2" has repeated in the
location 1st and 3rd cell.

Your advice is valuable! Many thanks,
Wilchong



T. Valko wrote:
You could use one of these:

=IF(COUNTIF(B1:C1,A1),A1,"-")

=IF(ISNA(MATCH(A1,B1:C1,0)),"-",A1)

Dear sir,
I have an excel formular which involve 3 cells, A1, B1 and C1. The value
[quoted text clipped - 12 lines]
I hope you can advice me on this. Many thanks,
Wilchong

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200903/1

Replacement of "IF(OR( ))"
 

Hi

Try
=IF(COUNTIF(A1:C1,A1)1,A1,"-")

--
Regards
Roger Govier

"wilchong via OfficeKB.com" <u43231@uwe wrote in message
news:9327cefec35fd@uwe...
Dear T. Valko,
Thanks for your advice. I have studied your formula carefully. Your
suggested formular work perfectly for IF(OR(A1=B1, A1=C1), A1, "-")
situation.
I am encountering the new situation like this IF(OR(B1=A1, B1=C1), B1,
"-").
Because the parameter, B1, is located in the middle of the series number,
therefore, IF(COUNTIF( )) cannot be work. I am wondering other formula can
be
deal with this situation.

According to my example, the result which shown in the cell of A5 is 2, B5
is
"-" and C5 is 2. From this way, I can see the "2" has repeated in the
location 1st and 3rd cell.

Your advice is valuable! Many thanks,
Wilchong



T. Valko wrote:
You could use one of these:

=IF(COUNTIF(B1:C1,A1),A1,"-")

=IF(ISNA(MATCH(A1,B1:C1,0)),"-",A1)

Dear sir,
I have an excel formular which involve 3 cells, A1, B1 and C1. The value
[quoted text clipped - 12 lines]
I hope you can advice me on this. Many thanks,
Wilchong

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200903/1

Replacement of "IF(OR( ))"
 

I'm not real sure what you're looking for. Maybe this:

=IF(COUNTIF($A1:$C1,A1)1,A1,"-")

Copy across to a total of 3 cells.

--
Biff
Microsoft Excel MVP


"wilchong via OfficeKB.com" <u43231@uwe wrote in message
news:9327cefec35fd@uwe...
Dear T. Valko,
Thanks for your advice. I have studied your formula carefully. Your
suggested formular work perfectly for IF(OR(A1=B1, A1=C1), A1, "-")
situation.
I am encountering the new situation like this IF(OR(B1=A1, B1=C1), B1,
"-").
Because the parameter, B1, is located in the middle of the series number,
therefore, IF(COUNTIF( )) cannot be work. I am wondering other formula can
be
deal with this situation.

According to my example, the result which shown in the cell of A5 is 2, B5
is
"-" and C5 is 2. From this way, I can see the "2" has repeated in the
location 1st and 3rd cell.

Your advice is valuable! Many thanks,
Wilchong



T. Valko wrote:
You could use one of these:

=IF(COUNTIF(B1:C1,A1),A1,"-")

=IF(ISNA(MATCH(A1,B1:C1,0)),"-",A1)

Dear sir,
I have an excel formular which involve 3 cells, A1, B1 and C1. The value
[quoted text clipped - 12 lines]
I hope you can advice me on this. Many thanks,
Wilchong

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200903/1

Replacement of "IF(OR( ))"
 

Good morning Valko,
Your suggested formula is working perfectly.

Many thank,
Wilchong

T. Valko wrote:
I'm not real sure what you're looking for. Maybe this:

=IF(COUNTIF($A1:$C1,A1)1,A1,"-")

Copy across to a total of 3 cells.

Dear T. Valko,
Thanks for your advice. I have studied your formula carefully. Your
[quoted text clipped - 26 lines]
I hope you can advice me on this. Many thanks,
Wilchong

--
Message posted via http://www.officekb.com

Replacement of "IF(OR( ))"
 

Hi Roger,
I used your suggested formular and it really work very well.

Many thanks,
Wilchong

Roger Govier wrote:
Hi

Try
=IF(COUNTIF(A1:C1,A1)1,A1,"-")

Dear T. Valko,
Thanks for your advice. I have studied your formula carefully. Your
[quoted text clipped - 26 lines]
I hope you can advice me on this. Many thanks,
Wilchong

--
Message posted via http://www.officekb.com

Replacement of "IF(OR( ))"
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"wilchong via OfficeKB.com" <u43231@uwe wrote in message
news:9331b255ce27d@uwe...
Good morning Valko,
Your suggested formula is working perfectly.

Many thank,
Wilchong

T. Valko wrote:
I'm not real sure what you're looking for. Maybe this:

=IF(COUNTIF($A1:$C1,A1)1,A1,"-")

Copy across to a total of 3 cells.

Dear T. Valko,
Thanks for your advice. I have studied your formula carefully. Your
[quoted text clipped - 26 lines]
I hope you can advice me on this. Many thanks,
Wilchong

--
Message posted via http://www.officekb.com