How to use the IF function in Excel to calculate values based on different criteria
The IF statement is a simple function in Excel that is one of the building blocks you need when you are working with large spreadsheets. You may not know you need it yet, but once you know how to use it, you won't want to live without it.
IF() Function Syntax
The IF() function has the following syntax:
=IF(logical test, value if true, value if false)
The IF function works by performing a logical test that can only have one of two outcomes - TRUE or FALSE. It then outputs a result based on the outcome of that test.
Logical Tests in Excel
You can use anything as a logical test provided Excel can determine whether the outcome is TRUE or FALSE.
Some examples of logical tests that you can use with the IF function include:
- C5=C6 (compare cell C5 to C6. If they are equal, the outcome is TRUE, otherwise the outcome is FALSE).
- C5>C6 (if C5 is greater then TRUE, otherwise FALSE)
- C5="" (if cell C5 is empty then TRUE, otherwise FALSE)
- SUM(A5:A10)>B5 (if the sum of cells A5 to A10 is greater than B5 then TRUE, otherwise FALSE).
There are a number of ways to construct more complex logical tests which we won't cover here.
Examples of the IF() function in action
To use the IF() function, follow these steps:
- Click on a cell and enter the IF() function:
- Enter the logical test as shown in this example:
- The value in the Result column is the outcome of the IF formula
- The logical test checks to see whether the cell in the Day column (B5) = "Wednesday" (we use the speech marks to tell Excel the value we're performing the test on is text rather than a number)
- If the value in the Day cell is "Wednesday", then the result will be Yes.
- Otherwise the result will be No, as it is in this case.
There are many ways to use the IF() function in Excel that we won't cover here, but look out for more advanced IF() lessons coming soon.