Excel 101: IF, AND, OR Features and Conditional Logic Defined

You studying this tells me you want to study extra about Excel. This text continues our Excel sequence, the place we explored the VLOOKUP operate within the final iteration. The whole VLOOKUP information demonstrated how the operate works and the way greatest to make use of it. This time, we will carry the identical focus to conditional logic and formulation just like the IF operate in Excel. The goal is to know the several types of conditional logics and know the way to use their operators in a working operate inside Excel.

So, no fluff wanted right here. Let’s merely dive in, beginning with what Conditional Logic in Excel is.

What’s Conditional Logic in Excel?

Conditional logic in Excel means making selections based mostly on a situation. In easy phrases, Excel checks a rule you outline, evaluates the outcome, after which performs an motion based mostly on that end result.

For instance, suppose you might have college students’ marks in a sheet and wish to determine whether or not a pupil has handed or failed. Slightly than checking every worth manually, you possibly can merely apply a situation: if the marks are 40 or above, return “Cross”; in any other case, return “Fail”. That’s conditional logic in motion.

The identical logic is used throughout many real-world duties in Excel. You would possibly wish to mark gross sales above a goal as “Achieved”, classify bills as “Excessive” or “Low”, or determine whether or not a fee is “Pending” or “Accomplished”. In every case, Excel is evaluating a situation and returning an output based mostly on the outcome.

On the core of this course of is an easy concept:

check a situation > get a TRUE or FALSE outcome > use that outcome to resolve what occurs subsequent.

Such conditional logic is strictly what makes Excel greater than only a spreadsheet for storing information. Its formulation react to values dynamically, slicing down on hours of guide work.

To make this conditional logic work, Excel depends on conditional operators, that are the symbols used to check values. Subsequent, allow us to study conditional operators intimately.

Additionally learn: 50+ Excel Interview Inquiries to Ace Your Interview

What are Conditional Operators in Excel?

Give it some thought, how precisely will you evaluate values inside Excel for any conditional logic to work? You have to comparability symbols for various circumstances, like equal (=), larger than (>), smaller than (<), and many others., proper? All such comparability symbols are referred to as conditional operators in Excel. In essence, these are used to check whether or not a situation is true or false. They’re the constructing blocks behind conditional logic, as a result of they permit Excel to check values earlier than a operate decides what to return.

In easy phrases, these operators assist Excel reply questions like:

• Is that this worth larger than 50?
• Is that this cell equal to “Sure”?
• Are these two values totally different?
• Has the goal been met or not?

Excel helps six predominant conditional operators:

• `=` : equal to
• `>` : larger than
• `<` : lower than
• `>=` : larger than or equal to
• `<=` : lower than or equal to
• `<>` : not equal to

Allow us to perceive this with a easy instance. Suppose cell `A2` comprises the worth `75`.

=A2>50

Excel checks whether or not 75 is larger than 50. Since that situation is true, the method returns `TRUE`.

Now take a look at this:

=A2<50

This time, Excel checks whether or not 75 is lower than 50. Since that isn’t true, the result’s `FALSE`.

That `TRUE` or `FALSE` output is what powers conditional formulation in Excel. Features like `IF`, `IFS`, `AND`, and `OR` depend on these comparisons to make selections.

For instance:

=IF(A2>=40,"Cross","Fail")

Don’t fear, we’ll study concerning the IF operate intimately shortly. For now, simply word on this instance that Excel first checks whether or not the worth in `A2` is larger than or equal to 40. If the situation is true, it returns `Cross`. If the situation is fake, it returns `Fail`. Extra importantly, word that even the IF operate begins with a conditional operator.

So, whereas capabilities like `IF` typically get all the eye, the true decision-making begins with these operators. They’re what inform Excel the way to consider a situation within the first place.

Now that the operators are clear, the subsequent step is to know the conditional capabilities during which they’re used, beginning with the `IF` operate.

Additionally learn: Microsoft Excel for Information Evaluation

IF Operate in Excel

The IF operate is among the most generally used formulation in Excel. In its most elementary sense, it checks whether or not a situation is true or false, after which returns a outcome based mostly on that end result. In easy phrases, it tells Excel: if this occurs, do that; in any other case, do this.

To grasp it correctly, allow us to break it into two components.

IF Operate Syntax

The syntax of the IF operate is:

=IF(logical_test, value_if_true, value_if_false)

Right here, every half has a particular position:

• logical_test is the situation Excel checks
• value_if_true is the outcome returned if the situation is true
• value_if_false is the outcome returned if the situation is fake

Allow us to take a look at a easy instance:

=IF(A2>=40,"Cross","Fail")

Here’s what Excel is doing on this method:

• It first checks whether or not the worth in cell A2 is larger than or equal to 40
• If that situation is true, Excel returns Cross
• If that situation is fake, Excel returns Fail

So, if A2 comprises 65, the outcome might be Cross. If it comprises 28, the outcome might be Fail.

That is the fundamental construction of each IF method. First, Excel evaluates the situation. Then it decides which outcome to return.

Forming the Method

Now that the syntax is obvious, the subsequent step is to truly construct the method in Excel.

Suppose you might have marks listed in column A, and also you wish to present the end in column B.

Begin by clicking the cell the place you need the output to seem. Then kind:

=IF(A2>=40,"Cross","Fail")

Press Enter, and Excel will immediately return the outcome based mostly on the worth in A2.

For the reason that worth meets the situation on this case, you get ‘Cross’. If it didn’t, you’ll get ‘Fail’.

As soon as the method works in a single cell, you possibly can drag it down to use the identical logic to the remainder of the rows. Excel will robotically modify the cell reference for every row.

As an example:

• in row 2, Excel checks A2
• in row 3, it checks A3
• in row 4, it checks A4

That is what makes the IF operate so helpful. You create the logic as soon as, and Excel repeats it throughout the dataset in seconds.

Now that we perceive how a single IF method works, the subsequent step is to see what occurs when there are greater than two potential outcomes. That’s the place Nested IF statements are available.

Nested IF Statements in Excel

A single `IF` operate works nicely when there are solely two outcomes. However many actual Excel duties contain greater than only a yes-or-no determination. You could must assign grades, label efficiency bands, or categorise values into a number of teams. That’s the place Nested IF statements are available.

A Nested IF merely means inserting one `IF` operate inside one other, so Excel can check a number of circumstances one after the opposite.

Nested IF Syntax

Contemplate a easy Excel sheet that has the marks of scholars saved as information, and you must grade the scholars based mostly on their marks. A fundamental Nested IF method for a similar will look one thing like this:

=IF(A2>=90,"A",IF(A2>=75,"B",IF(A2>=40,"C","Fail")))

This may increasingly look intimidating at first, however the logic is easy. Excel checks every situation in sequence:

• If `A2` is 90 or above, it returns `A`
• If not, it checks whether or not `A2` is 75 or above, and returns `B`
• If not, it checks whether or not `A2` is 40 or above, and returns `C`
• If none of those circumstances are met, it returns `Fail`

So if `A2` comprises 82, the method returns `B`. If it comprises 36, Excel returns `Fail`.

The important thing factor to know right here is that Excel stops as quickly because it finds the primary true situation. It doesn’t hold checking the remaining.

Forming the Method

Suppose you might have pupil marks in column `A`, and also you wish to assign grades in column `B`.

Click on the output cell and enter:

=IF(A2>=90,"A",IF(A2>=75,"B",IF(A2>=40,"C","Fail")))

Then press Enter.

Excel will consider the circumstances from left to proper and return the right grade for that row. As soon as the method works, drag it down to use the identical grading logic to the remainder of the information, as seen within the picture beneath.

One essential factor to recollect: the order of circumstances issues. Within the instance above, the very best rating vary is checked first. Should you reverse the order carelessly, Excel might return the unsuitable outcome.

Nested IF statements are helpful, however they will turn out to be tough to learn when too many circumstances are concerned. That’s precisely why Excel launched a cleaner different referred to as `IFS`.

Additionally learn: 10 Most Generally Used Statistical Features in Excel

IFS Operate in Excel

Think about if, within the grading instance above, you had grades as much as Z handy out. The Nested `IF` statements might get the job finished, however will certainly turn out to be very messy, in a short time. When you begin stacking a number of circumstances inside each other, the method turns into more durable to learn, more durable to edit, and simpler to interrupt. That’s the place the `IFS` operate helps.

The `IFS` operate is designed to check a number of circumstances in a cleaner format. As an alternative of nesting one `IF` inside one other, you checklist every situation and its end in sequence.

