Simultaneous 'if' statements

[Ant123]

Hi,

I was hoping someone might be able to help me solve a problem:

I am trying to compare two sets of data: column A and column B - both contain data with 4 characters.

I have compared column A and column B such that column C contains 'TRUE' if A&B are the same and 'FALSE' if they are not.

I now want column D, to read 'FALSE' if column A & B do not start with the same letter and 'TRUE' if they do but only for the columns that read 'FALSE' in column C.

IF column C = FALSE, AND IF column A and B start with the same letter, column D = TRUE
IF column C = FALSE, AND IF column A and B dont start with the same letter, column D = FALSE

e.g.

Column A Column B Column C Column D
D714 D56X FALSE TRUE
F498 F498 TRUE
D714 K875 FALSE FALSE

I also need to count the TRUE's and FALSE's in column D - will this be affected by the blanks?

Thank you :-)

[Jonmo1]

Try

=AND(NOT(C1),LEFT(A1)=LEFT(B1))

[daffodil11]

=if(c1=false,and(left(a1,1)=left(b1,1)),"")

Edit The 2nd parameter of left is optional? Mind = blown.

[martindwilson]

col c =a1=b1
col d =IF(C1,"",LEFT(A1)=LEFT(B1))

[ben_hensel]

So like this:

A1 = first_string
B1 = second_string
C1 = IF(A1=B1, TRUE, FALSE)
D1 = IF(C1=TRUE, "", IF( LEFT(A1, 1) = LEFT(B1, 1), TRUE, FALSE))

Then counting:
E1 = countif(C:C, TRUE)

E2 = countif(D:D, TRUE)

probably you want E1+E2 as your output for TRUEs here?

E3 = countif(D:D, FALSE)

[asar_k]

Hi Ant123,

I think I've found a solution. It goes like this:
=IF(AND((C3=FALSE),LEFT(B3,1)=LEFT(A3,1)),"TRUE","FALSE")

Let me know if it worked.

Btw, this is my very first attempt at solving a problem in excel. I'm quite new to it. So please excuse if there's a fault with the formula. :-)

Thanks,
asar_k

[martindwilson]

ben you dont need to test for = true if the thing you are testing is true/false or even 1/0 if(c1,1,2) will evaluate based on true/false in b1 so if b1 is true it returns 1 if false 2
similarly =a1=b1 will happily produce true/false without any IF() used
also left(a1,1) is the same as just left(a1)

[ben_hensel]

I was going for "explicit" rather than "efficient".

[martindwilson]

@ben fair point

[Ant123]

Hi All,

Thank you so much for your answers! They are a great help!

@ Asar_k and jonmo1: your formulas worked except that it did not leave a blank column if C1 = TRUE

@daffodil11, ben_hensel, martindwilson - all worked thank you!

I have used: IF(C1=TRUE, "", IF( LEFT(A1, 1) = LEFT(B1, 1), TRUE, FALSE)) as I am new to this so it helps me understand what I am asking Excel to do.

Just to check, am I telling Excel: if C1 reads true, put a blank space and for all others (not sure why I dont need 'IF C1 = FALSE?), if the first character from the left in A1 and the first character from the left in B1 are the same, designate 'TRUE' and if not 'FALSE'?

Thank you :-)

[Pete_UK]

... (not sure why I dont need 'IF C1 = FALSE?) ...

The IF function tests for an EITHER / OR situation. You can think of it as:

=IF(Condition_to_be_tested, Action_if_TRUE, Action_if_FALSE)

so if the condition is TRUE then the first action will be carried out, if it is FALSE then the second action will be evaluated. Thus if you test for IF(C1=TRUE,... (or more simply IF(C1,... ) then you don't also need to test for IF(C1=FALSE,... as that is implied in the second action.

Hope this helps.

I'm sure the contributors are grateful for your feedback, but if the answers take care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, I would like to inform you that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

[Ant123]

Hi Pete,

Thanks very much for the information. It is a great help! :-)