The Excel If function returns different results, depending on whether a test criterion is true or false. Find out all you need to know about this Excel function, widely used for data analysis.
One of Excel’s great strengths is its vast catalog of functions, making it easy to apply specific operations to data cells. This is one of the main factors behind the phenomenal success of this software in the corporate world.
There are a wide variety of Excel functions. Among these, the Excel If function is one of the most widely used for comparing data, analyzing groups of data or creating interactive reports.
💡Related articles:
Excel If function: What is Excel?
Excel is a spreadsheet software created by Microsoft, available on Windows, macOS, Android and iOS. It is part of the Microsoft 365 suite of tools, formerly Office.
This software allows you to format, organize data and apply mathematical formulas to it. Each spreadsheet is made up of cells ordered in rows and columns, into which data is placed.
Users can also create graphs and pivot tables. The Visual Basic for Applications programming language can even be used to create macros.
Data analysts and other professionals use Excel to simplify data visualization. Many companies use Excel for business analysis, human resources management, operations management or performance reporting.
Since its launch in 1985, Excel has stood the test of time as the benchmark for spreadsheet software. Today, it is an essential business tool.
Excel If function - What are the Excel functions?
An Excel function is a predefined formula, performing a specific calculation using values entered by the user as arguments.
Each Excel function has a different role and calculates a specific value. Simply enter the arguments to obtain the result value in the cell.
Each function has two major components: a function name, and arguments. A distinction is made between required arguments, without which the function cannot calculate the result, and optional arguments. Some functions, such as “NOW”, require no arguments at all.
There are hundreds of Excel functions, but the main categories are text, date, time, logic, math, statistics, finance and search functions.
The easiest way to insert a function into a cell in Excel is to type the name of the function to be inserted, starting with an equal sign. For example: =SUM. You then specify the arguments in brackets, and press Enter to obtain the result.
What is the Excel If function
The Excel IF function is one of Excel’s most frequently used functions. It allows you to test a condition, and return a different value if the result is TRUE or FALSE.
An IF statement tests the condition and returns one value for a TRUE result, or another value for a FALSE result. For example, you could use this function to check whether an employee deserves a bonus. The result will be “YES” if total sales of a product exceed €5,000, or “NO” if they fall below this amount.
It is also possible to use the Excel IF function to evaluate a single function, or to include several IF functions in a formula. When multiple IF statements are used in Excel, they are referred to as embedded IF statements.
The IF function is commonly used by many professionals. For example, financial analysts commonly use the SI function to evaluate and analyze data by evaluating specific conditions.
This function can be used to evaluate texts, values or even errors. It’s not limited to checking whether one thing is equal to another and returning a single result.
It is also possible to use mathematical operators and perform additional calculations related to the criterion. In addition, multiple IF functions can be nested to perform multiple comparisons.
How is the Excel If function formula written?
The Excel IF function is written as follows:
=IF(logical_test, true_value, false_value
Let’s now look at the various arguments that make up the formula. The “logical_test” argument is essential, as it is the condition to be tested and evaluated as TRUE or FALSE.
The logical test portion of the formula uses a comparison operator to compare values and determine whether a statement is true or false. Logical operators include: = (equal to)
> (greater than)
>= (greater than or equal to) < (less than) <= (less than or equal to) <> (unequal to)
The logical test returns a TRUE or FALSE value. The logical test argument within an IF formula needs to be returned as a Boolean TRUE or FALSE value.
The “true_value” and “false_value” arguments, on the other hand, are optional and indicate the value to be returned if the logical test is TRUE or FALSE. This value can be a number, text in quotation marks, a reference to another cell, a formula with another IF function or even a formula from other function combinations.
How to use the Excel If function
The Excel If function can be used in many different ways. To understand this, it’s best to look at a few examples.
Let’s start with a simple statement, with a basic test to check whether the value in cell B2 is greater than the value in cell A2. If the argument is true, we want a text return indicating: “YES”. On the contrary, if the argument is false, we want to see the message “NO”.
The formula is: =IF(B2>=A2, “Yes”, “No”)
For the second example, let’s suppose we want to test cell A2 and perform an action if this cell is not empty. If the cell is empty, the formula will assign it the status “Open”. If it contains a date, the formula will assign it the status “Closed”.
The formula is written :
=IF(A2<>””, “Ouvert”, “Fermé”)
The third example is a store offering discounts based on quantity purchased. Each quantity range 1, 2, 3 or 4 corresponds to a unit price of 10, 8, 6 or 4.
Using multiple Excel If Functions, it is possible to create a formula to check multiple conditions and perform different calculations based on quantity. The aim is to obtain the total price by adding up the unit prices after reduction. For this example, the quantity of products purchased is indicated in box A2.
The formula is written :
=A2*IF(A2>=4,4,IF(A2>=3,6,IF(A2>=2,8,IF(A2>=1,10))))
This is an embedded Excel If function.
Embedded Excel If function
Data can be grouped on the basis of more than two logical tests in an Excel If function. For each additional category, another Excel If function must be nested within it.
The two SI instructions are nested within each other. If the result of the first test is false, the next Excel If function is evaluated. The process repeats until the last function is reached.
A maximum of 64 Excel If functions can be nested. However, embedded Excel If functions can become too long. Alternatively, you can use the new IFS function introduced in 2019 or the VLOOKUP, XLOOKUP or Index Match formulas.
What is the purpose of the Excel If function?
One use case for the Excel If function is data categorization. This involves dividing data into several categories according to a given criterion.
This process is very useful for data analysis. It is possible to filter elements by size, or use categories for a summary report, pivot table or diagram.
SI formulas are used for many purposes by analysts, and by many other professions. Common examples include checking whether an argument is true or false.
It may also be to produce a NUMBER or a TEXT, or to generate conditional formulas. For example, the result may be A2+B4 if true and C3+D7 if false.
This function is also used to create scenarios for financial modeling. It can also be used to create a debt schedule or a fixed asset depreciation schedule for accounting purposes.
The subtleties of the Excel If function has several subtleties. Firstly, it will work if the logical_test returns a numerical value. Any non-zero value will be treated as TRUE, and zero will be treated as FALSE.
A #VALUE! error may occur when the argument to “test_logic” cannot be evaluated as TRUE or FALSE. In addition, when arguments are supplied to the function as arrays, the IF function will evaluate all elements of the array.
Errors when using the Excel If function can be linked to data types. Excel cannot compare a text with a number or a date. Data must be formatted to enable comparison.
Finally, conditions can be counted using the COUNTIF and COUNTIFS functions, and summed using the SUMIF and SUMIFS functions.
💡Related articles:
How do I take an Excel training course?
The Excel If Function is just one of the many features of Microsoft Excel. To master this widely used business tool, you can choose the DataScientest training course.
In just 6 days, this certification course will enable you to master Excel and its most advanced functions. In addition to the Excel If function, you’ll learn about text, date, finance, logic and search functions. You’ll also learn how to create charts, pivot tables, matrix formulas and the Visual Basic language.
At the end of this course, you’ll be able to use Excel to analyze data, manage spreadsheets and data tables, create charts and use all functions and formulas.
This course prepares you for the TOSA RS5252 certification and includes the official exam. This highly recognized certification enables you to demonstrate your Excel skills to employers.
Our program is completed entirely by distance learning via the Internet, in an intensive BootCamp format. For financing, our organization is eligible for financing options. Don’t wait any longer and discover DataScientest!
