This is the situation when we need to bring the result which is based on a lot of specifications.
To understand the same, let’s take an example:
As you can observe in the image above, we have product, colour and sales.
Now, let’s say we need to jot down the products or make a list of all those products which are either in Red or Yellow and have the sales below 20000. (x or y and z – now you get it!)
So, to do the same, we will be using the IF Function, AND Function, OR Function.
The first step is to use the IF Function, i.e. =IF(
The following function requires logical test, value if true and value if false.
The logical test that we will take in the IF Function is the AND Function as shown in the image above.
The following function requires logical 1, logical 2, and so on…
Then to add the logical, we will input the OR Function. We can understand that it would be a little confusing, but once you try it, you will find it easy.
The OR Function i.e.
=OR( requires logical 1, logical 2, and so on…
Now to add the first logic in the OR Function, we will select the colour i.e. cell C3 and we will put the “equal to” sign, after putting the same we will write Red in quotation marks.
Quotation marks are necessary as we are adding specifications in the formula.
So, it becomes C3=”Red”
Then we will come to the second logic, i.e. the colour of Yellow. To add the same in the formula we will repeat the same thing we did for the Red colour.
It becomes: C3=”Yellow”
Then we will close the OR Function and come back to OR Function. As our OR Function was the first logic in the AND Function, so we will come to the second logic.
As we want to highlight those products which have sales below 20000, so to do the same, we will select the sales i.e. cell D3 and use the greater sign for 20000, it becomes:
Then we will close the AND Function and come back to the IF Function. Now that we have inputted the logical test, we come to the point of value if true, so let’s say we want to show “True” on the screen if the following product satisfies the specification. So, to do the same, we will write the same in the formula including the quotation marks just like in the image above.
Then we will write done for the value if false, i.e. “False”.
Again, please do not forget the quotation marks as you are inputting a type of specification of your choice. If you do not put the quotation marks, excel will show an error in your formula and all the efforts will go into vain.
Now all you have to do is close the IF Function and it will complete your formula.
Overall, your formula becomes as follows:
Now all you have to do is hit “enter”.
And BAAAAAMMMM!!! Here comes your answer on the screen i.e. False. This means that the product 231 does not satisfy your requirement (Product should be in either Red or Yellow colour and should have sales below 20000).
Now, all you have to do is drag the formula down till you need to use it, it will make your work easier. Now, you can easily find the product that satisfies your requirements or specifications.
It’s easy, isn’t it?