Conditional Formulas in Excel & Tricks Using the IF Function in Excel

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”)

Conditional formatting

Formatting cells based on multiple criteria outside of the standard function

With Excel 2007, 2010 & 2013’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:

=IF($D2=”Player 2″,TRUE,FALSE)

=AND(MONTH(C2)=MONTH(TODAY()),D2=”Sales”)

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:

=IF($D2=”Tie”,TRUE,FALSE)

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

If you liked this article, please share it!

Also read How to Decide Which Excel Lookup Formula to Use –
Top 10 Tips & Best Practices to Optimize and Speed-up Excel Formulas

---

Courtesy: brimagency.com

– By Daniel Ferry, Excel Hero, MVP

Do you love IF()?

The IF worksheet function in Excel is essential.

In my experience, after SUM and AVERAGE, it is one of the first functions that budding Excel users discover with an Eureka moment. They start feeling empowered and realize for the first time that they can control Excel and make interesting things happen in their spreadsheet.

IF is very important and sometimes a situation demands that the logic of a compound formula branch, i.e. if some situation, then do this, otherwise do that.

So what are some alternatives to IF()?

I’ll give you three, none of which have an arbitrary limit of conditions.

The first two are fairly simple: Table Lookup and Boolean Logic.

Suppose you had a situation where you wanted your formula to use a certain value based upon the contents of the Cell A2. You might have a nested formula that looks something like:

=IF(A2=”red”,25,IF(A2=”white”,101,IF(A2=”blue”,74,0)))

A very simple formula, it results in a value of 25 for red in A2, 101 for white, 74 for blue, and finally 0 for any other color or no color at all. But what if you had more than seven colors where you needed to supply values in this formula. It simply would not work. In fact Excel won’t even let you enter such a formula: when you hit enter it will pop up an error message informing you that you cannot nest the IF function to that many levels and the formula will be summarily rejected (Excel 2007 will let you get away with it, but please don’t). A much better idea would be to create a lookup table somewhere in your workbook with one column for all of your colors and another for all of their corresponding values. Then you could do a simple Vlookup of the table to return the correct value for any color. The table could be extended to thousands of rows and our formula would stay just as concise as it was for three colors. Excel has quite a few functions that can lookup values in such tables not just Vlookup.

The second approach uses boolean logic. The following formula produces the exact same result as our original nested IF formula, but notice that it uses no IF functions at all:

=(A2=”red”)*25 + (A2=”white”)*101 + (A2=”blue”)*74

This is a much more elegant formula. It’s shorter. It’s easier to read. And, it’s easier for Excel to calculate. There is no branching at all. There’s one phrase for each color and the calculation engine just plows through and adds up the totals for each phrase. So how does this work? Consider the following:

=(A2=”red”)

This simple comparison will result in a value of TRUE if A2 has “red” in it (just to be clear: the string of letters, not the background color). If there is anything else or even nothing in A2, then the result will be FALSE. True and False are actual values in Excel. If you were to enter this formula in some cell, you would see the word TRUE or the word FALSE in that cell, depending on what was in A2. It’s kind of interesting when you think that only one value out of an infinite number of values in A2 will result in a True. False will be the result for anything else (is that infinity minus one?)

I need to point out that this type of comparison does not distinguish between upper and lower case, so “Red”, “RED”, “rEd”, and “reD” will all be counted as “red.” If case sensitivity is important in your circumstance you should change the formula to =EXACT(A2,”red”). The EXACT() function will return True only if the two strings are identical including the case of each character. But for now let’s assume that case is irrelevant as it often is.

You’ll notice that our first phrase in my boolean logic replacement formula above has the “*25” appended. Here we are exploiting the fact that Excel stores the value of True internally as the number 1 and the value of False as the number 0. Let’s assume that the string “blue” was in A2 and watch below how Excel gets to the correct result:

=(A2=”red”)*25 + (A2=”white”)*101 + (A2=”blue”)*74

=(False)*25 + (False)*101 + (True)*74

=(0)*25 + (0)*101 + (1)*74

=74

In practice, since there is no branching in the logic, Excel can calculate this style of formula at lightning speed, and as in the Table Lookup method, there is no limit to the number of phrases (colors in this case) you can include in the formula. This method is potent and will be used extensively in a vast variety of situations here at Excel Hero.

So there you have two fairly simple alternatives to blindly using the IF function. Both have no limits on the number of conditions, and both are much easier to read and easier for Excel to execute. In our mantra of “interactive, faster, shorter, easier to maintain, or simpler” they trump the nested IF approach. If the IF function has made you feel empowered, just wait until you master these two techniques!

Ok. I said I would share three alternative techniques. The third technique is to encode multiple answers into one integer value and to decode them using bit inspection and a bitmask. This is the most potent tactic of all, but it’s way beyond the scope of this post.

Finally remember that IF() is not evil, just precious. There are times when it’s unavoidable, and it would be a shame to hit the nesting limit simply because several layers of unnecessary nesting have already occurred in a formula. Personally, I make a game out of it and pride myself on using as few (mostly none) IF functions as I can. Try it. You’ll soon be treating the IF function as if it were a precious jewel not to be squandered, and as a byproduct your spreadsheet models will be much better.

As a footnote to this post I’d like to point out that prior to Excel 2007, one of the uses of the IF function that was unavoidable was error catching. Say you developed a beautiful custom formula and it produced the perfect result except that sometimes because of inputs beyond your control, the formula resulted in one of Excel’s error values. In addition your model required that the result of your fancy custom formula be used as an input somewhere else, and the error values cascaded causing errors all over the place (error in = error out). The only fix for this type of scenario is to trap the error with an IF function that looks something like this:

=IF(ISERROR(MyFancyFormula),0,MyFancyFormula)

Heinous, to be sure. It requires doubling your fancy formula just to prevent Excel’s error message from laying havoc to our model; and it forced us to use an IF function. Thankfully, in Excel 2007, Microsoft introduced a new function called IFERROR(). So now the Excel calc engine does not need to calculate MyFancyFormula twice just to accommodate the possibility of an error. Here is how the same situation is handled in Excel 2007 and 2010:

=IFERROR(MyFancyFormula,0)

If you liked this article, please share it!

Also read Microsoft Excel: Useful Functions You Should Get to Know

---

Courtesy: excelhero.com