To get pleasure from Excel absolutely, you could know learn how to use the IF-THEN perform.

person using if then statements in excel

Download 10 Excel Templates for Marketers [Free Kit]

The IF-THEN is one in all Excel’s most spectacular capabilities, as you should utilize it to investigate information, draw inferences and make choices below sure preset situations. However what precisely are IF-THEN statements, and how will you begin utilizing them?

On this information, we’ll present you learn how to use IF-THEN statements to enhance your mastery of Excel.

Desk of Contents

What are IF-THEN Statements in Excel?

IF-THEN statements are capabilities in Excel that return a singular set of actions after your preset situation is met.

Merely put, while you use the “If” assertion, you’re setting a situation and instructing Excel to carry out a singular set of actions when your preset situation is true and one other when the situation is fake.

To make use of the “If “ perform in Excel, you begin by writing the equal signal (=), adopted by “if,” then your situation, and a two-part syntax instructing this system on what to do when your necessities are or aren’t met.

The syntax normally appears like this:

  • =IF(logical_test, [value_if_true], [value_if_false])

writing if then statements in excel, if then statements in excel with text

The “If” perform is helpful, particularly when you’ve got a big information quantity and wish to keep away from the stress of computing formulation for every information. Right here’s an instance of how easy it’s to make use of IF-THEN statements in Excel:

On this instance, I’ve a spreadsheet containing three columns of knowledge. The place column A accommodates title information, and columns B and C, comprise integer information.

Utilizing the If perform, I set a situation utilizing the syntax:

  • =IF(B1>C1, “sure,” “no”)

This perform initiates a command telling the Excel program to check if the information worth in cell B1 is larger than the information worth in cell C1.

I’ve merely instructed Excel to provide me a response of “sure” below the situation that the worth of cell B1 is larger than cell C1 and a response of “no” below the situation that the worth of cell B1 is lower than the worth of cell C1.

I then prolonged this perform to the opposite respective cells utilizing a easy autofill trick. Take a look at our information on using Excel like a pro for some useful tips to make your Excel expertise simpler.

It’s vital to notice that the IF-THEN assertion solely means that you can set a situation for 2 variables. Meaning you possibly can solely set situations together with, say, the information worth in cells B1 and C1 or a situation together with information values in cell B1 and an outlined integer or textual content.

For instance, you possibly can set a situation instructing Excel to return a response of “Sure” if the worth in cell B1 is larger than the worth of cell B2 as proven within the instance above. Or, you possibly can set a situation asking Excel to return the response ”sure” or “no” if the worth of cell B1 is larger or lower than 1000.

Right here’s a fast instance:

writing if then statements in excel, if then statements in excel with text

Right here, I informed Excel to return the response “sure” if the information worth in cell C1 is larger than 5000 and “no” if the worth is lower than 5000.

Right here’s the response Excel returned:

writing if then statements in excel, if then statements in excel with text

As talked about earlier, the IF-THEN assertion is a strong Excel perform with quite a few advantages. Studying to make use of this perform correctly will astronomically improve your mastery of Excel.

The Advantages of IF-THEN Statements In Excel

Listed below are some advantages that IF-THEN statements in Excel can give you:

Checking for Errors in Knowledge

You should use IF-THEN statements to examine for errors when inputting information into the spreadsheet.

For instance, should you really feel like you’ve got made a mistake by inputting a quantity as a substitute of a textual content into your spreadsheet, you possibly can examine utilizing an if perform:

  • IF(B1 = “”, “Textual content”, “Quantity”)

Calculating Debt Schedule or Depreciation Schedule

Monetary Analysts can use IF-THEN statements to calculate debt and depreciation schedules.

Utilizing the IF-THEN assertion, you possibly can set situations asking this system to return a response for months with excellent balances and even draw up a compensation schedule.

For Budgeting

As a monetary analyst, utilizing IF-THEN statements would make budgeting simpler. IF-THEN capabilities will assist you to create as much as 64 situations, which might help you in your budgeting course of.

To Arrange Knowledge

The first capability of IF-THEN statements is setting directions below sure situations. Thus it’s simple to arrange information in your spreadsheet below sure situations utilizing if statements.

You possibly can set situations for a specific cell field or a number of cells and manage your spreadsheet information below stated situations.

Works Effectively With Different Formulation

An thrilling benefit of IF-THEN statements is their flexibility. Attributable to its flexibility, you should utilize IF-THEN statements with different formulation in Excel.

For instance, you should utilize it with the DATEVALUE() perform, the SUM() perform, and even the COUNT() perform.

Use IF-THEN Statements in Excel

Now that you recognize what an IF-THEN assertion is and the way it can profit you, the following step is understanding learn how to use it correctly.

There are two strategies to make use of the perform, and we’ll begin with the fundamentals. First, right here’s a easy step-by-step process for writing IF-THEN statements in Excel.

Step 1: Click on on the cell you want to format, say cell A1.

Step 2: Write the IF-THEN perform components instantly into the cell field.

writing if then statements in excel, if then statements in excel with text

Or within the components field. writing if then statements in excel, if then statements in excel with text

Step 3: Start writing your situations and anticipated responses to stated situations.

writing if then statements in excel, if then statements in excel with text

Keep in mind: All the time begin the components with an “equal” signal and enter the suitable punctuation so that you don’t get an error message after inputting the perform.

Right here’s an alternate step-by-step you should utilize.

Various Step 1: Click on on the “Formulation” tab within the taskbar menu of your Excel program.

writing if then statements in excel, if then statements in excel with text

Various Step 2: Click on on the “Insert Perform” possibility, which can launch a dialog menu:

 writing if then statements in excel, if then statements in excel with text

Various Step 3: Choose “IF” from the record of choices within the dialog menu and click on “OK.” It should launch a dialog field.

writing if then statements in excel, if then statements in excel with text

Various Step 4: Within the dialog field, enter your situation and directions telling this system what solutions to return when the situation is met and when it isn’t met.

writing if then statements in excel, if then statements in excel with text

Observe: It’s higher to make use of the “Insert Perform” methodology should you’re writing a easy IF-THEN perform, because it launches a components builder that may show you how to remove the dangers of errors when inputting your components.

This methodology will robotically enter the suitable punctuations and codecs and solely require you to enter your situations and directions within the three fields of the dialog field.

Now that you understand how to start out with the IF-THEN perform, let’s overview the opposite use instances for if capabilities.

Writing IF-THEN Perform for texts

writing if then statements in excel, if then statements in excel with text

IF-THEN capabilities for texts are common IF-THEN capabilities, however on this case, the perform checks for a string of textual content after which returns a preset response relying on whether or not the situation is fulfilled.

Utilizing the IF-THEN perform is kind of easy, and we’ve touched on it just a few instances within the examples to date. Nonetheless, right here’s a step-by-step process for writing IF-THEN statements in Excel with textual content.

Step 1: Click on on the cell field the place you wish to insert the perform.

Step 2: Write the “equal” (=) signal.

Step 3: Begin writing the if assertion, together with the situations for the take a look at and the responses to be returned.

Step 4: After writing the if assertion, click on enter.

Right here’s an instance displaying learn how to generate a comment stating who handed or failed a course for a first-year class utilizing IF-THEN statements in Excel with textual content:

On this instance, utilizing the IF-THEN perform:

  • If(B2 = “Move,” “Scored above 50”, “Didn’t rating above 50”)

This system was requested to return a response of “Scored above 50” or “Didn’t rating above 50” if the string of textual content in cell B2 is the same as “Move.”

Nonetheless, we will take a look at the opposite means round utilizing the perform:

  • If(B2 <> “Move,” “Scored above 50”, “Didn’t rating above 50”)

This system was requested to return a response of “Scored above 50” or “Didn’t rating above 50” if the string of textual content in cell B2 just isn’t equal to “Move.”

Observe: All the time insert double citation marks when stating your textual content parameters.

IF-THEN statements are case-insensitive except in any other case said.

To make an IF-THEN assertion case-sensitive, you could precede your situation parameters with the phrase “EXACT.” Utilizing the instance above, Excel will take a look at for uppercase textual content with this components:

  • IF(EXACT(B2, “PASSED”), “Scored above 50”, “Didn’t rating above 50”)

Writing IF-THEN Capabilities for Numbers

It’s essential to know the essential Excel operators and their indicators earlier than writing IF-THEN statements in Excel for numbers. Right here’s a information to Excel that may show you how to learn the basic Excel formulas, operators, and more.

Observe these steps.

Step 1: Click on on the cell field the place you wish to insert the perform.

Step 2: Write the “equal” (=) signal.

Step 3: Begin writing the if assertion, together with the situations for the take a look at and the responses to be returned.

Step 4: After writing the if assertion, click on enter. Your components ought to seem like this:

  • IF(B2>=50, “Handed”, “Failed”)

Utilizing the perform above, we will write an IF-THEN assertion to examine if a scholar handed or failed a course. Right here’s an instance:

writing if then statements in excel, if then statements in excel with text

This perform units a situation to check if cell B2 accommodates an integer worth larger than or equal to 50. If this situation is met, this system will return a response of “Handed” and a “Failed” response if the situation isn’t met.

