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

**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:**

Four Simple Steps to Start With Your Very First Excel Macro

## Comments

Add a new comment - No HTMLYou must be logged in and verified to post a comment. Please log in or sign up to comment.