IFS Operate Syntax

The syntax of the `IFS` operate is:

=IFS(logical_test1, value_if_true1, logical_test2, value_if_true2, ...)

Every logical check is adopted by the outcome Excel ought to return when that situation is true.

Allow us to take the identical grading instance we utilized in Nested IF:

=IFS(A2>=90,"A",A2>=75,"B",A2>=40,"C",A2<40,"Fail")

Here’s what Excel does:

• If `A2` is 90 or above, it returns `A`
• If not, it checks whether or not `A2` is 75 or above, and returns `B`
• If not, it checks whether or not `A2` is 40 or above, and returns `C`
• If `A2` is beneath 40, it returns `Fail`

The logic is just like Nested IF, however the construction is way cleaner. You don’t have to maintain monitor of a number of closing brackets inside brackets.

Forming the Method

Suppose marks are listed in column `A`, and also you need grades in column `B`.

Click on the output cell and sort:

=IFS(A2>=90,"A",A2>=75,"B",A2>=40,"C",A2<40,"Fail")

Then press Enter.

Excel will check the circumstances so as and return the outcome for the primary situation that evaluates to true. After that, you possibly can drag the method down for the remainder of the rows.

This makes `IFS` particularly helpful when you might have a number of potential outcomes and wish the method to remain readable.

That stated, `IFS` is greatest if you find yourself checking a number of separate circumstances. However typically the problem will not be a number of outcomes. Typically you wish to check multiple situation on the similar time. For that, Excel makes use of `AND` and `OR` capabilities.

AND and OR Features in Excel

To date, we have now checked out formulation the place Excel checks one situation at a time. However in actual spreadsheets, a single situation is commonly not sufficient. You might have considered trying a outcome solely when a number of circumstances are true, or when at the least one out of a number of circumstances is true. That is the place `AND` and `OR` are available.

Each are logical capabilities in Excel, and they’re normally used inside formulation like `IF`.

AND Operate Syntax

The `AND` operate returns `TRUE` solely when all circumstances are true.

Its syntax is:

=AND(logical1, logical2, ...)

Allow us to say a pupil passes provided that they rating greater than 40 in principle and greater than 40 in sensible.

=AND(A2>40,B2>40)

=IF(AND(A2>40,B2>40),"Cross","Fail")

=OR(logical1, logical2, ...)

=OR(A2>=100000,B2="Sure")

=IF(OR(A2>=100000,B2="Sure"),"Bonus Eligible","Not Eligible")

=IF(AND(A2="Sure",B2="Sure"),"Accepted","Pending")

=IF(OR(A2="Sure",B2="Sure"),"Accepted","Pending")

=IFERROR(worth, value_if_error)

=IFERROR(A2/B2,"Error in Calculation")

=IFERROR((B2-A2)/A2,"Not Out there")

=IFNA(worth, value_if_na)

=IFNA(VLOOKUP(E2,A2:C10,2,FALSE),"Not Discovered")

=VLOOKUP(E2,A2:C10,2,FALSE)

=IFNA(VLOOKUP(E2,A2:C10,2,FALSE),"Product Not Discovered")

Additionally learn: Superior Excel for Information Evaluation

Conclusion

As you begin utilizing these formulation in your Excel sheets extra typically, you’ll realise the period of time every of those can prevent. These capabilities are what make Excel really feel like a working determination system. As an alternative of simply storing numbers and textual content, Excel can consider circumstances, apply guidelines, and return the correct solutions robotically. Therefore, these formulation like `IF`, `IFS`, `AND`, `OR`, `IFERROR`, and `IFNA` have a lot sensible worth.

To sum up, the `IF` operate is the start line if you want Excel to decide on between two outcomes. Nested `IF` helps when these outcomes improve. `IFS` provides a cleaner strategy to deal with a number of circumstances with out turning the method right into a bracket jungle. `AND` and `OR` take the logic additional by permitting you to check a number of circumstances collectively, relying on whether or not all or simply certainly one of them must be true. Lastly, `IFERROR` and `IFNA` assist make your spreadsheets extra readable by changing error messages with helpful outputs.

Since they’ve such excessive sensible worth, the true advantage of studying these capabilities is the flexibility to make spreadsheets smarter, cleaner, and much more helpful in actual work. When you perceive how conditional logic works, you realise the ability of Excel with regards to deciphering information.