How to work with logical functions (IF) sheet



To compare any values, of numbers, formulas or text values, it's possible through logical Excel functions. In a logical expression is included at least one of the comparison operators (=) -equal, (>) - greater-than symbol, (<) - less-than symbol, (>=) – greater or equal, (<=) – less or equal, (<>) – not equal. The result of a logical expression is TRUE (1) or FALSE (0)

Let's regard the function (IF). It has syntax: =IF (logical_expression,value_if_true,value_if_false).

  • Logical expression – it's an expression that should be checked is it true or false.
  • Value if true – is a returned value in case if the value is true.
  • Value if false – is a returned value in case if the value is false

Consider a concrete example. =IF(В3>50, 10, 40). If the value of the cell B3 is greater than 50, then the formula returns the value 10, if not – 40.

As arguments Value_If_True, Value_If_False, is accepted to use text arguments, for example, "exam is passed", "exam is not passed". To return an empty row as an argument, it's necessary to insert "".

By way of logical_value, the text value can be also used, for example, С4= "London".

To create more complicated expressions the following functions are used: AND, OR, NOT. AND and OR have the same syntax =AND(logical expression1,logical_value2…). Analogically with OR. The function NOT has only one argument: NOT(logical_value). Consider an example. Let the function IF returns Yes, if А2>3 and В2>10. Otherwise the function will return No. The formula will look like this:

=IF(AND(А2>3,В2>10),"Yes","No")

It's easy to guess that the function OR differs from AND that in the same formula, but with value OR instead of AND =IF(OR(А2>3,В2>10),"Yes","No"), to return the True it's enough to fulfill just one of the conditions, i.e. either А2>3, or В2>10.

To change the value for the opposite one it's possible using the function NOT. It returns the True, if the argument is False and vice versa.

The function IF can be put in each other (till 7 levels). Let's consider an example.

=IF(В2>50,"Always",IF(AND(B2>=30,B2<50),"Usually","Never"))

If the value in the cell B2 is greater than 50, then the value "Always" must be returned. Otherwise, if B2 is in the range from 30 till 50, then the value "Usually" must be returned. If none of the conditions has been fulfilled, then the value "Never" must be returned.

To check if the cell is empty or not, one should use the function ISBLANK, its syntax is: =ISBLANK(value)

The argument of "value" is the reference on the cell or the range of cells. If the reference is given on the empty cell, then the function will return the value True, otherwise – False

 

comments powered by Disqus