Excel

Using IF & Other Logical Functions in Excel

Many Excel users know how to use basic logical functions. However, I’ve realized that many people are uncomfortable combining multiple Excel logical functions into a single large chain. This is the first step to becoming an Excel wizard.

Excel Logical Functions List

The following is the list of functions that I will cover in this post. I’ve added the French translation as I work in a bilingual environment and not everyone has an English installation.

There are other logical functions that are less commonly used that I will not cover. You can read up on them here.

English Name French Name
IF SI
IFERROR SIERREUR
AND ET
OR OU
COUNTIF(S)
SUMIF(S)

Common Uses of Excel IF Functions

The basic IF function is one of the basic building blocks of Excel. Here are some simple examples that show some use cases. All the other logical functions tend to be included within IF functions except for IFERROR and COUNTIF which can have other use cases.

=IF(A1 = "Philip", TRUE, FALSE)
=IF(AND(A1 >= 2, A2 <= 3), "This is true", "This is false")
=IF(OR(A1 = 2, A2 <> 2), 1, -1)
=IFERROR(Badly Formated Text, "Well Formated Text")
=IF(COUNTIF(A:A,"*sub-string*") > 0, "We found you", "You never existed")
=IF(COUNTIFS(A:A,"*sub-string*",B:B,"chicken wing") > 0, "We found you next to a chicken wing.", "You may be there but not next to a chicken wing")

 

The first statement is just looking to see if cell A1 contains the exact text “Philip”. If it contained, ” Philip” or “Philip “, it would return false.

The second statement would be true if cell A1 is greater or equal to A2 and cell A2 is smaller or equal to 3.

The third statement is similar except with an OR statement. An OR statement are true if at least one of its parts are true. For those who aren’t in the know, the <> symbol represents “is not equal to.”

The fourth statement is most often used at work when there is a possibility of dividing a number by 0. Dividing by 0 results in an Excel error and it looks nicer to have a fail safe rather than staring at a #DIV/0!.

I examine in COUNTIF & COUNTIFS type functions in another post.

No Comments

Leave a Reply