Adding some If…Then logic to our Excel formulas can really make creating dynamic spreadsheet easier.

Basically the IF logic statement in excel works just like it does in any web coding program, it check to see if a condition is met and then executes something based on the results.

It looks like this:

=IF(logical_test, value_if_true, value_if_false)

So for instance the formula:

=IF(A2>B2,”yes”,”no”)

Checks to see if Cell A2 is bigger than B2 and returns “Yes”if it is or “No” if it is not.

You can do an endless number of things with the true and false values, such as return text, return numbers, return a cell value or even execute a formula.

Lets look at some various options:

=IF(A2>B2,”yes”,”no”) — returns text answers
=IF(A2>B2,”why yes it is larger”,”no of course its not larger”) — returns longer text answers
=IF(A2>B2,1,2) — returns numeric answers
=IF(A2>B2,A2,B2) — returns cell values
=IF(A2>B2,(A2-B2),(B2-A2)) — returns nested formulas
=IF(A2>B2,”Sorry, you are at your limit”,(D2*4)) — returns combination of options

Ok, so lets take it up a notch. If you want to check more than one condition you can use an AND or an OR operator:

=IF((AND(D2>5,C2<100)),2,1)
=IF((OR(D2>5,C2<100)),2,1)

Now – you can even nest multiple AND/OR statement into the same IF formula, the difference here is that while each logic test can have its own value if True, you can only have one value if all are false. You can nest up to 7 IFs in one formula, so that means you can return up to 8 different results (7 different values if true and one value if false).:

=IF((AND(D2>5,C2<100)),3,(IF((OR(D2>5,C2<100)),2,1)))
=IF((B2=”Michigan”),4,IF((B2=”Indiana”),3,IF((B2=”Ohio”),2,IF((B2=”Illinois”),1,””))))

Notice the second example the false value is left blank “” meaning the cell will be left blank if the item is not from any of the four states listed.

Ok – go forth and be logical.

This entry was posted on Tuesday, January 8th, 2008 at 3:11 pm and is filed under Excel, Software. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.