IF Condition In Excel

MS Excel is a popular spreadsheet tool that is globally used in business and various other industries for its efficiency and ease of use. The IF condition is an important part of the Excel software and allows users to make logical comparisons between distinct values and the user’s expectations.

As a small business owner, the IF function in Excel is an integral part of my daily record keeping as it allows me to set specific conditions that help me keep track of my earnings and expenditures.

In this tutorial, I will take you through the definition, uses, and some common uses of the IF function.

What Is IF Condition in Excel?

The IF function in Excel determines whether Excel meets a given condition and returns a result based on whether the result is “true” or “false”. The IF condition in Excel is conditional and returns results based on the success or failure of a given criteria.

For instance, the IF function in MS Excel can be used as follows:

“=IF(Condition B, “value C”, “Value D”)”

The Excel IF formula returns “Value C” if condition B is met and returns “Value D” if condition B is not met.

Syntax of the IF Excel Function

The IF condition in Excel accepts the following arguments:

• Logical_test: Logical test refers to the condition that the function will evaluate. The condition can either be a logical expression or a value.

• Value_if_true: The IF function in Excel returns this value if the condition is “true”.

• Value_if_false: The IF function in Excel returns this result in the case the condition is “false”. In this formula, the “logical_test” is a needed argument, on the other hand, the “value_if_true” and “value_if_false” are conditional arguments.
  

How To Use the Excel IF Function

Before we get into further details, let us take a look at how to use IF condition in Excel:

1. Add an IF function

Open a new spreadsheet in Excel, You can start a new Excel spreadsheet, determine where you want to examine the results, and then click on a cell. After that, click the 'fx' option to open the function wizard and choose the IF function. Or you can click on the 'OK' button.

2. Create a logical test

Next, you need to decide on the condition that you want to assess in Excel, and then input it in the “logical_test” field. Excel allows you to use various logical operators for this, some of which include:

In the 'logical_test' field, enter 'D2>70' to see if cell D2's value is more than 80. In order to determine the value of the cells, you can add conditions. In the 'logical_test' field, for example, you can type 'D2=E1' to see if the value of D2 and cell E1 are equal. The IF function takes into account the new value to assess the condition each time you modify the values in D2 or E1.

3. Customize 'value_if_true'

The next field is 'value_if_true' and it is optional. You can leave it blank by writing "" into the field space. If you leave it blank and the condition is true, Excel generates a blank result. You can customize the 'value_if_true' result in the following ways:

1. If you choose the 'value_if_true' option and then choose another cell, the true value will relate to that cell.

2. When you input a word or phrase with quotations around it, the true value displays as a word or phrase.

3. When you enter a number, the true value shows as a number.
   

4. Customize 'value_if_false'

In Excel, a value is indicated in the 'value_if_false' field if the condition is false. If not, it is equivalent to the 'value_if_true' column. The same customization guidelines apply to the optional 'value_if_false'. Excel produces 'False' as the value if you leave it blank and the condition is false.

5. Click ‘OK'

After you’ve input your data into the “logical_test”, “value_if_true”, and “value_if_false”, click on the OK button. Then confirm if your IF function in Excel produces an accurate result or not. You can also click and drag the cell handle down a column or across a row to duplicate the function to other cells.

How To Create A Nested IF Function In Excel?

You can follow these steps to use Excel to create a nested IF function:

1. Type an IF function

If you have complex formulas, typing them could be easier than using an insert function. Choose the cell where you wish to view the final result. Select the cell and enter '=IF'. After that, Excel fills in the formula and allows you to add more data to finish it.

2. Type the first condition

Next, you need to create the first condition and a true value. For example, to get a ‘low’ value in Excel when C2 is less than or equal to 40, you can input:

