What the IF function does

 

The IF function is one of Excel’s most useful and most used functions. What it does, basically, is test to see whether a certain condition is true or false. If the condition is true, the function will do one thing, if the condition is false, the function will do something else.

The basic form or syntax of the function is:

=IF(logic test, value if true, value if false)


Using the IF Function to Enter Number Data


Examples

A math quiz ask the question “what is 5 X 7 equal to?” the IF function that would check to see if the answer was correct and, if so, give the student a mark would be:

=IF(A5 = 35,1,0)

Similarly, a geography quiz could ask “What is the capital of Australia?” An IF function that would check to see if that answer was correct and give the student 5 marks would be:

=IF(A5 = “Canberra”,5,0)


The example below uses different deduction rates in its calculations based on employee income.

=IF(A5 < 29701, A5 * 15%, A5 * 25%)


Entering Text with the Excel IF Function

Below, the example IF function is setup to test whether a student's mark is greater than or equal to 50. If it is, "Passed" is written into the target cell. If not, the word "Failed" appears.

=IF(A5>=50,”Passed”,”Failed”)

A second example could be used by a company to quickly determine which employees are entitled to a bonus for exceeding a certain level of production.

=IF(A5>=5000,”Pay Bonus”,”No Bonus”)

Note: when you want to use text in an IF function each text statement must be enclosed in quotes, such as "Pay Bonus".


 

Leaving Cells Blank with the Excel IF Function

=IF(A5 > 5000,”Too High”,” ”)

In this example, the IF function acts as a flag. If the value in cell A5 goes above 5,000, the warning “Too High” is displayed in the cell. If A5 is not above 5,000, there is no need for a warning so the cell remains blank.


Excel Nested IF Function Tutorial

For example, deductions from an employee's income usually depends on employee income. The higher the income, the higher the deduction rate. We can use an IF function to determine what the deduction rate will be.

For this example, if employee income is:

  • less than $29,701, the deduction rate is 15%

  • greater than or equal to $29,701, but less than $71,950, the deduction rate is 25%

  • greater than or equal to $71,950, the deduction rate is 28%

The first deduction rate is handled by the logic test and the value if true argument of the first IF function. To do this, we write the beginning of the IF function as:

=IF(A5 < 29701, A5*15%,

To add the second and third deduction levels, we nest one IF function inside another. For example:

=IF(A5<29701,A5*15%,IF(A5<71950,A5*25%,A5*28%))


The logic test of the Nested IF function, checks to see if a employee’s income is greater than or equal to $29,701, but less than $71,950. If it is, the deduction rate is 25%. If the income is greater than or equal to $71,950, the deduction rate is 28%. Additional rate changes could be added another nested IF functions inside the existing function.


Note: there is no comma separator in the numbers 29,701 or 71,950 in the above example. This is because the IF function uses the comma to separate the three sections or arguments of the IF function contained within the round brackets.

If you use a comma as a separator in numbers greater than a thousand, Excel will give you an error message saying you have too many arguments in your function.



http://spreadsheets.about.com/od/tipsandfaqs/qt/if_enter_number.htm