January 25th, 2012
Tips, Tools and Tutorials
If ever there was a single function in Excel that I use the most, it’d probably be the IF function. It is a workhorse as far as I’m concerned, as it can be used in combination with several other functions for a plethora of purposes. Along with other Logical Functions like AND, OR, NOT, TRUE, and FALSE, you’ve got all the ingredients you need to create some really cool formulas that will help you not only sort your data, but can even help improve the aesthetics of your spreadsheets and help to automate the analysis of your data. Here are some reasons why I have unconditional love for conditional formulas in Excel.
A conditional formula in Excel is a formula that makes a logical test of data using the IF function. It essentially allows you to create a basic logical argument of “If (this), then (that).” Though there is an entire subset of philosophy devoted to truth-functional propositional logic, in this case, you don’t won’t have to open a textbook to develop logical arguments within Excel. One of the great benefits of using conditional formulas within Excel is that it’s pretty simple. Conditional programming is used in web design and development as well, particularly in the case where a website is visited using different browsers. I like to think of it as a point in which math, philosophy, and programming meet.
The basic syntax of the IF formula in Excel is:
logical_test: the condition that you are checking for
[value_if_true]: the result you want if the condition is true
[value_if_false]: the results you want returned if the condition is false
As an example, you can include this formula in cell D2 to list the player with the highest score:
You can also use the AND, OR, and NOT functions to produce TRUE and FALSE results.
You can also use <> as an operator in your formula in place of “not equal to” like so:
At some point in time, when you are faced with a lot of data in your spreadsheets, you may want to find a way to highlight or “filter” out some of your data based on specific criteria. For example, if you wanted to see if the value of a cell in column A is equal to the value of a cell in column B (duplicates), you can use a conditional formula in column C to give you a TRUE or FALSE result.
This can be helpful if you are using this formula across a large range of cells and you want to be able to catch any anomalies. In another example, if you were trying to determine which cells have values of a certain range, you can also use the IF function to create a conditional formula like:
If your objective is to count how many cells fit a specific criteria (e.g. values greater than 50), you would probably be better off using the COUNTIF function. However, the benefit to using basic conditional formulas with the IF function is that you can use this formula for conditional formatting so you can highlight cells that match a criteria of your choosing.
=COUNTIF(D2:D5,B1) for cell references and numerical values
=COUNTIF(D2:D5,”Player 1″) for text vaues—don’t forget to include quotation marks if you’re referring to a text value
Finding duplicates across rows or columns [e.g. =IF(A1=B1,”Same”,”Different”)]
Finding values in a specific range [e.g. values greater than 4: =IF(A1>4,TRUE,FALSE)]
Calculate different equations based on different values of a single cell
[e.g. if you’re trying to use a formula that is dependent upon conditions, you can “nest” your functions (where multple functions are used within each other)
In this example, if you were to calculate the difference of one player’s victories over the other (without ending up with negatives) and to also denote if there is a tie, you could use a formula like:
This formula is actually two IF formulas in one…first, you have the first IF formula IF(B7>C7,B7-C7,…). However, with the value_if_false part of the equation, you include yet another IF formula: IF(C7>B7,C7-B7,”Tie”). If both the first IF formula is false (meaning that B7 is NOT greater than C7) AND the second IF formula is also false (C7 is NOT greater than B7), then the final false value carries over from the second (or in this case, inner-most) IF formula, which is “Tie.”
Applying multiple condtions in a single formula
For this example, let’s say you wanted to determine if Player 2 had a good game day by not only determining if Player 2 was a winner of the game, but that they also scored more than 3 points. You can create an IF formula with a nested AND formula so that you can narrow down your results to just the games where Player 2 met BOTH criteria like so:
=IF(AND(D2=C1,C2>3),”Good Game”,”Needs Improvement”)
In this formula, if Player 2 (cell C1) is the victor (cell D2) of the game, AND Player 2’s score (cell C2) is greater than 3, then a result of “Good Game” is produced, otherwise it will result in “Needs Improvement.” This means that if Player 1 wins, if there is a tie, or if Player 2 wins but doesn’t score more than 3 points, it will result in a “Needs Improvement” result. If you wanted to adjust the formula so that if Player 2 scores 3 points and you want a “Good Game” result instead of “Needs Improvement,” then you would adjust the > symbol to a greater than or equal to symbol >= in the formula.
=IF(AND(D2=C1,C2>=3),”Good Game”,”Needs Improvement”)
Formatting cells based on multiple criteria outside of the standard function
With Excel 2007 & 2010’s conditional formatting, you have several options available that you can use to highlight the cells that you apply the formatting to based on the selected cells’ value. But what if you wanted to format cells based on criteria that is out of the scope of the default options? You do it with a formula, of course!
In the Conditional Formatting menu, you can select the “Use a formula to determine which cells to format” rule type, which will provide you with a box for entering your formula. If, for instance, you wanted to highlight the cell listing the Game number (i.e. cells A2:A5) based on the values of the cells in an adjacent column (column D in this case which lists who the victor was), you could use a formula such as:
Keep in mind that by default, if you click on the cell you are using in the formula, Excel will create an absolute reference (using $ in front of the column letter and row number). If you want your formula to adjust along with the cells that it applies to, you will need to remove these absolute references by simply removing the $ accordingly). Once you’ve got your formula nice and spiffed up, click on the Format button to designate the formatting you wish to apply (I’ve chosen the cell fill color of blue), then hit OK.
Since I only applied this formatting to cell A2, I want to adjust the range of this conditional format, so I will go to the Conditional Formatting menu, and under Manage Rules, I can “stretch out” the range of this condition. Also, if I wanted to apply another conditional format, say to highlight the games that Player 1 had won but in a different color, you can create a new rule with a new format by following the same steps you did before, but adjusting the formula to =IF($D2=”Player 1″,TRUE,FALSE) and changing the cell fill color (I chose red).
You can repeat the same steps to highlight the cells that have a tie (I used a purple cell fill color) with this formula in a new rule:
Make sure to adjust the ranges for the conditional formatting rules and you’ve got a color coded list that adjusts when the scores change.
For more helpful information on using conditional formulas and the IF function, check out this great post from Daniel Ferry who appears to share some love for conditional formulas as well.
• 4 years ago