How to Use an "If Then" Statement in Excel

The If function extends Excel basic calculating abilities by providing conditional evaluations, based on logical, true/false tests. As an example If Then statement in Excel, you might instruct Excel to check that a number is positive before adding it to a total. A single comparison is already remarkably useful, but Excel supports up to 64 nested If statements to address highly complex situations.

The If function follows the basic format "if(test,true,false)" to construct basic comparisons. The logical test can use virtually any other function or reference in conjunction with an operator and comparison value. For example, "A1>0" simply checks if the value in A1 is positive and "SUM(A1:E1)=F2" assesses if the total of the five referenced cells is equal to the value in F2. To compare text values, place the comparison text in quotes. The true and false values in the equation are the action or output that depends on the logical test. This next example tests if A1 equals "Doe, John" and then adds the values in B1 to C1 if that comparison is true, or outputs nothing (represented by the empty quotes) if it's false:

=IF(A1="Doe, John",B1+C1,"")

Nesting means to insert additional If statements in place of the true or false action of another If function. As an example, to test if the value in A1 is greater than 0 but less than 10, you might use the following formula:

=IF(A1>0,IF(A1<10,"between zero and 10",""),"")

As you can see, even this simple problem can be confusing, so it helps to read it out loud, such as, "If A1 is greater than zero, then check if A1 is less than 10. If it is, then output 'between zero and 10'; otherwise, output nothing, and if the original comparison is false, output nothing."

One way to simplify nested If statements is to minimize their use by using Excel's And and Or functions to combine comparisons. These functions are structured as "AND(test1,test2,...)" or "OR(test1,test2,...)" for up to a maximum of 255 comparisons. In the previous example, you could eliminate the nested If statement by using the following formula to check if A1 is more than 0 and less than 10 in a single step:

=IF(AND(A1>0,A1<10),"between zero and 10","")

That statement more simply reads, "If A1 is greater than zero and A1 is less than 10, then output 'between zero and 10'; otherwise, output nothing."

Writing a complex, nested formula from beginning to end in a single step is often impractical, so it's better to use an inside-to-outside or outside-to-inside approach. In either case, you would write a single comparison, test the results, add the next comparison, test again, and so on. In the previous nested example, you might start with the following formula to test the output:

=if(A1<10,"between zero and 10","")

You would then add the first comparison around it, such as: =if(A1>0,if(A1<10,"between zero and 10",""),"")

Likewise, you could work outside-to-inside and use placement markers to test the first comparison before adding the nested If statement in place of the marker, such as: =if(A1>0,"true","")

You would then replace "true" and its quotation marks with the next comparison. This method also works well when you're adding nested statements for both the true and false actions.