Excel IF Statement Tutorial
Excel functions are statements used in the Microsoft Excel spreadsheet application. The IF statement is one of the Excel logical functions. It is used to perform a conditional test on values and formulas and return a specified value (or no value) if the specified condition is true and another if it is false.
The syntax of the IF statement is IF(logical_test,value_if_true,value_if_false). A simple example-IF(1+1=2,TRUE,FALSE)-would return the text "TRUE". The statement-IF(B1+B2=2,TRUE,FALSE)-would return TRUE if the values in cells B1 and B2 added up to 2; otherwise it would return FALSE.
Using the IF Statement
To begin using the IF statement, click in the Excel cell where you want the answer to appear. Then click in Formula Bar (fx field) located above the cells and enter the equal sign "=". Next, enter the function. For example, =IF(B1>10,"B1 is greater than 10","B2 is less than or equal to 10"). Hit the "Enter" key. The answer will populate in the cell selected in the first step.
The value_if_false portion of the statement can be left off if you only want to populate the cell with a value if the condition is true. If the condition is false, the cell will contain the text "FALSE" or the value 0. To return FALSE if the condition is false, use the syntax IF(logical_test,value_if_true). To return 0 if the condition is false, use the syntax IF(logical_test,value_if_true,). Note that the second statement has a comma after the TRUE value. For example-IF(A1+2=4,4)-will return the value 4 if the value in cell A1 plus 2 equals four. If it doesn't, FALSE will be returned. Alternatively, IF(A1+2=4,4,) will return a 0 value if the statement is false. If both values are left off (with a trailing comma), then 0 will be returned if the condition is true and FALSE will be returned if the condition is false. For example, IF(E1=4,) returns 0 if the value in E1 is 4 and FALSE if E1 does not contain 4.
You can nest up to seven IF statements as value_if_true and value_if_false arguments. The following example of nesting assigns a color to a numerical value.IF(A2>6,"red",IF(A2>5,"orange",IF(A2>4,"yellow",IF(A2>3,"green",IF(A2>2,"blue",IF(A2>1,"indigo",IF(A2>0,"violet","transparent")))))))
IF(A2>=60,"Pass (:","Fail ):") If the number above is greater than or equal to 60, then the function returns "Pass (:". Otherwise, it returns "Fail ):".IF(AND(A1=1,A2=1), 2) If both A1 and A2 contain the value 1, 2 is returned. Otherwise, FALSE is returned.IF(D8=100,SUM(B4:B8),"") If the value in cell D8 is equal to 100, then the result of adding the values in cell range B4 through B8 is returned. Otherwise, empty text ("") is returned (resulting in an empty cell).