Excel Spreadsheet Formulas If Then

Excel Spreadsheet Formulas If Then
SI function
The SI function is one of the most popular functions in Excel and allows you to make logical comparisons between a value and a result you expect. In its simplest form, the SI function says:
YES (Something is True, do something, otherwise do something different)
For this reason, an SI instruction can have two results. The first result is if the comparison is True and the second if the comparison is False.
If you want to move forward to work with multiple SI statements, see: Advanced SI functions: Working with nested formulas to avoid errors.
Simple SI examples
Cell D2 contains the formula = SI (C2 = “YES”; 1; 2)
= SI (C2 = “Yes”, 1,2)
In the example above, cell D2 says: YES (C2 = Yes, then return a 1, otherwise return a 2)
Cell D2 contains the formula = SI (C2 = 1, “YES”, “NO”)
= SI (C2 = 1, “Yes”, “No”)
In this example, the formula in cell D2 reads: SI (C2 = 1, then return Yes, otherwise return No)
As you can see, the SI function can be used to evaluate text and values. It can also be used to evaluate errors. It is not limited to checking if one element is equal to another and returning a single result, you can also use mathematical operators and perform additional calculations according to your criteria. You can also nest several SI functions to perform various comparisons.
NOTE: If you are going to use text in formulas, you will have to type the text in quotation marks (for example, “Text”). The only exception is the use of TRUE or FALSE, which Excel understands automatically.
First steps
The best way to start writing an SI instruction is to think about what you want to achieve. What comparison are you trying to make? In many cases, writing an SI instruction can be as simple as mentally analyzing logic: “what happens if this condition is met and what happens if it is not met.” Always ensure that the steps follow a logical progression, otherwise the formula will not do what you think it would do. This is especially important when creating complex (nested) IF statements.
More SI examples
The formula in cell D2 is = SI (C2> B2, “Budget exceeded”, “Within budget”)
= YES (C2> B2, “Budget exceeded”, “Within budget”)
In the above example, the function in D2 says YES (C2 is greater than B2, return “Budget Exceeded”, otherwise return “Within budget”)
The formula of cell E2 is = SI (C2> B2; C2B2;
= SI (C2> B2, C2B2, O)
In the previous illustration, instead of returning a text result, we are going to return a mathematical calculation. The formula in E2 reads as follows: IF (the actual amount is greater than the budgeted, subtracts the budgeted quantity from the actual quantity, otherwise, nothing is returned).
The formula for cell F7 is SI (E7 = “Yes”, F5 * 0.0825.0)
= SI (E7 = “Yes”, F5 * 0.0825; 0)
In this example, the formula in F7 reads as follows: IF (E7 = “Yes”, calculates the total quantity with F5 * 8.25%; otherwise, there is no sales tax, so 0 is returned )
Recommended Procedures: Constants
In the latter example, we saw that both “Yes” and the tax rate (0.0825) were written directly into the formula. In general, it is not recommended to use literal constants (values that may change occasionally) directly in the formulas, as it can be difficult to find and change them in the future. It is much better to place the constants in their own cells, where they can be found and changed easily. In this case it is correct, since there is only one SI function and it will rarely change the tax rate. Even if this happens, it can be easily changed in the formula.
Calculation Operators
If you want to get more information about the different calculation operators that you can use in the formulas, (<less than,> greater than, = equals, <> is not equal to, etc.), see this article: And priority.
Use YES to check if a cell is blank
Sometimes it is necessary to check if a cell is blank (usually, so that a formula does not display a result without input data).
The formula of cell E2 is = SI (D2 = 1, “Yes”, IF (D2 = 2, “No”, “It is possible”))
In this case, we will use SI with the ESBLANCO function:
= YES (BLANK (D2); “Blank”; “Not blank”)
Which equals SI (D2 is blank, returns “Blank”, otherwise returns “Not Blank”). You could also easily use your own formula for the “Not Blank” condition. In the following example we will use “” instead of ESBLANCO. Basically, the two double quotes (“”) equals “nothing”.
Check if a cell is blank: The formula in cell E2 is = YES (BLANK (D2); “Blank”; “Not blank”)
= YES (D3 = “”; “Blank”; “Not blank”)
This formula reads as follows: IF (D3 has nothing, it returns “Blank”, otherwise, it returns “Not Blank”). Here is an example of a very common method of using “” to prevent a formula from performing a calculation if a dependent cell is blank:
= SI (D3 = “”; “”; YourFormula ())
IF (D3 is equivalent to nothing, nothing is returned, otherwise calculate formula).
SI nested function example
While a single SI function contains only two results (true or false), nested SI functions can have 3 to 64 results.
Use “” to check if a cell is blank: The formula in cell E3 is = YES (D3 = “”; “Blank”; “Not blank”)
= YES (D2 = 1, “Yes”, YES (D2 = 2, “No”, “Possible”))
In the previous illustration, the formula of E2 says the following: IF (D2 equals 1, it returns “Yes”, otherwise, if D2 equals 2, it returns “No”; possible”)). Note that there are two closing parentheses at the end of the formula. They are required to complete both SI functions, and if you try to write the formula without the two closing parentheses, Excel will try to correct it for you.
Warning
Although Excel allows you to nest up to 64 different SI functions, it is not recommended to do so. Why?
The use of several SI instructions requires a careful planning to create them correctly and ensure that your logic can calculate without error each condition until the end. If you do not nest your SI instructions with absolute accuracy, a formula might work in 75% of cases but return unexpected results in the remaining 25%. Unfortunately, the probabilities of detecting that 25% of cases are very rare.
Keeping several instructions YES can be very difficult, especially if, after a while, try to find out what you (or, worse still, someone else) was trying to do.
Several SI instructions require several opening and closing parentheses (), which can be difficult to manage depending on the complexity of the formula.