We are starting a series of 3 articles dedicated to top 9 Excel functions you must know.
This first article of the series is dedicated to IF, AND and OR functions and how we can leverage the performance by combining these functions together.
IF function is a decision-making function that is very helpful in the decision-making situations. For example, let’s imaging that you have a list of clients and respective revenues. You would like to assign a specific category to the client based on their revenues. For example, the clients that provide less than 1 million are considered to be Silver clients. The clients that provide between 1 million and 5 million are considered to be Golden clients and the clients that bring more than 5 million in revenues are Platinum clients.
Think for a second. How would you solve this problem using Excel? Manual assignment of the specific category is not an option.
Well, you can use IF function. Actually there are several ways to solve this problem, but we will use IF function in this case.
To solve our specific problem, the IF function can be the following:
=IF([@[Revenue this year]]<1000000;"Silver";IF([@[Revenue this year]]<5000000;"Golden";"Platinum"))
The result of our function is exactly what we want:
You are probably getting the idea of how IF works, but let’s define the general structure of the function:
=IF(logic_value; value_if_logic_value_is_TRUE; value_if_logic_value_is_FALSE)
You may think: “Wait a minute! But in the example above we had one IF function within another?” Yes, you are right. Actually, the values being return when logic_value of our function is TRUE or FALSE can be any other function. Amongst the variety of functions in Excel, we picked two particularly “friendly” to IF function.
AND and OR functions
AND and OR functions are logic functions that return TRUE or FALSE based on the values of arguments that are passed to these functions.
More specifically, AND function returns TRUE if all arguments, passed to the function, return TRUE. Whereas OR function returns TRUE if at least one argument returns TRUE.
Let’s refer to our example with the companies. One day, boss comes and tells that all clients, who are for more than 10 years with us, are consider as platinum clients regardless of the revenues. At the same time golden clients can only be golden if they have been our clients for more than 5 years and the revenues are more than 1 million.
Now we have additional information and additional requirements.
To get the desirable result, we need to modify our IF formula in the following way:
=IF(OR([@[Revenue this year]]>5000000;[@[Years as our Clients]]>10);"Platinum";IF(AND([@[Revenue this year]]>1000000;[@[Years as our Clients]]>5);"Golden";"Silver"))
Pause for a second and try to understand this formula…
The first argument of the IF function is OR([@[Revenue this year]]>5000000;[@[Years as our Clients]]>10). We can read this formula in the following way: If revenue is more than 5 million OR the client is more than 10 years with our company, then this client is our Platinum client, otherwise analyze the next IF function.
The second IF function has the following argument: AND([@[Revenue this year]]>1000000;[@[Years as our Clients]]>5).
We can read this formula in the following way: If revenue is more than 1 million AND the client is more than 5 years with our company, then this client is our Golden client, all other clients are Silver clients.
Here is how the final result looks:
As you can see Company One and Company ABS changed the Category.
Hope you see how easy it is to use Excel functions. And more importantly how powerful they are.
We believe that IF is one of the most powerful and useful functions in Excel. With the combination of AND and OR functions, IF can solve literally any decision-making problem that the user can face. Moreover, if you are planning to take the next step and dive into VBA world, IF is one of the most useful functions for VBA as well.
More to Read: