To automate the calculation and display values depending on the fulfillment of conditions we use IF function in Excel. Anything that depends on another cell, the result of the formula or filling values, can be easily determined by just this feature. This is a function from the Logical category and in this article, learn how to use it.
Adding IF function
Functions in Excel are located on multiple places:
- activate fx button in the formula bar and fill the functions wizard dialog
- write manually, start with =
- since 2007 version we can use the Formulas tab with the functions library
For novice users it is better to use a function wizard, which will help them to fill it and advises the user as where to write. The result formula is then wrote itself into the formula bar.
=IF(logical_test, value_if_true, [value_if_false])
- logical test … what should be checked; there may be value comparison, formula or other function, cell reference, and this condition must have the logical result of TRUE or FALSE; this argument must be filled
- value_if_true … what is to be as a result of this function to display if the condition is met (ie when the condition comes as TRUE); again, there may be a formula, another function, a reference to a cell or text / numeric value
- value_if_false … as well as for the argument value_if_true, but for failure conditions (ie when the condition comes as FALSE)
Example 1 : In recent years we compare revenues and expenditures of the company. We want automatically according to income and expenses show a net balance “profit” or “loss”.
Function in cell E3 has the formula = IF(C3>D3,”profit”,”loss”)
More IFs together
Each IF function can display two possible results (arguments for true and for false). If we need more results, you need the more IF funtions. In Excel version 2003 we can have a maximum of seven nested functions, in version 2007 up to 64. It is always necessary to have in the formula of one function less than the desired number of possible solutions. Eg. for three possible solutions PROFIT / LOSS / EQUALITY counting financial balance we need to have two IF functions.
Example 2: A situation may occur, that revenues are equal to expenditures. For the third solution it is necessary to have two IF functions together
Function in cell E3 has the formula =IF(C3>D3,”profit”,IF(C3<D3,”loss”,”equality”))
If this guide has helped you, become a fan on Facebook and recommend this site to your friends, it can be useful for them too.