This article will guide how to IF-THEN function in Excel for Microsoft 365, MS Excel 2019, 2016, 2013, 2010; Excel for iOS, Excel online, Google Excel and more Microsoft excel.
IF-THEN function in Excel
How to use Excel IF-THEN function is a potent tool for adding decision-making to spreadsheets. It determines if a condition is true or false and then executes a certain set of instructions following the outcome.
For instance, excel formulas entering an IF-THEN statement in Excel can check if a certain cell is bigger than 900. If so, you can have the formula return “PERFECT” as its result. You can change the formula to “TOO SMALL” if it isn’t.
The IF-THEN formula can accept a wide range of conditions.
The function’s name and the function arguments are enclosed in parenthesis in the syntax of the IF-THEN function.
The IF-THEN function should be written in this way:
The logic test is contained in the function’s IF section. Here, pdf to excel converter you compare two values using comparison operators.
If-then function in excel two arguments are included in the THEN section of the function, which follows the first comma.
- If the comparison is accurate, the first parameter instructs the function what to perform.
- The function is instructed what to do if the comparison is false by the second parameter.
An example of an easy IF-THEN function
Let’s look at a simple example of an IF-THEN expression before going on to more challenging calculations.
The value of $100 is entered in cell B2 of our spreadsheet. To determine whether the value exceeds $1,000, we can enter the following formula into C2.
These arguments are used with this function:
- B2>1000 checks to see if the value in cell B2 exceeds 1000.
- If B2 is more than 1000, “PERFECT” displays the word “PERFECT” in cell C2.
- If B2 is not greater than 1000, “TOO Small” returns the word TOO Small in cell C2.
Only two values can be compared in the function’s comparison section. Both of those possible values are:
- Fixed quantity
- A group of symbols (text value)
- Day or hour
- Methods that can return any of the values above
- A link to any other cell in the spreadsheet that contains any of the values listed above
Any of those above can be returned by the function’s TRUE or FALSE component. You can add more calculations or functions to the IF-THEN function to make it more complex (see below).
In this case, “value if false” will calculate and return the result because B2 is not more than 50,000.
- That amounts to B2*0.10 in this instance, or 4000.
- In cell C2, where the IF-THEN function is added, the result will be entered as 4000.
- Additionally, you can incorporate calculations within the function’s comparison side.
- For instance, you might substitute the following function for the IF-THEN statement above if you wanted to predict that only 80% of income will be taxable.
Except when using TRUE and FALSE, which Excel automatically understands, you must include any text you want to return in quotation marks when entering true or false conditions of an IF-THEN statement in Excel. There is no need to use quote marks for other values or calculations.
Calculations must be Inputted Into the IF-THEN Function.
Depending on the comparison findings, alternative calculations might be embedded for the IF-THEN function to execute.
Based on the total revenue in B2, one computation is utilized in this example to determine the tax due.
The reasoning test examines B2’s total income to determine whether it exceeds $50,000.00.
With this, B2 will first be calculated before being compared to 50,000.
When entering figures in the thousands, remember to avoid using commas. This is because Excel considers a comma the end of an argument within a function.
Within an IF-THEN Function, Nesting Functions
A function may also be “nested” (or embedded) inside an IF-THEN function.
This enables you to carry out complex calculations and then contrast the actual outcomes with anticipated ones.
Consider a spreadsheet with five students’ grades listed in column B for this example. You might use the AVERAGE function to aver these grades. Cell C2 may return “Excellent!” or “Needs Work,” depending on the results of the class average.
This is how the IF-THEN function would be entered:
When the class average is more than 85, this function displays the word “Excellent!” in cell C2. Otherwise, “Needs Work” is returned.
As you can see, adding the IF-THEN function to Excel together with embedded computations or functions enables you to make dynamic spreadsheets that are incredibly functional.
In Excel, how can I make several IF-THEN statements?
To build several IF-THEN statements in Excel, use nesting. Use the IFS function instead.
In Excel, how many IF statements can you nest?
A single IF-THEN statement can include up to 7 nested IF statements.
How does Excel’s conditional formatting function?
You may test for several circumstances by applying multiple rules to the same data using conditional formatting in Excel. If there are any conflicts between the several rules, Excel first checks to see which one should be applied to the data.