How to use the IF function in Excel

/ Ms Excel / By

The IF function in Excel is used to perform a logic test. A formula that uses this function is also known as an IF statement or an If/Then statement.

All formulas using this function can have one of two results: As we will see in the following examples, the formula is designed to test whether something is true. If it’s true, one thing happens, but if it’s not, another thing happens.

The IF function is one of many logical functions that you can use in Excel. Others are AND, IFERROR, IFS, NOT, and OR.

The IF function can be used in all versions of Excel.

IF function syntax and arguments

Any formula using the IF function consists of several parts:

=IF(logical_test, value_if_true, [value_if_false])

  • logic test: The status you are testing. It is imperative.
  • value_if_true: What happens when logical_test is true. It is necessary.
  • value_if_false: What happens if logical_test is false. It’s optional.

It’s easy to write an IF statement in Excel if you read it a little differently: if the first part is true, then do it. If the first part is wrong, do this other thing instead.

Observe these rules:

  • Excel returns FALSE if logical_test is false and value_if_false is omitted.
  • To return text as value_if_true or value_if_false, it must be enclosed in quotes, except for the words TRUE and FALSE.
  • The IF function is not case sensitive.
  • Excel 2010 and newer versions allow up to 64 IF statements in the same formula. Older versions of Excel are limited to seven.

Examples of IF functions

Here are some of the different ways to use IF formulas in Excel:

  Formula from Excel calculation SUM and OFFSET

Write the text if the statement is true

=IF(A2>A3,”Bigger”,”Smaller”)

This is a really simple example of declaring the IF in Excel. The test is to see if A2 is greater than A3. If yes, write larger, if not, write smaller.

Perform calculations if the statement is true

=IF(A2>A3,A2-A3)

This IF statement is worded a little differently. Instead of value_if_true the result is a word, it’s the subtraction of one value from another. So if A2 is actually greater than A3, the difference will be the result. If it’s not true, Excel will return FALSE because we left out the value_if_false part.

Test the statement with math

=IF(A2/A3=5,A2/A3,””)

Another way to write an IF statement is to do a calculation in the logical_test section. The IF condition here is A2/A3=5. If it is true then we do the A2/A3 calculation. If it doesn’t equal 5, we want the result to be nothing, so we use quotes.

Check if a date is today

=IF(A2=TODAY(),”This is today”,””)

Other Excel functions can be used in an FI declaration. In this example, we use the TODAY function to check if A2 is today’s date. If this is the case, the formula writes It is today, otherwise nothing is written.

Use the formula AND with IF

=IF(E2<=HEUTE(),"Jetzt","Bald") =WENN(UND(F2="Jetzt",D2>=(B2-C2)),”Yes”,”No”)

This example of the IF function is a bit more complicated. This is to see if an item we owe money for is overdue and if so, we can see if that amount is within our budget or not to be able to refund it. If both statements are true, we can see in column G if it’s time to pay it.

IF(E2<=TODAY(), « Now », « Soon ») is in the Emergency column. It tells us if the item is overdue or due today by comparing the due date to today’s date. If the due date is today or in the past, now is written in column F, otherwise it is written soon.

The second IF statement is still structured as an IF statement, although it uses AND. The bold part here is where the AND function is, and since it’s in the first group of commas, let’s use it as the logical_test:

  How to change cell size in excel

=IF(AND(F2= »Now »,D2>=(B2-C2))Yes No)

Here it is written differently to show that it is like the other IF statements:

=IF(Test this AND function,write Oui whether it is true or write Not if it is wrong)

There are two IF statements in the AND function:

  • F2=”Now” is one of the formulas in column G. It checks if Now is in F2.
  • D2>=(B2-C2) consists of two parts: first it does the B2-C2 calculation to see how much we still have to pay for the item, then it checks the available budget in D2 to see if we have the money, to pay it back.

So if we owe money now and we have the funds to pay it back, we’re told it’s time to pay for the item.

Examples of nested IF declarations

Nested IF statements are called when there are multiple IF statements in the formula. The structure is almost identical, but instead of closing the parenthesis at the end of the first row, we put a comma and write another statement.

Two FI statements in one form

=IF(B2=”F”,”Class A”,”IF(B2=”M”,”Class B”))

This first example is used to classify students by gender, classifying females as Class A and males as Class B. The formula checks F and M in B2 and then writes either class A or class B depending on which statement is true.

The number of parentheses you need at the end of a formula with nested IF functions is the same number of times IF is written. In our example, IF is written twice, so two parentheses are needed at the end.

Three FI statements in one form

=IF(A2=TODAY(),”This is today”,IF(A2TODAY(),”date in the future”)))

Here is an example of a formula with multiple IF statements. It’s identical to the TODAY example above, but with an additional logical test:

  • IF(A2=TODAY(), “It is today”. checks if A2 is today’s date and returns It’s today if it is.
  • IF(A2 tests if today is greater than A2 to see if A2 is an older date and returns Old Date if it is.
  • IF(A2>TODAY(), «Date Future». does the opposite and checks if today’s date is less than the date in A2 and returns Future Date if it is.
  How to transpose Excel data

Price of the copy if the explanations are wrong

=IF(C2=”Invoice”,””,IF(C2=”Food”,””,B2))

In this final example of a nested IF formula, we need to quickly find the total amount of all purchases that don’t fall into a specific category. We add up all of our unnecessary purchases, and with a long list, this is the best way to go. We have found that every item description that includes invoice or delivery is important, so the price must be given in B2 for all other items.

This is what happens:

  • C2= »Invoice », » »: When C2 tells Bill if you can’t connect, leave the cell blank.
  • C2=”food”, “”: If C2 shows Power, if you can’t connect, leave the cell blank.
  • B2: If any of these statements are false, write what B2 says.

This formula leaves us with a list of prices that we can then add up using the SUM function to quickly assess how much money has been spent on items we didn’t need.

An easier way to write nested IF statements

As you incorporate more and more of the formula, it can quickly become unmanageable and difficult to change later. One way to make nested IF statements more user-friendly is to add a newline after each statement, like this:

= IF(A2=TODAY(),”This is today”, IF(A2TODAY(),”date in the future”)))

To do this in Excel you need to edit the following in the formula bar:

  • Select the formula bar at the top of Excel.
  • Place the mouse under the text area until the cursor turns into a double-headed arrow, then click and drag down the box to provide more working space.
  • Place the cursor after the equals sign and press Alt+Enter (Windows) or Ctrl+Option+Enter (macOS). The rest of the formula is thus placed on a new line.
  • Repeat step 3 before each IF statement so that each instance is placed on its own line.