If, Then Statements

If there is a function in Excel that I use more than if(), it would take me a long, long time to figure out what that was.  I have been using this conditional statement for as long as I can remember and it is extremely helpful and applicable across a broad range of situations.

The general usage:

If([Condition to Check],[What to do if the condition is true],[What to do if the condition is false])

For example:

1. if(a1=”Bob”,”Boy”,”Girl”) … checks if cell a1 says “Bob”; if so, the cell is populated with “Boy”; if not, “Girl”
2. if(abs(A1-B1)>1000,”Double-Check”,””) … checks for the difference between A1 and B1; if the difference is more than one thousand, the cell is tagged “Double-Check” (perhaps you want to look more into it); if not, it’s left blank.

Here are some other examples you might use if then statements:

1. Checking a Social Security Number field for 9 digits (you can also prevent this with data validation; more on a later post)
2. Checking to see if a name exists (via match() or vlookup() in a separate list
3. Using the birth date and current date (today() function) to determine the age, then flagging accordingly

Attached is a spreadsheet that gives two applications of the function.  One is a simple student list, showing pass or fail grades based on whatever score the teacher decides is the passing grade.  Feel free to change the score to see the impact on the sheet.  I’ve also thrown in a couple more learning tools (conditional formatting, absolute vs. relative references) for you.

The other example shows a nested if statement.  All that means is that you put an if statement within another if statement.  It’s a good way to do tiered conditions, such as the sales bonus scenario.  I’ll show you in a later post an easier way, using something called Case Select Statements in VBA.

Let me know what you think or if you have questions.

ifStatements