Move C1 and C4 Again to Flip the Confirmation

In this Commodity

IF Function Overview
IF role Syntax and inputs:

What is the IF Function?
- How to Utilize the IF Role
- What IF tin return
- Using IF with AND
- Using IF with OR
- Using IF with XOR
- Using IF with Non
- Nested IF Statements
- Simplifying Complex IF Statements with Helper Columns
- Using Group to Hide Helper Columns
- The IFS Part
- Using IF with Conditional Formatting
- Using IF in Array Formulas
- IF in Google Sheets
- Boosted Notes
- VBA IF Statements

This tutorial demonstrates how to utilize the Excel IF Function in Excel to create If And so Statements.

IF Main Function

IF Part Overview

The IF Office Checks whether a condition is met. If Truthful practise one matter, if Simulated do another.

To apply the IF Excel Worksheet Part, select a cell and type:

if formula syntax

(Discover how the formula inputs appear)

IF role Syntax and inputs:

=IF(logical_test,value_if_true,value_if_false)

logical_test – Logical expression. Instance: A1 > 4.

value_if_true – Value or Calculation to perform if logical expression is True.

value_if_false – Value or Calculation to perform if logical expression is FALSE.

What is the IF Function?

IF is a "provisional" function. This means you define a logical test, and it volition return one value if that examination evaluates as true, and a unlike value if it is false

How to Utilise the IF Office

Here'southward a very bones instance so you lot tin encounter what I mean. Attempt typing the following into Excel:

=IF( 2 + 2 = four,"It's true", "It'southward false!")

Since two + two does in fact equal 4, Excel volition render "It's true!". If we used this:

=IF( two + two = v,"It's true", "It's simulated!")

Now Excel will render "It's fake!", considering 2 + ii does not equal 5.

Here's how you might use IF in a spreadsheet.

=IF(C4-D4>0,C4-D4,0)

How to use IF

You run a sports bar and you lot prepare private tab limits for different customers. You've set upward this spreadsheet to check if each client is over their limit, in which case you'll cut them off until they pay their tab.

You check if C4-D4 (their current tab amount minus their limit), is greater than 0. This is your logical test. If this is true, IF returns "Yes" – you should cut them off. If this is imitation, IF returns "No" – you let them proceed drinking.

What IF tin render

Above nosotros returned a text string, "Yeah" or "No". Simply yous can too return numbers, or even other formulas.

Let's say some of your customers are running upward big tabs. To discourage this, you lot're going to start charging involvement on customers who become over their limit.

Y'all can use IF for that:

=IF(C4>D4,C4*0.03,0)

Return Formula

If the tab is higher than the limit, return the tab multiplied by 0.03, which returns 3% of the tab. Otherwise, return 0: they aren't over their tab, so you won't charge interest.

Using IF with AND

Yous can combine IF with Excel'due south AND function <<link>>. You lot utilize this in the logical test, which enables y'all to specify two or more conditions to test. Excel will only return TRUE if ALL of the tests are truthful.

Then, you lot implemented your involvement rate. Only some of your regulars are complaining. They've e'er paid their tabs in the by, why are you cracking down on them at present? You lot come up with a solution: you lot won't accuse interest to certain trusted customers.

You make a new column to your spreadsheet to identify trusted customers, and update your IF statement with an AND office:

=IF(AND(C4>D4, F4="No"),C4*0.03,0)

IF AND

Let'south look at the AND part separately:

AND(C4>D4, F4="No")

Note the two conditions:

C4>D4: checking if they're over their tab limit, equally before
F4="No": this is the new bit, checking if they are not a trusted customer

So now nosotros merely return the involvement rate if the customer is over their tab, AND we have "No" in the trusted customer column. Your regulars are happy again.

Read more on the principal folio for the Excel AND Role <<link>>.

Using IF with OR

OR is another of Excel's logical functions. Like AND, information technology enables y'all to define more one condition. Only dissimilar AND, it will return Truthful if ANY of the tests you lot define are true.

Maybe customers existence over their tab is not the simply reason you'd cut them off. Mayhap yous requite some people a temporary ban for other reasons, gambling on the premises maybe.

So you add a new column to identify banned customers, and update your "Cutting off?" column with an OR test:

