IF This Then That? (Quick Excel Tip!)

We all know how useful Microsoft Excel can be. And one of the most used and easy to remember functions is IF.
Many of us are not software developers, but in school or college have used function IF in one form or another, maybe in Pascal or C++. The same function can be found in Excel and can be really useful!

Summary

IF function can perform a logical test and return the desired value. You can use more than one condition by nesting IF functions. The IF function can also be combined with logical functions like AND and OR. To better explain the function we will look at one simple Example:

We will have a table with Existing and Finish Elevations. We want to know if we have Cut or Fill based on weather Exist. Elev. > Finish Elev. (Cut) or Exist. Elev. < Finish Elev. (Fill).

The Syntax is =IF (logical_test, [value_if_true], [value_if_false]). logical_test – Value or Logical Expression that can be Evaluated as Cut or Fill, value_if_true Value to return when Logical_Test is True, value_if_false Value to return when Logical_Test is False.

In our Case we have to compare cells A2 and B2 so we type =B2>A2 this will be our Logical Test, “Cut” and “Fill” will be our Values. Now to complete the function we just compose the whole Syntax.
=IF(B2>A2,”Fill”,”Cut”)
or in other words If Finish Elev. (B2) is more than Existing Elev. (A2) return Fill (“Fill”) else return Cut (“Cut”).

This Formula will appear in cell C2. Now we can easily drag it down and Excel will use if for all cells below.

Nested IF

Nested IF is used when we want to use more than one IF function so we can test more conditions and return more than two Values. Each IF statement needs to be carefully “nested” inside another so that the logic is correct. We can use more up to 64 IF functions, however, this will be a pain to compose and in case you have a more complex scenario you’d better use another function. Here is an example for more complex Nested IF function.

=IF(X13<0,IF(W13<0,D13,(D13-(W13/(T13-U13))*D13)),IF(W13<0,(D13-(X13/(U13-T13))*D13),0))

Logical Operators

A logical operator in Excel is used to compare two values. Logical operators are also called Boolean operators because the result of the comparison in any given case can be either TRUE or FALSE.

Condition Operator Description
Equal to = If value in A1 is equal to value in B1 formula returns TRUE,  otherwise FALSE.
Not equal to <> TRUE if A1 Is different than B1 and FALSE if they are equal.
Greater than > TRUE if a value in cell A1 is greater than a value in cell B1; otherwise FALSE.
Less than < TRUE if a value in cell A1 is less than in cell B1; FALSE otherwise.
Greater than or equal to >= TRUE if a value in cell A1 is greater than or equal to the values in cell B1; FALSE otherwise.
Less than or equal to <= TRUE if a value in cell A1 is less than or equal to the values in cell B1; FALSE otherwise.

 

How useful was this post?