IF Formula with Four Conditions

IF Formula with Four Conditions

Many insights derived from data analyses include determining the number of observations that meet specific characteristics. For example, an analysis of a person’s ideal automobile purchase may require that the price is below a specific dollar amount, the engine have a minimum horse power rating, and the transmission to be automatic. While it is possible to scan through the data and mark each observation that meets these requirements, a simpler and faster way is to use an IF/AND or IF/OR function combination. This lesson will explain how to properly utilize this combination.

The IF Formula:

=IF(logical_test, [value_if_true], [value_if_false])

Let’s begin with a review of the IF formula by itself. This nifty formula in Excel allows the user to specify a logical condition, either numerical or textual, and returns a value, also specified by the user, for whether the condition is met (TRUE) or not met (FALSE). The statement has three parts, each separated by a comma, as shown in the formula above: the logical test, the value if true, and the value if false. Continuing with the automobile purchase example, take a look at the following worksheet:

clip_image002

Please note that the values shown are not actual market data (I made them up!). Regardless, cell H2 shows the IF formula to determine whether or not I can afford the Jeep Patriot. The first part of the formula, the “logical test,” checks to see if the price listed for the Patriot in D2 is less than the amount that I feel comfortable paying: $20,000. In the next part, I have specified that I want to see the word “YES” if the price is indeed below $20,000 and the word “NO” if it is not (if it is equal to or greater than $20,000).

Notice that I used quotation marks around these words. If you wish to have text returned from the IF formula, you must put quotes around the text that you want for the true and false values. A common use of the IF formula in data analytics is for returning binary values of either 1 or 0. For these numerical outputs quotes are not needed; in our example, simply replace the “YES” with a 1 and the “NO” with a 0 (without quotation marks).

Let’s return to our worksheet and see what happens after we type the formula and hit enter:

clip_image004

Because the price of the Patriot in D2, $25,000, was not less than $20,000, the formula returned the value that I specified if the condition were not met: “NO.” Now that our formula is set up, we can drag down the column and determine the affordability of each of the listed vehicles:

clip_image006

We can see that based on my budget of $20,000 I can only afford the Honda Civic, Toyota Camry, or Kia Rio. I’m that much closer to finding my ideal automobile, but to truly determine which one I want to buy, I will need to assess more conditions. We will do this together in the next section of this lesson, where we will combine the IF and AND formulas.

The IF Formula with Multiple Conditions using the AND Formula:

=IF(AND(logical_test 1, logical_test 2, …), [value_if_true], [value_if_false])

As the title of this tutorial suggests, we want to learn how to use the IF formula with at least four conditions. So far we have seen one condition: price must be less than $20,000. However, like many other data analyses, my ultimate decision is not determined by one aspect of the observations alone. Beyond making a purchase that fits my budget, I also would like a vehicle that has a model year of 2013 or newer, has a horse power of at least 145, and has an automatic transmission. These three characteristics will be the additional conditions that the vehicles will need to fulfill for them to be ideal for purchase.

Notice that all of these conditions must be met in order for the vehicle to be ideal for purchase. There are multiple ways to determine such things in Excel, including (the utterly barbaric way of) scanning through the data and manually marking those that meet all of the conditions, or creating an IF formula in each column that focuses on a single condition and then aggregating those results. Neither of these methods is as efficient and elegant as combining the AND formula with the IF formula.

Look at the worksheet below. You’ll see that I have added another column “Ideal” next to that of “Can I Afford?” where I determined whether each vehicle was in my price range:

clip_image008

The formula in cell I2 may look a bit hairier than the one we saw before in H2, but I have to tell you, with utmost sincerity, that it is not. Below I have written the formula for the IF/AND combination from I2:

=IF(AND(D2<20000,C2>2012,E2>145,F2=“Automatic”), “YES”, “NO”)

Now watch this formula transform into our original simple IF formula that checked each vehicle’s price condition:

=IF(AND(D2<20000,C2>2012,E2>145,F2=“Automatic”), “YES”, “NO”)

Here’s the secret: the AND formula takes the place of the logical test in the IF formula. Then within the parenthesis of the AND formula, you can write as many conditions (logical tests) as you want, each separated by a comma. The AND formula by itself will return either a “TRUE,” if all the logical tests are met, or a “FALSE,” if even one is not met. As such, the IF formula around the AND will let you specify your values for a return of “TRUE” or “FALSE” as we did in the first section.

Okay, now let’s see what happens when I hit enter and we assess whether or not the Jeep Patriot is the ideal vehicle for me:

clip_image010

“NO.” This outcome should not surprise anyone. Recall that my ideal vehicle has four conditions:

1) It costs less than $20,000:                            D2<20000

2) It is newer than a 2012 model:                   C2>2012

3) It has at least 145 horse power:                 E2>145

4) It has automatic transmission:                   F2=“Automatic”

While, it does pretty well, hitting points 2 through 4, it fails the first condition, that of price. It is not ideal as I have defined it. Now let’s drag down the formula for each of the other nine vehicles and see if there are any that I would consider ideal:

image

PHEW! Thank Goodness! Row 9 contains a Kia Rio that returned a “YES.” Let’s see why:

1) It costs less than $20,000:                             D9<20000

2) It is newer than a 2012 model:                     C9>2012

3) It has at least 145 horse power:                   E9>145

4) It has automatic transmission:                     F9=“Automatic”

Looks like I’ve got a sporty Rio in my future.

The IF Formula with Multiple Conditions using the OR Formula:

=IF(OR(logical_test 1, logical_test 2, …), [value_if_true], [value_if_false])

Now that we’ve reviewed IF formulas and the IF/AND combination, we can quickly cover another combination: the IF/OR formula. Take a look at the equation above. Look familiar? It should. In structure, this combination is exactly the same as its AND-containing cousin. The difference is in its operation. Whereas the IF/AND combination will only return the value that you specify for “TRUE” if ALL of the logical arguments hold true, the IF/OR combination will return the value that you specify for “TRUE” if ANY of the logical arguments are true.

Going back to my vehicle purchase example, I’ve just realized that my conditions have led me to an ideal vehicle that I never thought I’d own in a million years: a Kia Rio. Therefore, I decide that I need to reevaluate my criteria. I determine that horse power and model year are immaterial. Again I focus on my budget and am rather adamant about not going above $20,000. However, I would be willing to break this budget, if I could get the vehicle with an automatic transmission. Here’s the spreadsheet again:

clip_image015

Notice that “AND” has been replaced by “OR” and I have dropped two of the four logical tests, so that only price and transmission are being assessed, but otherwise the formula follows the same structure. Let’s skip ahead and apply the formula to each vehicle:

clip_image017

We can see that there are a few cells that contain “YES” in this column, including the Jeep Patriot. Let’s see why:

1) It costs less than $20,000:                     D9<20000

2) It has automatic transmission:             F9=“Automatic”

While the Jeep had been excluded on price in the first and second analyses, our swap of “AND” for “OR” allowed the program to return a “TRUE” value (a “YES”) because the Jeep did meet one of the conditions, an automatic transmission.

A Quick Summary:

Note the differences between the cells in the “Can I Afford?,” “Ideal,” and “Good Enough” columns in the last graphic.

In the first, we used the IF formula to determine if we could afford each automobile; if the price was less than $20,000. With this simple, single condition IF formula, we found that the Honda, Toyota, and Kia would be affordable purchases.

In the second, we sought our ideal vehicle, by specifying that we wanted to see a “YES” if it were less than $20,000, newer than 2012, had at least 145 horse power, and had an automatic transmission. We found that one car was ideal: the Kia Rio.

In the third, we decided that horse power and transmission were not that important. Instead, we decided that we did not want to spend more than $20,000, unless the vehicle had an automatic transmission. This allowed for several other options, besides those that we determined we could afford under the first column; the Jeep, Subaru, and Lincoln are all considered good enough because they have automatic transmissions (though they are still overpriced).