Excel if-then: how the IF function works
IF is one of the most popular features in Excel. Sometimes in a spreadsheet, something should be done if certain conditions are in place. What is known as an “if” statement can be solved with an “if-then” statement in Excel. The principle is the same: if a value is true, then do something, otherwise do something else. Here, we explain how if-then in Excel works and the steps you need to take to execute it.
The IONOS HiDrive Cloud Storage enables you to store, edit, and share your Office documents centrally and securely. Whether for private or professional purposes, you can work flexibly on any device with the HiDrive app and other interfaces. IONOS' state-of-the-art US data centers ensure your data is optimally protected.
What is the if-then statement in Excel?
With the IF formula (to give it its proper name), you can make a logical comparison: Is the requested value identical to what you expect or not? If this query is true, then it triggers an action of your choice. If it’s false, it triggers a different action. Simply put: if A, then B, otherwise C.
This makes if-then one of Excel’s most important tools, which can be used in all sorts of situations. Whether you are working on a simple membership directory, a table, or corporate key figures, the IF formula is highly effective – both alone and in combination with other functions.
If-then in Excel: how does it work?
Like every function and formula in Excel, IF is based on a specific syntax:
=IF(condition, value_if_true, value_if_false)
As shown above, the function has three parameters, the first two of which are compulsory.
- Condition: This position must contain a condition – a comparison between two values – where one or both values can be cell references. The possible conditions are:
- Equal (=)
- Unequal (<>)
- Less than (<)
- Greater than (>)
- Less than or equal to (<=)
- Greater than or equal to (>=)
- Then_value (value_if_true): In this parameter, enter what should happen if the condition is true. Values, character strings, cell references, and other functions can all serve as results – these should be entered in quotation marks.
- Else_value (value_if_false): This final parameter is optional. If you don’t specify anything here, the function will return FALSE. Otherwise, the else_value behaves in the same way as the then_value.
In practice, an Excel if-then statement can look like this:
=IF(A1>=100,”target achieved”,”target not achieved”)
In Excel, functions in the formula bar always start with an equals sign and the parameters are embedded in parentheses. You can also make absolute cell references by using a dollar sign. This means that functions will always refer to the specified cell, even when copied to other cells.
$1 Domain Names
Register great TLDs for less than $1 for the first year.
Why wait? Grab your favorite domain name today!
You don’t have to enter the IF function into a cell or the formula bar manually. If you like, you can use the “Insert function” feature, which helps you fill out formulas correctly.
Excel if-then explained with an example
The IF function can be used in various situations, for example, when sorting data points. The following example is an inventory. With IF, you can create a market to indicate when a certain minimum stock level is reached.
=IF(D2<10,”Yes,”No”)
Since the call reference isn’t definitively set in the function, you can copy the formula into further lines by dragging the fill box. The references are then automatically modified.
In addition to a call reference, it’s also possible to add more functions in the IF. You can add a discount to the original price in case the last sale was too long ago.
=IF((DAYS(TODAY();F2))>30;C2*0,75;C2)
In this case, the DAYS function is part of the test: The current data is compared with the specified date (in F2) and the difference in the number of days is displayed. IF checks whether the count is higher than 30. If this is the case, the “then_value” where you’ve placed a mathematical calculation occurs: a discount is given to the original value (in cell F2). Otherwise the original value is retained.
Of course, you can also include parameters for then and else. The one drawback of the IF function is that the test only knows two results: TRUE or FALSE. If you want to differentiate between multiple conditions, though, a new IF function can occur instead of the “else_value,” which then performs another check.
In our example, it would be conceivable to give an even higher discount to goods that already have a reduced price if there are only a few of them left. For this, we have two conditions: The last sale took place more than 30 days ago, and there are no more than 10 items in stock. For this reason, we need to place an “AND” link in the test parameter as well as another if-then request in the else parameter, because we want to make three results possible: 50% discount, 25% discount, and no discount.
=IF(AND(DAYS(TODAY();F2>30;E2<10);C2*0,5;IF(DAYS(TODAY();F2;C2*0,75;C2))
The nesting of the IF function if very practical, but it has its limits: Theoretically, Excel if-then functions can be processed 64 times within one form. This isn’t recommended, though. The nesting creates a complexity that makes it difficult to create error-free syntax. For example, you can easily forget one or more parentheses. In such a long form, searching for errors also takes a lot of work. If need be, it also makes sense to work with support columns or other functions like “VLOOKUP”. The IFS functions can also be another solution to the nesting problem.
It’s also possible, of course, to incorporate if-then into other Excel functions. This makes sense, for example, in a simple “SUM” function. Imagine the example company wants to find out how many items are currently discounted. In such a situation, you can use this formula:
=SUM(IF(D2=C2;0;1);IF(D3=C3;0;1);…)
In practice, though, this formula becomes very long. Instead, it’s better to work with a support column in which you again use the simple if-then function, and then set up the “COUNT IF” function. With this, you can only count the cells that correspond to a particular criteria, so for example those that contain the word “Yes”:
=COUNTIF(E2:E11;“Yes”)
IFS: extension of the if-then
Since Office 2016, Excel has offered yet another if-then function. The IFS function works similarly to IF: A “then_value” is assigned to a logical test – but up to 127 times. The syntax looks as follows:
=IFS(Something is True1; Value if True1; [Something is True2; Value if True2];…[Something is True127; Value if True127])
What stands out here: There’s no longer an “else_value”. But this also means that you have to define the reverse yourself – in our example, in the event that no discounts are used. Therefore:
=IFS(AND(DAYS(TODAY();F2)>30;E2<10);C2*0,5;DAYS(TODAY();F2)>30;C2*0,75;DAYS(TODAY();F2)<=30;C2)
The two checks for the discounts happen one after another. This is followed by the else case: All products that were sold less than or equal to 30 days ago.
HiDrive Cloud Storage with IONOS
Your data is stored securely in data centers in the US and Europe. Access HiDrive from any device, any time, from the office or on the go.
Related articles
Microsoft excel alternatives: 5 premium free-of-charge applications
For many companies, spreadsheets are a part of day-to-day business: whether to compare costs, create diagrams, or plan financing and revenue streams, most tend to gravitate towards Microsoft Excel for their calculations. But is there any way around having to pay for this expensive product? We’ll show you 5 of the best alternatives to Microsoft Excel here.
optimarcShutterstock 30 Excel shortcuts to speed up your work
Excel shortcuts enable you to carry out your work a lot faster, but you first have to learn them. We present you with 30 useful Excel key combinations that faciliate your daily work. From navigation, to inserting cells, to calculating totals. If there isn’t already a shortcut for a function you regularly use, you can easily create your own Excel shortcuts.
Pivot tables: using Excel to its fullest
Excel pivot tables can considerably ease the evaluation of data. Data analysis can be especially time-consuming and tedious with complex numerical series, but with Excel pivot, this doesn’t have to be the case. Simply place your data in the right context and filter the essential information in just a few clicks. The pivot table allows you to create expressive diagrams in no time. Find out all you…
Setting the print area in Excel
Excel spreadsheets can sometimes grow to epic proportions. However, you might not need the entire spreadsheet for a presentation. Rather than wasting tons of paper, you can set the print area in Excel. That way you can print out only the content that’s important to you right now. We’ll explain how to set and restore the print area in Excel.
ChinnapongShutterstock Identifying Duplicates in Excel
There are several reasons why you may want to find duplicates in Excel – to filter out errors or analyze double entries. Excel can automatically display these duplicates to you. Thanks to color highlighting, you can instantly see which entries appear more than once in your tables. And the feature is easy to use: We’ll explain how it works.