IF function in Excel with examples
Office suite of applications helps to perform many tasks in different areas. So, MS Excel, being a tabular processor, is able to solve various tasks. Just enter the function, and here it is - the result. As a rule, this application is used in accounting, but not only. And in all its variety, the function "IF" is far from the last place.
MS Excel Spreadsheet Overview
The tabular processor is designed for organizing calculations, plotting graphs and diagrams without writing special software.
An Excel document is called a workbook, which consists of sheets. Their number can be as much as you want. By the way, the tables in this application do not have an end, and in the literal sense. Although the developers claim that the table consists of columns named by the Latin alphabet (A – IV) and rows numbered in Arabic numerals (1–65536).The workbook has a table that consists of rows and columns, and their intersection is called a cell.Directly with her work will be organized. Users can work with a group of cells, also called a block or range.
Initially (we are talking about MS Excel 2003) in the working area there are 2 toolbars: “Standard” and “Formatting”. Of course, the user can add other commands that he uses most often. So, you can organize a panel from which the function "IF" will be called through a dialog box.
The cell address is formed from the letter of the Latin alphabet and the Arabic numeral: A10, D15, T1523. Both data and formulas can be entered into the cell. If a calculation occurs in a cell, then the total will be displayed exactly in it, and the given formula in the corresponding line above.
Varieties of functions
Excel batch has about 400 functions. They were divided into relevant categories (several examples are given for familiarization):
- financial: POSTPLAT, PUU, PS, BET, NPV, etc .;
- dates and times - TIME, DENNED, YEAR, etc .;
- mathematical - ABS, ATAN, LOG, LN, SIN, etc .;
- statistical - DISP, HYPERGEOMET, QUARTILE, etc .;
- references and arrays - ADDRESS, SELECT, AREA, MATCH, PARTS, etc .;
- databases - BDDISP, BSCHETA, BDSUMM, DSPRNACH, etc .;
- textual - REPLACE, FIND, REPEAT, FIT, FIXED, etc .;
- logical - IF, TRUE, NOT, FALSE, etc .;
- checks of properties and values - ERROR, INFORM, ETHER, CELL, etc.
As you can see, the function "IF" is included in the category of logical functions. Their more detailed study will be a little later.
Learn more about logical functions.
Formulas from this category are necessary for comparing any data types, namely mathematical, textual, logical. Any condition record must contain at least one comparison sign: =,>, <,> =, <=, <>. It is these symbols that determine the relationship between the elements.
The result of a given logical equation can be TRUE or FALSE. The first parameter is expressed as (1), the second - as (0).
Most often, the IF function in Excel works in tandem with formulas such as AND and OR. The first is used to check several conditions and their simultaneous execution. If at least one of the parameters is false, then the whole condition "And" will take the value (0). “OR” accepts the truth when at least one of the conditions is such.
"And", "OR" can have up to 30 conditions in their formula. However, it is necessary to remember: long, heavy formulas are very difficult to perceive. Therefore, if possible, it is advisable to make them somewhat easier by adding columns or rows.
Formula "IF". The subtleties of entering several conditions
The IF function is expressed by the following formula, which can be defined in two ways.
The first way: the function "IF" (the formula can be entered in lowercase or capital letters) is written manually by the user, the bracket opens, and the spreadsheet displays a hint by which you can navigate.The second way: call the function through the menu or toolbar. In the menu you need to go to the "Insert" - "Functions". Already here in the category "Logic" is the function "IF" in Excel, with several conditions or one parameter. A dialog box opens in which you enter the necessary indicators to get the result.
If the condition is true, then the first specified expression is executed; if false, then the result will be the second parameter.
Novice users need to remember that the tabular processor will help in case of an error.A dialog box appears suggesting options for the shortcomings. The user can correct the error on his own or use the help, which lists common examples of writing such formulas. Any little thing can cause a problem to fail. Not there is a comma, instead of a colon put a semicolon, the bracket is not closed, put an extra sign "minus" - this is not a complete list of shortcomings encountered in the work.
Typical errors when entering formulas
When entering various formulas into a cell (the IF function in Excel is no exception), various errors may appear.
- #NAME? - the address of the cell to which the function refers is entered incorrectly, or there is a defect in the name of the formula.
- #NUMBER! - there are a number of limitations in various mathematical functions, i.e. conditions (negative number under the sign of the root or natural logarithm). It is necessary to revise the given function and correct the defect.
- #VALUE! - The user may have entered incorrect parameters. For example, instead of a range of cells, cells are indicated by the enumeration method.
- #LINK! - the function cannot refer to a cell, because its address is incorrect.
- ########## - this means that the result of the calculation does not fit in the specified width of the column. Enough to expand it, and then the answer will be visible.
A few examples
An example of the function "IF" may contain one or more conditions. This may include additional formulas: “AND”, “OR”, “IF”, etc.
A list of workers with consumer and housing loans is given. The table shows payroll and deduction charges.
- Keep those employees 20% who have two types of credit.
- Keep in repayment of debt 10% for those employees who have at least one loan.
- Retain 20% of those workers who have two credits, otherwise, if there is at least one debt, then retain 10% of wages; if there are no debts, print the line “No credit”.
This function may contain several nested formulas. The main thing is not to make mistakes in the preparation of parameters.