Using the AND and OR functions in the same formula

[Jan Buckley]

I need to create a formula that says IF LEFT(A1,1)="7" AND LEFT(C1,1)="A" OR
LEFT(C1,1)="T" OR LEFT(C1,1)="G", THEN CONCATENATE(A1,B1,C1)

I don't know if I can use AND and OR in the same formula, or how to string
it all together. Help, please. Thank you.

 

[Ron Coderre]

Try this:

IF(OR(AND(LEFT(A1,1)="7",LEFT(C1,1)="A"),LEFT(C1,1)="T",LEFT(C1,1)="G"),A1&B1&C1,"")

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Jim Thomlinson]

You can use And / Or in the same function but you need to be very careful
with your bracket and such. Try this...

=IF(AND(LEFT(A1,1)="7", OR(LEFT(C1,1)="A",
LEFT(C1,1)="T", LEFT(C1,1)="G")), A1 & B1 & C1, "")

 

[PCLIVE]

One way that seems to work.

=IF(AND(LEFT(A1,1)="7",OR(LEFT(C1,1)="A",LEFT(C1,1)="T",LEFT(C1,1)="G")),CONCATENATE(A1,B1,C1))

 

[CLR]

=IF(OR(LEFT(C1,1)="T",LEFT(C1,1)="G",AND(LEFT(A1,1)="7",LEFT(C1,1)="A")),CONCATENATE(A1,B1,C1),"")

All on one line.......watch out for the posting word-wrap

Vaya con Dios,
Chuck, CABGx3

 

[David Biddulph]

I need to create a formula that says IF LEFT(A1,1)="7" AND LEFT(C1,1)="A"
OR
LEFT(C1,1)="T" OR LEFT(C1,1)="G", THEN CONCATENATE(A1,B1,C1)

I don't know if I can use AND and OR in the same formula, or how to string
it all together. Help, please. Thank you.

Assuming that you intend AND to take precedence over OR, and that you want
your answer cell to be blank if the condittion isn't met, then try:
=IF(OR(AND(LEFT(A1,1)="7",LEFT(C1,1)="A"),LEFT(C1,1)="T",LEFT(C1,1)="G"),CONCATENATE(A1,B1,C1),"")

Otherwise, shuffle the formula accordingly.

 

[Ron Coderre]

WELL....afer *carefully* re-reading the request...and realizing that my first
post missed the mark.

Here's an option that you could try
=IF(AND(LEFT(A1,1)="7",ISNUMBER(SEARCH(LEFT(C1,1),"ATG"))),A1&B1&C1,"")

Note: Instead of using the CONCATENATE function, you can just link the text
values to be joined by ampersands (&)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP