Making decisions with Excel´s IF function

How To Buy

     Or call 1300 305 017

The IF function is the most popular and useful of the logical functions in Microsoft® Excel®. It tests values in referenced cells, and then takes action or displays certain values based on the findings.

Here's the general syntax for the IF function:
 =IF(logical_test,value_if_true,value_if_false)

The values if_true and if_false can be numeric or text; if they are text, enclose the text in quotation marks.

A shortcut to IF  

Here's a secret about IF: You don't have to use it, at least not for simple comparisons when all you want is a true-or-false answer. To compare two values based on a comparison operator, simply type = followed by the comparison to be made.

For example, to check whether A1 is greater than A2, type =A1>A2. If it's greater, the result is TRUE; otherwise, the result is FALSE.

Use comparison operators with IF

The IF function is surprisingly versatile – use it to compare the values of various cells, to compare a cell's content to a constant value or the result of another formula, and more. These are the standard comparison operators:

• > is greater than
  
  
• >= is greater than or equal to
  
  
• = is equal to
  
  
• < is less than
  
  
• <= is less than or equal to
  
  
• <> is not equal to 

Following are some examples of comparison operators used with the IF function: 

=IF(A1<>A2,"Not Equal","Equal"): If the value of A1 is not the same as the value of A2, show the words Not Equal; otherwise, show the word Equal.  

=IF(A1>=0,0,A1): If the value of A1 is greater than or equal to 0 (zero), show 0; otherwise, show the value of A1.  

=IF(IF(A1>A2,200,100)=200,"Yes","No"): If the value of A1 is greater than A2, set a temporary value to 200; otherwise, set it to 100. Compare the temporary value to 200. If it's equal to 200, display Yes; otherwise, display No.  

IF(C10=AVERAGE(D1:D5),C10,"Out of Range"): If the average of D1:D5 is equal to C10, show that average; otherwise, show the text Out of Range.  

Now you try it. For the following sentences, create the correct IF function syntax:

• If the value of C6 is not equal to the value of A12 plus 14, display the text "Invalid"; otherwise, display the value of C6.
  
  
• If the value of B4 is greater than the sum of A1:A12, multiply C10 by 1.25; otherwise, multiply C10 by 0.75. 

Here are the answers:

• If the value of C6 is not equal to the value of A12 plus 14, display the text  "Invalid"; otherwise, display the value of C6.
  
  =IF(C6<>A12+14,"Invalid",C6)
  
  
• If the value of B4 is greater than the sum of A1:A12, multiply C10 by 1.25; otherwise, multiply C10 by 0.75.               
  
  =IF(B4>SUM(A1:A12),C10*1.25,C10*0.75)

The IF function adds a simple decision-making element to your spreadsheets – and now that you know how to use it, you should find many situations in which it will be helpful to you.