=IF(OR(C4>D4,E4="Aye"),"Yeah","No")

IF OR

Looking just at the OR function:

OR(C4>D4,E4="Yeah")

At that place are two weather:

C4>D4: checking if they're over their tab limit
F4="Yes": the new part, checking if they are currently banned

This will evaluate to true if they are over their tab, or if at that place is a "Yeah" in column Due east. As yous tin see, Harry is cut off now, even though he's not over his tab limit.

Read more than on the main page for the Excel OR Office <<link>>.

Using IF with XOR

XOR is another logical function, which returns the "Sectional Or". This is a little less intuitive than the previous ones we've discussed.

In simple cases, you define two conditions, and XOR will render:

Truthful if either argument is true (same equally a normal OR)
FALSE if both arguments are truthful
Simulated if both arguments are false

An example might make this clearer. Imagine y'all want to beginning giving monthly bonuses to your staff :

If they sell over $800 in food, or over $800 in drinks, you'll requite them a half bonus
If they sell over $800 in both, you'll give them a total bonus
If they sell under $800 in both, they don't become whatsoever bonus.

Y'all already know how to work out if they become the full bonus. You'd only apply IF with AND, as described earlier.

=IF(AND(C4>800,D4>800),"Yeah","No")

IF XOR A

But how would you work out who gets the half bonus? That'south where XOR comes in:

=IF(XOR(C4>=800,D4>=800),"Yes","No")

IF XOR B

As you can run across, Woody's potable sales were over $800, but not nutrient sales. So he gets the half bonus. The reverse is true for Charabanc. Diane and Carla sold more than $800 for both, then they don't get a half bonus (both arguments are Truthful), and Rebecca made under the threshold for both (both arguments False), and then the formula over again returns "No".

Read more on the main folio for the Excel XOR Function <<link>>.

Using IF with Non

NOT is yet another of Excel'southward logical functions, which is very commonly used with IF.

Non reverses the outcome of a logical test. In other words, it checks whether a condition has not been met.

You can utilise it with IF similar this:

=IF(AND(C3>=1985,NOT(D3="Steven Spielberg")),"Scout", "Don't Watch")

IF NOT

Here we take a table with information on some 1980s movies. We want to place movies released on or later on 1985, that were not directed by Steven Spielberg.

Because Non is nested within an AND Function, Excel will evaluate that offset. It will then utilize the result equally part of the AND.

Read more on the main page on the Excel Not Function <<link>>.

Nested IF Statements

You tin can also return an IF argument within your IF argument. This enables you to make more circuitous calculations.

Let'south become back to our customers table. Imagine y'all want to allocate customers based on their debt level to y'all:

$0: None
Up to $500: Low
$500 to $1000: Medium
Over $1000: High

Yous tin can do this by "nesting" IF statements:

=IF(C4=0,"None",IF(C4<=500,"Low",IF(C4<=1000,"Medium",IF(C4>one thousand,"High"))))

Nested IF simple

It'south easier to empathize if you put the IF statements on separate lines (ALT + ENTER on Windows, CTRL + COMMAND + ENTER on Macs):

= IF(C4=0,"None", IF(C4<=500,"Low", IF(C4<=1000,"Medium", IF(C4>m,"High", "Unknown"))))

IF C4 is 0, we return "None". Otherwise, we move to the next IF statement. IF C4 is equal to or less than 500, nosotros return "Low". Otherwise, we motility on to the adjacent IF argument… and then on.

Simplifying Complex IF Statements with Helper Columns

If yous accept multiple nested IF statements, and yous're throwing in logic functions too, your formulas can become very difficult to read, test, and update.

This is especially important to keep in mind if other people will be using the spreadsheet. What makes sense in your head, might not be and so obvious to others.

Helper columns are a bully fashion around this issue.

You're an analyst in the finance department of a large corporation. You've been asked to create a spreadsheet that checks whether each employee is eligible for the company alimony.

Hither'due south the criteria:

Complex IF Table

And then if you're under the historic period of 55, you need to have 30 years' service under your chugalug to be eligible. If you're aged 55 to 59, you need 15 years' service. And so on, upwardly to historic period 65, where y'all're eligible no matter how long you've worked there.

You could use a unmarried, complex IF statement to solve this trouble:

=IF(OR(F4>=65,AND(F4>=62,G4>=5),AND(F4>=60,G4>=10),AND(F4>=55,G4>=15),G4>xxx),"Eligible","Non Eligible")

Complex IF

Whew! Kinda hard to become your head around that, isn't it?

A improve approach might be to utilise helper columns. We accept five logical tests here, corresponding to each row in the criteria table. This is easier to see if we add together line breaks to the formula, as we discussed earlier:

=IF( OR( F4>=65, AND(F4>=62,G4>=5), AND(F4>=60,G4>=10), AND(F4>=55,G4>=15), G4>30 ),"Eligible","Not Eligible")

And then, we can split up these 5 tests into separate columns, then simply check whether whatsoever one of them is true:

Complex IF Helper

Each cavalcade in the tabular array from E to I holds each of our criteria separately. Then in J4 we accept the following formula:

=IF(COUNTIF(E4:I4,TRUE),"Eligible","Not Eligible")

Here we accept an IF statement, and the logical test uses COUNTIF <<link>> to count the number of cells within E4:I4 that contain TRUE.

If COUNTIF doesn't find a TRUE value, information technology will return 0, which IF interprets as FALSE, so the IF returns "Not Eligible".

If COUNTIF does find any TRUE values, it will render the number of them. IF interprets any number other than 0 as Truthful, and so it returns "Eligible".

Splitting out the logical tests in this way makes the formula easier to read, and if something's going incorrect with information technology, information technology'due south much easier to spot where the error is.

Using Group to Hide Helper Columns

Helper columns brand the formula easier to manage, but once you've got them in place and you know they are working correctly, they often just take up infinite on your spreadsheet without adding any useful information.

Yous could hibernate the columns, merely this tin lead to problems because hidden columns are hard to notice, unless y'all expect closely at the column headers.

A ameliorate option is grouping.

Select the columns you desire to group, in our example Due east:I. Then press ALT + SHIFT + Right Arrow on Windows, or COMMAND + SHIFT + K on Mac. You lot can as well go to the "Information" tab on the ribbon and select "Group" from the "Outline" section.

Yous'll see the group displayed above the column headers, like this:

Complex IF Grouping

Then simply press the "-" button to hide the columns:

Complex IF Grouping

The IFS Role

Nested IF statements are very useful when yous demand to perform more complex logical comparisons, and you need to do it in one cell. However, they can get complicated as they get longer, and they can be difficult to read and update on your screen.

From Excel 2019 and Excel 365, Microsoft introduced another function, IFS, to help make this a scrap easier to manage. The nested IF example above could be achieved with IFS similar this:

=IFS( C4=0,"None", C4<=500,"Low", C4<=grand,"Medium", C4>1000,"High", Truthful, "Unknown", )

You tin read all virtually information technology on the master page for the Excel IFS Function <<link>>.

Using IF with Conditional Formatting

Excel'south conditional formatting feature enables you lot to format a cell in different means depending on its contents. Since the IF returns different values based on our logical exam, we might want to apply conditional formatting to make these dissimilar values easier to see.

So let'south go back to our staff bonus table from earlier.

IF Conditional format Table

Nosotros're returning "Yes" or "No" depending on what bonus we want to requite. This tells u.s.a. what we need to know, but the information doesn't bound out at united states of america. Allow's try to fix that.

Here'southward how you'd exercise it:

Select the prison cell range containing your IF statements. In our example that's E4:F8.
Click "Conditional Formatting" on the "Styles" department of the "Abode" tab on the ribbon.
Click "Highlight Cells Rules" and and so "Equal to".
Type "Yes" (or whatever return value you need) into the first box, and so choose the formatting y'all want from the second box. (I'll choose green for this).
Repeat for all your return values (I'll also set "No" values to reddish)

Conditional-format-NO

Conditional format Both

Here'south the result:

Conditional format Result

Using IF in Array Formulas

An assortment is a range of values, and in Excel arrays are represented equally comma separated values enclosed in braces, such equally:

{1,2,three,4,v}

The beauty of arrays, is that they enable you to perform a adding on each value in the range, and and so return the result. For example, the SUMPRODUCT Function takes 2 arrays, multiplies them together, and sums the results.

So this formula:

=SUMPRODUCT({1,2,3},{four,5,6})

…returns 32. Why? Let's work it through:

1 * 4 = 4 2 * 5 = x 3 * 6 = 18  iv + ten + 18 = 32

We can bring an IF statement into this picture, so that each of these multiplications merely happens if a logical test returns true.

For example, take this information:

https://www.automateexcel.com/excel/wp-content/uploads/2020/07/SUMPRODUCT-Case-Range.png?ezimgfmt=ng:webp/ngcb20

If you wanted to calculate the total committee for each sales manager, y'all'd utilise the following:

=SUMPRODUCT(IF($C$2:$C$ten=$G2,$D$2:$D$10*$E$two:$E$10))

Note: In Excel 2019 and earlier, y'all accept to printing CTRL + SHIFT + ENTER to turn this into an array formula.

We'd end up with something like this:

https://www.automateexcel.com/excel/wp-content/uploads/2020/07/SUMPRODUCTS-IF-Results-Table.png?ezimgfmt=rs:198×95/rscb20/ng:webp/ngcb20

Breaking this down, the "Manager" column is column C, and in this instance, Olivia's proper name is in G2.

So the logical exam is:

$C$ii:$C$10=$G2

In English, if the name in column C is equal to what's in G2 ("Olivia"), Do multiply the values in columns D and E for that row. Otherwise, don't multiply them. Then, sum all the results.

Y'all tin can learn more virtually this formula on the principal page for the SUMPRODUCT IF Formula <<llink>>.

IF in Google Sheets

The IF Function works exactly the same in Google Sheets as in Excel:

IF Google Function

Additional Notes

Use the IF Function to test if a condition is TRUE. If the condition is TRUE and then do one affair. If it'southward Simulated then do another. The condition must exist a logical expression (ex: a1 >5), a reference to a cell containing TRUE or FALSE, or an array containing all logical values.

The IF Function can just test 1 condition at a fourth dimension. However, you can "nest" other logical functions within the IF condition to test multiple weather condition at one time:

=if(AND(a1>0,a2>0),Truthful,Imitation) =if(OR(a1>0,a2>0),True,Imitation) =if(XOR(a1>0,a2>0),TRUE,FALSE)
OR Functions test if one or more than conditions are met.
AND Functions test if all atmospheric condition are met.
XOR Functions test if ane and only 1 condition are met.

You lot can as well "nest" an IF Function within an IF Function:

=if(a1<0,if(a2<0,"Both","only 1"),"but one")

Now for some concrete examples of how the IF office works in practice:

i. Start a New Piece of work book.

two. In Cell A1 enter the value x (and press Enter)

Enter value 10

three. Then in Cell B1 enter the following formula:

=IF(A1 > 5,"BIGGER THAN 5","LESS THAN 5")

4. The screen should look like this now:

Bigger

five. If you've entered the formula correctly, you will come across the message "Bigger than 5" appear in prison cell B1.

half dozen. The formula you've entered in Cell B1 performs the test "A1> 5" i.e it checks if the value in Jail cell A1 is greater than 5. Currently the value in Cell A1 is ten – then the condition is TRUE and the message "BIGGER THAN five" appears

7. If we now change the value in jail cell A1 to 2:

Smaller

Thtn the message in jail cell B2 is now "LESS THAN five" equally the condition is FALSE.

8. You tin proceed changing the value in cell A1 and the message in cell B2 will adjust accordingly.

9. Of course in that location are situations where the status could give mischievous results:

• What happens if we enter the Value 5 in Cell A1 ?

• What near if nosotros leave Cell A1 blank ?

• What about if we put some text in Cell A1 eastward.g the phrase Domestic dog

VBA IF Statements

You can also use If Statements in VBA. Click the link to learn more than, only here is a simple example:

Sub Test_IF ()  If Range("a1").Value < 0 so   Range("b1").Value = "Negative" End If  End If

This code will exam if a cell value is negative. If and then, it volition write "negative" in the next cell.

silversteinanstandn.blogspot.com

Source: https://www.automateexcel.com/functions/if-formula-excel/