Formula Fridays are short reads, spotlighting a single formula that you can start using straight away.
Sometimes you will need an IF statement to be dependent on more than one logical test, at the same time. For example, you might need the product category to be “Dress” and the length to be “Mini” in order for the item to be classified as a “Short Dress”.
If you haven’t had a chance yet to read my previous post on nested IFs. I would recommend having a look at that first, as it will help give more context to what will be discussed in this post.
The key ingredient to remember is that the AND function needs to be nested, inside the IF function. The same way your sentence out loud would be “IF it’s a dress AND it has sequins, it’s a great dress”.You wouldn’t say “it’s a dress AND it has sequins IF it’s a great dress”. Well, you might, but then you would correct yourself. Or someone would ask you what you’re talking about.
Just to make sure no stone is left unturned. Here is an example of the AND Function, nested inside of an IF statement.
=IF(AND(D2=“DRESS”,J2=“Knitted”),“Jersey Dress”,“Woven Dress”)
IF the item is a dress AND the fabric construction is knitted it is a jersey dress.
Sometimes a gorgeous dress can have sequins and be either a mini or a maxi. For that we need to bring in the OR Function, that Excel knows there are different variables to the logical test.
=IF(AND(I2=“Sequin”,OR(G2=“Mini”,G2=“Maxi”)),“Gorgeous dress”,“Lovely dress”)
In this example we have replaced what was previously a fixed AND option, with the OR function. Which enables us to tell Excel there are different variables for the logical test.
There will be instances when your classification will depend on one thing OR another. One great example of this in action is when it comes to ranging.
Often stores will be grouped together based on their sales performance. Which is a process known as tiering and grading. And these grades or tiers are what will help inform how much stock and number of stock options is bought at different store levels.
However, there will be instances when you will need to group the tiers or grades at a top line level. To determine if a style is All store – available in all stores, Mid store – available in most stores or Top store – available in select stores, for example.
This can be done by combining the IF and the OR Functions.
=IF(OR(D2=“MAT2”,D2=“TWN1”,D2=“TWN2”),“ALL STORE”,0)
Right now this formula will only tell us if a style has been ranged to All store. Anything that does not match the logical test of the OR Function will present as 0. But that’s not what we want. We want is to be able to see how far down the chain each of these styles are ranged to.
I like to write out, or sound out, what I need the outcome of a nested IF to be. Because they can get very long and quite confusing.
We have our hypothetical list of store tiers in our ranging column, column D. And we need to match them up with what they relate to with regards to All store, Mid store and Top store.
We already have the beginning part of the formula
=IF(OR(D2=“MAT2”,D2=“TWN1”,D2=“TWN2”),“ALL STORE”,0)
To expand it to include the classification options for MID STORE, we will replace the 0 with another IF statement.
=IF(OR(D2=“MAT2”,D2=“TWN1”,D2=“TWN2”),“ALL STORE”,IF(OR(D2=“CTY3”,D2=“CTY2”),“MID STORE”
Here we are saying if the ranging is MAT2, TWN1 OR TWN2, input ALL STORE. If it is not any of those but it is either CTY3 OR CITY2, input MID STORE
Next we need to extend the formula a final time with to include the classification option for TOP STORE. And we will do this with another IF statement.
=IF(OR(D2=“MAT2”,D2=“TWN1”,D2=“TWN2”),“ALL STORE”,IF(OR(D2=“CTY3”,D2=“CTY2”),“MID STORE”,IF(OR(D2=“CTY1”,D2=“TRL”),“TOP STORE”)))
IF statements are one of the most useful tools in Excel. Whether they are used on their own, nested together or alongside other functions such as the AND/OR Functions. You are guaranteed to get clearer data insights. And be able to quickly identify your data categories.
How to use the Substitute Function Formula Friday #18 Formula...
Read MoreHow to HLOOKUP Formula Friday #7 Formula Fridays are short...
Read MoreFinding an exact match for your needs on the internet can, at times, feel like trying to find the missing pair to your odd sock. You know it’s in the drawer somewhere, but you just can’t see it for love nor money. That’s where I can help, think of me as your Excel Agony Aunt, send in your lonely heart letters and I will find your perfect formula partner.
I can help bring your ideas to life. Let’s talk about what we can build together.
