IF Function | Excel

Excel provides a number of logical functions which allow the user to define various "conditions" and have data result in response to them. When definite conditions of these functions are met, a pre-defined calculation will be performed or text will be displayed. The following paragraphes will explain two types of these functions called "Single If" and "Nested If". They could check one and up to seven conditions, respectively.

Single IF Function

The IF function or statement is used to analyse data, test whether or not it meets certain conditions and then act upon its decision. Typically, this function consists of three arguments (parts) enclosed in one set of parentheses and each is separated by a comma, as shown below:

=IF ( logical_test, value_if_true, value_if_false)

Where,

• ● logical_test: is the condition to be met
• ● value_if_true: is the action to be performed if that condition is true
• ● value_if_false: is the action to be performed if false.

The formula can be inserted either by typing it or by selecting it from the Function Library on the formula’s ribbon, under heading of logical functions. The Function Library allow to view the above syntax (details) of IF function, using Mouse as follow:

• i. Click on the FORMULAS Ribbon.
• ii. Click the drop down arrow below the LOGICAL button and select IF from the displayed functions.
• 
  
  
  
  
• iii. The next dialog box will appear and the three arguments can be seen within the box.
• 
  
  
  

Argument: logical_test

This argument of the IF function is the "condition" to test if a cell is a certain value, or to compare two cells. In order to compare two values, symbols called LOGICAL OPERATORS are used as follow:

A typical logical test could compare numbers using all the above operators also strings using only "=" operator. Where, the text string must be written in quotation marks. Reffering to given example below, if B2 > C2, tests whether or not the value contained in cell B2 of the spreadsheet is greater than the value in cell C2. As for strings, cell D3 could be tested for the word "good" as follows; D3="good".

Arguments: value_if_true and value_if_false

These arguments of the IF function are the results of test in case the condition is true or false and could show either a number from a calculation performed or pre-defined text. Reffering to the same logical test below, if the sales figure meets or exceeds the target, a BONUS is calculated (e.g. 2% of sales). If not, no bonus is calculated so a value of zero is returned. The IF function in column D of the example reads as follows;

=IF(B2>=C2,B2*2%,0)

Alternatively, we could display a message saying "NO BONUS". In this case, the true value will remain the same and the false value will be the text string "NO BONUS";

=IF(B2>=C2,B2*2%,"NO BONUS")

A particularly common use of IF functions is to produce "ratings" or "comments" on figures in a spreadsheet. For this, both the true and false values are text strings. As shown in next example, if a sales figure exceeds a certain amount, a rating of "GOOD" is returned, otherwise the rating is "POOR";

=IF(B2>1000,"GOOD","POOR")

Nested IF

As mentioned before, we can use If function to apply more than one condition and produce more than two possible outcomes. This is based on the same principle as a normal IF statement, but involves "nesting" a secondary formula inside the main one. The secondary IF forms the FALSE part of the main statement, as follows;

=IF(1st logic test , 1st true value , IF(2nd logic test , 2nd true value , false value))

Only if both logic tests are found to be false will the false value be returned. Notice that there are two sets of parentheses, as there are two separate IF statements. This process can be enlarged to include more conditions and more eventualities - up to seven (64 in later versions) IF's can be nested within the main statement. However, care must be taken to ensure that the correct number of parentheses are added. In next example, sales staff could now receive one of three possible ratings;

=IF(B2>1000,"GOOD",IF(B2<600,"POOR","AVERAGE"))

To make the above IF statement more flexible, the logical tests could be amended to measure sales against cell references instead of figures. In the example, column E has been used to hold the upper and lower sales thresholds.

=IF(B2>$E$2,"GOOD",IF(B2<$E$3,"POOR","AVERAGE"))

(If the IF statement is to be copied later, this cell reference should be absolute).