=IF(C1<=40, “Low”,

The comma after the ‘Low’ states that the “value_if_false” arg

The comma after the 'Low' indicates that the 'value_if_false' parameter follows the true value. When establishing a nested IF function, it is common to enter another IF function rather than a false value.

3. Enter another IF function

After providing the 'value_if_false' argument, you can use another IF function. Continuing from the previous example, you can type it as:

=IF(C1<=40, “Low’, IF(

You should additionally assess the open parenthesis criteria in Excel. For example, if C1 is less than or equal to 150, you may get the value 'High'. In that situation, you can write the formula as follows:

=IF(C1<=40,"Low",IF(C1<=150,"High",

The IF function has various useful Excel formulas that you can use on a daily basis.

4. Input the function's value if false

If C1 falls short of the 'Low' or 'High' specifications, you can generate a 'Medium' result. In the 'value_if_false' option, you can specify 'Medium' as the function's value as follows:

=IF(C1<=40,"Low",IF(C1<=150,"High","Medium"))

Because Excel analyzes guidelines from left to right, it completes the evaluation once a condition is determined to be true. Once you do that, it disregards the remaining conditions. For example, if C1 is 105, Excel considers it less than 150 and assigns a value of 'High'.

Things to Remember About the IF Function

Before you start working with IF function in Excel, it is important to learn how to use IF formula in Excel, some other things that you should remember are:

1. If the result of the logical test is a numerical number, the Excel IF function will execute. Any value that is not zero is regarded as TRUE in this situation, while zero is regarded as FALSE.

2. #VALUE error happens when the logical_test parameter is not able to determine if it is TRUE or FALSE.

3. The IF function examines each element of the array when any of the inputs are given to the function as arrays.

4. You should use the COUNTIF and COUNTIFS functions to count conditions.

5. You should use the SUMIF and SUMIFS functions to add up conditions.
   

Common Uses of the IF function

• Building a depreciation schedule

Financial analysts use schedules for depreciation to track a company's asset depreciation and combine the calculations from the balance sheet, income statement, and cash flow statement.

A depreciation schedule tests data entries and calculates the asset and account depreciation rates. It uses an Excel IF statement that requires data that is higher than, less than, or equal to the specified income, balance, or cash flow values.

• Budget Tracking

A simple IF statement is capable of identifying expenses and values based on an established budget. Provided a set of expenses and prices, you can write an IF function to classify data as above or under budget.

As a small business owner, this feature is vital to me as I can track my budget without worries. If my resource budget is $5000, I can create a function that will return any number larger than $5000 with the Excel IF function text “over budget”, and any number less than $5000 as “under budget.”

• Data organization

You can easily organize and categorize data using the IF condition in Excel. When you create a statement that ticks all the checkboxes of your preset conditions, Excel will output the results as intended for the specific condition.

• Calculating debt schedules

A debt schedule shows and classifies a company's debt depending on account maturity as well as interest rate.

After entering the data for the initial balance, repayments, draws, interest, and closing balance, the financial analyst makes use of an IF statement to test each debt account against the logical function conditions and assign it to the appropriate maturity and interest level range.

The function offers an effective way to automatically determine and categorize debt accounts.

Wrapping Up

The IF condition in Excel is one of the most commonly used features of the software, and it allows you to execute logical comparisons between a value and the result you expect. It isn’t just limited to logical comparisons, it can also perform various mathematical operations and calculations based on your needs.

You can learn more about Excel by signing up for numerous data analytics courses offered by upGrad.

Frequently Asked Questions

1. How do you write IF conditions in Excel?

IF function use in Excel generally entails a method where you start by writing the “=” equal sign, follow that with an “IF” and then state your condition with a two-part syntax on whether your set conditions are true or false.

2. How do you put 2 conditions in IF Excel?

The Excel IF function allows you to put two or more conditions together by adding an “AND” or “OR’ function.

3. How do you write an IF then sum formula in Excel?

You need to make the logical comparisons and then use the SUMIFS function.

4. What is the formula for IF condition contained in Excel?

You can use the formula:

=IF(ISTEXT(cell), value_to_return, "")

5. How do you use the IF function in Excel with 5 conditions?

In case you want to use the IF statement in Excel for 5 conditions, you can either use the Nested IF method, IF Function with OR Logic, or IF Function with AND Logic.

6. How do you use IF and IFS functions in Excel?

The IF and IFS functions are used to perform logical tests and return different values based on whether the test is true or false.