IF formula is a conditional formula used in excel which is based on three different required arguments, the formula is used as follows =IF ( Criteria, Value if the criteria is true, Value if the criteria is False), criteria is an equation based on one or more than one cell and the output is based on the criteria is met or not.
IF Formula in Excel (Table of Contents)
Excel IF Formula
The syntax for the IF formula in excel is as follows.
The arguments in the Excel IF function are defined below.
- Logical_test: The argument or logical condition which can be either true or false. The argument required can thereby be text, date or any Boolean operator etc.
- [value_if_true]: This argument will return a value if the evaluated condition entered in the logical_test is TRUE or identified to be met. This is again an optional requirement to be entered or can be entered as TRUE.
- [value_if_false]: This argument will return a value if the evaluated condition entered in the logical_test is FALSE or TRUE condition is not met. This is again an optional requirement to be entered or else it can be entered as FALSE.
How to Use IF Formula in Excel? (with Examples)
Below are the examples of IF Formula in Excel.
Consider the below table which provides the PE ratio for the respective company and its corresponding industry PE ratio. The Excel IF formula will be used to determine whether the particular stock is overvalued or undervalued. If PE for the stock is greater than industry PE then the Excel IF function should return “Overvalued” else “Undervalued”.
Excel IF formula is applied in the “Inference” column to arrive at the result.
The result is shown below:
Drag the formula to cell D6.
As seen in the above data table, IF formula in excel returns “Overvalued” if the condition for column B is greater than column C is satisfied and identified as true and returns “Undervalued” if the condition is not met and column B is not greater than column C so thereby is identified as false.
Consider the below table to calculate inventory for the year using the Excel IF function. This example also helps us to understand how to apply formulas in a TRUE and FALSE argument based on the given condition.
Below is the given data.
Apply the IF Condition formula.
The result is shown below:
Drag the formula to other cells.
As seen in the above example, the inventory for the year is computed by taking the difference between Beginning and ending inventory. Thereby the condition is used to compare column B and column A, if the value in column B is greater than column A then we subtract B minus A else if this condition is not met then we compute by subtracting column A minus B.
Omitting the [value_if_true] argument in the IF formula in excel while correctly entering the logical_test and [value_if_false] argument will return a zero ‘0’. If the condition in the TRUE argument is to be left as blank or it is expected to return a blank if the condition is identified as TRUE then enter double quotes [” “] in the TRUE argument that is in the second argument.
Consider the below data table to demonstrate the understanding for the above statement.
As seen in the above table, if the second argument is left blank or is omitted and the condition in a logical_test argument is satisfied then the IF function in excel will return a zero ‘0’.
Now in the below data table, if we intend to keep or want the Excel IF formula to return a blank if the TRUE condition is met then the second argument should be populated with double quotes. Thereby the resultant outcome is blank if the temperature is noted to be less than 18 degrees.
By omitting the third argument that is [value_if_false] will not result in an error if the criteria for the specified logical_test is not met or the FALSE argument can be left blank if no logical value is necessary to be returned for the expressed condition. If the function identifies a false condition then the [value_if_false] will not generate any error as thereby can be omitted by closing the bracket after entering the argument for [value_if_true], and the function will return ‘FALSE’ value by default in such case.
Consider the below data table to get better in site for the explanation to the above statement.
As seen in the above example, we have omitted the third argument for the FALSE condition. Thereby if we expect to keep the FALSE argument to be omitted, then we can simply close the brackets after entering the TRUE argument for the condition entered. In this case, if the condition is not met for temperature is not less than 18 degrees then the excel IF function returns a FALSE value.
Similarly as seen in the above example, if we intend to keep the FALSE argument as blank then double quotes need to be entered in the third argument. In this case, the Excel IF formula returns a blank if the temperature is not less than 18 degrees.
If the value to be returned for the specific condition entered in logical_test is to be TRUE or FALSE values, then enter TRUE in the second argument [value_if_true] and the third argument can be omitted or enter FALSE in [value_if_false]. This logical Boolean operator entered in the second and third argument should be entered without any double quotes as the IF function does not recognize the string function in quotes as logical values.
Consider the below example to get a better clarity for the above statement.
As shown in the above data table, the values entered in the second and third argument is TRUE and FALSE without double quotes. For the given example the IF function in excel returns a TRUE value if the condition is satisfied for temperature below 18 degrees and returns a FALSE value if the condition is not met.
The excel IF formula will end up returning “#NAME?” in case the function is wrongly spelled.
Things to Remember About IF Formula in Excel
- Though the logic for the condition is entered correctly, the underlying concept for entering the appropriate values for TRUE and FALSE should be clear and properly understood else the function might generate unexpected errors while arriving at the results.
This has been a guide to IF Formula in Excel. Here we discuss How to use IF Formula in Excel with examples and downloadable excel template. You may learn more about excel from the following articles –