Observe: IF-THEN capabilities don’t solely work for integers. They’ll additionally work for actual numbers and adverse numbers.

Writing IF-THEN capabilities for dates

One of many distinctive types of writing IF-THEN capabilities is the date format. Many individuals might imagine they will write IF-THEN capabilities for dates like they write IF-THEN capabilities for numbers.

Sadly, that’s not so.

For the reason that Excel program can not learn the common date format of mm/dd/yyyy, you’ll should insert the “DATEVALUE” perform into your IF-THEN perform to make the Excel program know that you just’re testing a date situation.

Your perform ought to seem like this:

  • IF(B2>=DATEVALUE(“mm/dd/yyyy”), “sure,” “no”)

Right here’s a step-by-step process on learn how to use this in your spreadsheet:

Step 1: In your spreadsheet, click on on the cell the place you wish to enter the perform.

Step 2: Write the “equal” (=) signal.

Step 3: Begin writing the if perform. Your syntax ought to look one thing like this:

writing if then statements in excel, if then statements in excel with text

Step 4: Click on Enter in your keyboard, and the perform will return a standing response of both “Launched” or “Unreleased.”

writing if then statements in excel, if then statements in excel with text

The perform above checks whether or not the date in cell B2 is larger than the eleventh of April 2023. The perform will give a “Launched” or “Unreleased” response relying on whether or not the situations are met or not.

Observe: You possibly can set the IF-THEN perform to check towards the present date by inputting the TODAY() perform into your IF-THEN perform.

That is what your perform ought to seem like:

  • IF(B2>TODAY(), “Launched”, “Unreleased”)

Writing IF-THEN Capabilities for A number of Situations (Nested IF statements)

A number of IF-THEN statements or Nested if statements, as generally referred to as, assist you to add a number of if statements into one if assertion.

A Nested if assertion will seem like this:

  • IF(logical_test, [value_if_true], if(logical take a look at, [value_if_true], if(logical take a look at, [value_if_true], [value_if_false])))

In case you wished to put in writing an IF-THEN perform to assign remarks to scores of first-year college students in a specific course. Right here’s how to do that in just a few steps:

Step 1: Click on on the cell field the place you wish to insert the perform

Step 2: Write the “equal” (=) signal.

Step 3: Start writing the IF-THEN perform. Your perform ought to look one thing like this:

  • IF(B2>90, “Glorious”, IF(B2>=80, “Very Good”, IF(B2>=70, “Good”, IF(B2>= 50, “Truthful”, “Poor”))))

writing if then statements in excel, if then statements in excel with text

Step 4: Click on the Enter key to obtain a response to your perform.

The perform will return a response of “Glorious” for college kids who rating 90 or above; “Very Good” for college kids who rating between 79 and 90; “Good” for college kids who rating 69 and 80; “Truthful” for college kids who rating between 49 and 70; and “Truthful” for college kids that rating beneath 50.

Finest Practices for IF-THEN Statements in Excel

Listed below are some practices to observe to get the very best out of your IF-THEN capabilities in Excel.

All the time use parentheses.

One of the best ways to keep away from errors in your formulation in Excel is through the use of parentheses. Subsequently, if you’d like your if assertion to perform correctly, make sure you use parentheses accurately.

For instance, when writing a number of conditionals or a nested if assertion, make sure you use parentheses in the suitable locations to keep away from “#NAME?” errors.

Use vary as a substitute of cells.

While you use Vary as a substitute of Cells, you possibly can keep away from errors arising when cell information modifications or your components must be adjusted.

Write out every part as its personal components.

It might sound pointless, however writing every spreadsheet part as its personal components would make your information extra readable and simpler to grasp.

One other profit to doing that is that you will discover every little thing you want in a single place and edit it in response to your wants.

Much more, if there’s an error together with your perform, writing every part as its personal components makes it simpler so that you can debug and discover any errors in your components.

By no means hesitate to make use of different formulation.

One incredible factor about Excel is that it allows you to use a number of capabilities by inserting them into each other.

Like different formulation in Excel, you possibly can insert different capabilities into your IF-THEN perform to get the very best out of it.

Utilizing your IF-THEN perform alongside different formulation makes it simpler to make calculations, saves time (particularly while you’re working with giant information), and makes it simple to identify any errors.

Getting Began

IF-THEN statements are sure to make your life simpler. Though it could be tough to know every little thing directly, you will discover it simple with fixed apply and steady use.

So don’t hesitate to go to this information anytime you face a problematic IF-THEN assertion problem.

excel graph templates

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *