Help with Formula - Concatenate
 

Hello All,

If the score in D1 and D6 are the same then in E4, I need to inlcude both
names (concatenate) like (John / Marty) else the formula below. The
following formula works great when they are not equal.

I know the following concatenate formula works =IF(D1=D6,A1&" / " &A6) , but
how do i combine it with the following formula if they are not equal. Any
help in this matter is greatly appreciated. If there is easier formula to
do this, it would be greatly appreciated.

=IF(AND(ISNUMBER($D1)=FALSE,ISNUMBER($D6)=FALSE),"
",IF(ISNUMBER($D1)=FALSE,$A6,IF(ISNUMBER($D6)=FALS E,$A1,IF(MAX($D1,$D6)=$D1,$A1,$A6))))

A B C D E

1 John 120 10 130
2
3
4 John / Marty
5
6 Marty 110 20 130
7

Help with Formula - Concatenate
 

=IF(AND(NOT(ISNUMBER($D1)),NOT(ISNUMBER($D6))),"",
IF(NOT(ISNUMBER($D1)),$A6,IF(NOT(ISNUMBER($D6)),$A 1,
IF($D1=$D6,$A1&" / "&$A6,IF($D1$D6,$A1,$A6)))))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Argus Rogue" wrote in message
...
Hello All,

If the score in D1 and D6 are the same then in E4, I need to inlcude both
names (concatenate) like (John / Marty) else the formula below. The
following formula works great when they are not equal.

I know the following concatenate formula works =IF(D1=D6,A1&" / " &A6) ,
but how do i combine it with the following formula if they are not equal.
Any help in this matter is greatly appreciated. If there is easier
formula to do this, it would be greatly appreciated.

=IF(AND(ISNUMBER($D1)=FALSE,ISNUMBER($D6)=FALSE),"
",IF(ISNUMBER($D1)=FALSE,$A6,IF(ISNUMBER($D6)=FALS E,$A1,IF(MAX($D1,$D6)=$D1,$A1,$A6))))

A B C D E

1 John 120 10 130
2
3
4 John / Marty
5
6 Marty 110 20 130
7

Help with Formula - Concatenate
 

A bit simpler

=IF(AND(ISNUMBER($D1),ISNUMBER($D6)),IF($D1=$D6,$A 1&" /
"&$A6,IF($D1$D6,$A1,$A6)),IF(ISNUMBER($D1),$A1,IF (ISNUMBER($D6),$A6,"")))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Argus Rogue" wrote in message
...
Hello All,

If the score in D1 and D6 are the same then in E4, I need to inlcude both
names (concatenate) like (John / Marty) else the formula below. The
following formula works great when they are not equal.

I know the following concatenate formula works =IF(D1=D6,A1&" / " &A6) ,
but how do i combine it with the following formula if they are not equal.
Any help in this matter is greatly appreciated. If there is easier
formula to do this, it would be greatly appreciated.

=IF(AND(ISNUMBER($D1)=FALSE,ISNUMBER($D6)=FALSE),"
",IF(ISNUMBER($D1)=FALSE,$A6,IF(ISNUMBER($D6)=FALS E,$A1,IF(MAX($D1,$D6)=$D1,$A1,$A6))))

A B C D E

1 John 120 10 130
2
3
4 John / Marty
5
6 Marty 110 20 130
7

Help with Formula - Concatenate
 

That was simple enough. thanks for the help. mush appreciated


"Bob Phillips" wrote in message
...
=IF(AND(NOT(ISNUMBER($D1)),NOT(ISNUMBER($D6))),"",
IF(NOT(ISNUMBER($D1)),$A6,IF(NOT(ISNUMBER($D6)),$A 1,
IF($D1=$D6,$A1&" / "&$A6,IF($D1$D6,$A1,$A6)))))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Argus Rogue" wrote in message
...
Hello All,

If the score in D1 and D6 are the same then in E4, I need to inlcude both
names (concatenate) like (John / Marty) else the formula below. The
following formula works great when they are not equal.

I know the following concatenate formula works =IF(D1=D6,A1&" / " &A6) ,
but how do i combine it with the following formula if they are not equal.
Any help in this matter is greatly appreciated. If there is easier
formula to do this, it would be greatly appreciated.

=IF(AND(ISNUMBER($D1)=FALSE,ISNUMBER($D6)=FALSE),"
",IF(ISNUMBER($D1)=FALSE,$A6,IF(ISNUMBER($D6)=FALS E,$A1,IF(MAX($D1,$D6)=$D1,$A1,$A6))))

A B C D E

1 John 120 10 130
2
3
4 John / Marty
5
6 Marty 110 20 130
7

Help with Formula - Concatenate
 

Here is a slightly shorter, but not simpler formula (read that as, "it's
obfuscated"<g)...

=CHOOSE((N($D1)<0)+2*(N($D6)<0)+AND($D1=$D6,ISNU MBER($D1))+1,"",$C1,$C6,CHOOSE(1+($D1$D6),$C6,$C1 ),$C1&"
/ "&$C6)

If we are sure only numbers (or nothing) is ever put into D1 and D6, then we
can save a few more characters by using this formula instead...

=CHOOSE(($D1<0)+2*($D6<0)+AND($D1=$D6,$D1<"")+1 ,"",$C1,$C6,CHOOSE(1+($D1$D6),$C6,$C1),$C1&"
/ "&$C6)

Rick


"Bob Phillips" wrote in message
...
A bit simpler

=IF(AND(ISNUMBER($D1),ISNUMBER($D6)),IF($D1=$D6,$A 1&" /
"&$A6,IF($D1$D6,$A1,$A6)),IF(ISNUMBER($D1),$A1,IF (ISNUMBER($D6),$A6,"")))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Argus Rogue" wrote in message
...
Hello All,

If the score in D1 and D6 are the same then in E4, I need to inlcude both
names (concatenate) like (John / Marty) else the formula below. The
following formula works great when they are not equal.

I know the following concatenate formula works =IF(D1=D6,A1&" / " &A6) ,
but how do i combine it with the following formula if they are not equal.
Any help in this matter is greatly appreciated. If there is easier
formula to do this, it would be greatly appreciated.

=IF(AND(ISNUMBER($D1)=FALSE,ISNUMBER($D6)=FALSE),"
",IF(ISNUMBER($D1)=FALSE,$A6,IF(ISNUMBER($D6)=FALS E,$A1,IF(MAX($D1,$D6)=$D1,$A1,$A6))))

A B C D E

1 John 120 10 130
2
3
4 John / Marty
5
6 Marty 110 20 130
7