This tutorial document Excel IF functions has been automatically translated by Google translate. |
Excel: logical Function IF | How to use the IF function
Function Logic
IF is undoubtedly one of the most used functions in Excel worksheets. The Excel IF function returns one value if the condition is TRUE, or another value if the condition is FALSE.
|
IF function: definition of the logic test | Excel logical operators: equal to, not equal to, greater than, less than
The logic test is the key element of the IF function and must be identified so that they can express the two conditions TRUE or FALSE that will be used to complete the expression syntax.
When setting the logical test comparison operators, (summarized in the table) are available.
Equal to; Greater than; Lower; Greater than or equal to; Less than or equal to, Different from.
After the expression of the logic test, in the later part of the function, it must be shown what to do if the comparison with the test result is TRUE or FALSE. Instead of TRUE and FALSE will be possible to express a value, a formula to calculate or text (in the case must be allocated between quotation marks quotation marks).
|
Apply the logical function IF
Consider a function SE that returns a
text result, and we assume to have to
comment with just enough monthly turnover of
more than $ 3,000 and as Inadequate those
less than $ 3,000. In cell D2 we insert the
following function if.
=IF(C3>3000,"sufficient","insufficient")
The result of
this function will return the comment
insufficient if the turnover is less than
3000, or sufficient if the turnover is
higher than 3000. If the turnover was equal
to 3000 would be returned insufficient
comment!
:: Observation. IF Function in the result of the comparison of the logical test (insufficient or sufficient) being a text, the function must be placed in double quotes.
Apply the Excel IF function | Enter formulas on the function arguments
The IF function in the definition of the topics [if TRUE] or [if FALSE] may return text, numeric, or the calculation formulas.
In the example (Figure 3) you want to give an incentive equal to 10% of sales for any monthly sales of more than $ 4,000.
Therefore, if a seller would be billed $ 7000, you will have to calculate the 10% incentive on the $ 3,000 that passed the threshold of 4000.
The feature can be set in the following way:
= IF (C3> 10000; (c3-10000) * 4%; 0)
The topic has been inserted Function FALSE 0 (zero). Therefore, if the seller will not exceed $ 4,000 in incentive column will return the value zero.
Apply the Excel IF Function | Use the logical NOT
In the example shown in Figure 4. You want to apply an IF function, which recognizes and returns a refund of $ 100 to all those who are not in the city of Verona (Italy).
In this case should set a policy of exclusion by using the NOT logical operator is expressed in the formula from open angle brackets and closed
"<>"
The Function considers whether the value in cell D3 is different from "<>" Verona.
TRUE if the value 100, if FALSE 0 (zero) is entered.
=IF(D3<>"Verona";50;0)
Nested the Excel IF function
When I needed more evaluation conditions, TRUE or FALSE in the topic of an SE function you can be included (nested) IF function later that takes into account a further logical TEST.
Examples of logical Excel nested IF function
In the example shown in the figure 5, you want to recognize a working premium of $ 100 if a person has more than 10-years, and $ 200 if you are over 20 years of work. The IF function, can be set in two different ways, in both cases providing a second nested IF function in the first. In the first case, the logical test of the IF function, checks whether the person has less than 10 years of work, if it were true the value 0 (zero) is inserted; in case it is false is considered a second IF logical function that checks if the person has more than 20 years of work. true in the topic of this second function is entered in the 200 and 100 false.
In the second case (picture 6), in the logical test of the IF function, checks whether the person has more than one job 10; in the true of this first function, it is a second nested IF function logical test in which it checks if it has more than 20 years of work (in the true and false values come expressed respectively 200 and $ 100). The formula ends with zero representing the false logic of the first IF function.
Function arguments: The dialog box of the logic function IF
Whether to attribute the correct syntax of the IF function may sound complicated, you can use the option offered by the Function Arguments dialog box.
To access them, simply click the Fx button in the Formula bar and select the IF function Logical functions between or among the newer functions.
Once identified and selected function Conditional IF, will open the Function Arguments dialog box.
As an example of the application of the SE logic function via the Function Arguments dialog box, considering the example previously discussed in Figure 2. The example expresses a sufficient or insufficient judgment if the January budget is greater or less than $ 3,000.
:: In the expression of sufficient or insufficient text value in the Function Arguments dialog box, you do not need to put the text in double quotes. Arguments function to automatically insert quotation marks in the correct syntax of the function.