Category: Logical Functions

IF – Excel Function

What it does: The IF() Function evaluates an argument. If the argument is TRUE, the formula does one thing. If the argument is FALSE, it does another thing.

Plain English: The classic logical function. If you’re cleaning a lot of data, you can use logical functions to push boolean results, ie TRUE or FALSE. Once you know whether the result is TRUE or FALSE, you can structure an IF statement around those results, allowing you do exactly what you want with the data.

“Nested” IF statements are a bit more advanced, but exactly what they sound like. If you nest IF statements, you’re essentially building multiple layers of IF statement to handle a variety of situations.

Arguments: IF(argument, result if TRUE, result if FALSE)

  • Argument – What are you trying to evaluate? (must be boolean result)
  • Result if TRUE – What do you want to happen if TRUE? This can be a text string, number, another formula, etc..
  • Result if FALSE – What do you want to happen if FALSE? This can be a text string, number, another formula, etc..
    • If this part of the formula is left blank and the argument is FALSE, the formula will return FALSE.

Blog Posts using IF()

  • Blog Post 1

Videos using IF()

  • Video 1

IFERROR – Excel Function

What it does: The IFERROR() Function is a function designed to trap errors when they occur. It is enacted when the argument/formula inside of the parentheses generates any of the following errors:

  • #N/A
  • #VALUE!
  • #REF!
  • #DIV/0!
  • #NUM!
  • #NAME?
  • #NULL!

If there is no error with the original formula, the original formula is run with no additional parameters.

Plain English: This function is awesome. While sometimes an ERROR in a function could result from misusing syntax or not building arguments correctly, often ERROR’s can be a good thing! IFERROR allows us to tell Excel what to do if and when there IS an error. It acts like a safety net for your formulas.

Example: Let’s say a client has asked you to replace all instances of “Example.net” to “Example.com” on her website. She changed domains but does not have the time to replace links across all 1,000 blog posts on her site. You have a tool that allows you to download the meta data from every blog post to your computer. Once the domains are replaced, you have a tool that allows you to upload a .csv of the new blog posts to the website and the changes are made instantly.

  • The first part of the project is to search for the domain “Example.net” in the blog posts. The formula is =search(“Example.net”,CELL)
  • Can you think of why you may get an error?…

What if there is no mention of “Example.net” in 125 of the blog posts? You’ll see an error in 125 out of the 1,000 cells. (a #VALUE error)

  • Our new formula is =IFERROR(search(“Example.net”,CELL),”No Domain”)

The new formula above will run the exact same search on each cell, but if there is an error, it will spit out the text “No Domain”. From there we can trap and deal with those cells separately.

Arguments: IFERROR(value,value if error)

  • Value – The value or formula you want to use first
  • Value if error – The value or formula you want to enact IF there’s an ERROR with the first one.

Blog Posts using IFERROR()

  • Blog Post 1

Videos using IFERROR()

  • Video 1

OR – Excel Function

What it does: The OR() checks if at least one condition you listed is TRUE.

Plain English: The OR() function is similar to the AND() function in that we’re testing a list of criteria. However, the OR() function equates to TRUE if any of the criteria are TRUE. The AND() function is only TRUE if ALL criteria are met.

If you’re analyzing offensive players in football, perhaps you want to narrow down your selection to only those players averaging at least 1 touchdown per game OR at least 100 avg. yards per game. With the OR() statement, you can do so.

Arguments: OR(TEST1, TEST2, etc..)

  • TEST 1 – The first thing you want to test, i.e. Averaging at least 1 touchdown per game – FALSE
  • TEST 2 –  The second thing you want to test, i.e. Averaging at least 100 yards per game – TRUE

NOTE: If any condition within the OR() statement equates to TRUE, the entire statement is TRUE.

Blog Posts using OR()

  • Blog Post 1

Videos using OR()

  • Video 1