How to use nested IF statements in Excel with AND, OR, NOT

Corporate finance, Excel function tutorials

If you want to learn Excel, keep reading or Get 10 days of free unlimited access to Lynda.com. for professional help and Excel Tutorials
***************

Following up on last week’s , here’s one on how to use Excel’s IF statement when you have multiple conditions to meet.

The original question I received from a reader, which prompted this series, was about how to use the IF statement in Excel when you had two conditions that had to be met. For example, when sales fell between a minimum and maximum number. Let’s take a look at our example again. Note that I’ve added the maximum amount of $200K into cell B3:

Before we go further, if you’d like to work through the examples yourself, here’s the raw data you can copy into an Excel worksheet. First, open up a blank excel worksheet. Next, highlight the table below. Copy it, and then go back to your excel worksheet. Go to cell A1 (or another empty cell, if you want to put the data elsewhere), and then select “Edit” from the menu bar. Select “Paste Special” and then “Text” from the popup box. Click “OK”. The data should appear in your Excel worksheet just as it does above.

Bonus 12%
Minimum amount 100000
Maximum amount 200000
Sales Bonus
Salesman A 87925
Salesman B 100000
Salesman C 145000
Salesman D 200750
Salesman E 178650
Salesman F 99555
Salesman G 147000
Salesman H 213450
Salesman I 122680
Salesman J 92500

Ok, now back to the tutorial.

Now, let’s suppose sales have to be greater than or equal to $100K and less than $200K for a salesman to receive a 12% commission rather than just be greater than $100K, as in our introductory example. How would you write that in “Excel-speak”?

It turns out that you can use , which Excel calls a logical operator (just like it calls the IF function). And, as usual, unlike how most other programming languages work, the syntax required in Excel is a bit different. To use it correctly, you have to write it like the following:

=AND(first condition, second condition, …, etc.)

(In other programming languages, AND would fall in between each condition, just like how we normally talk, but not in Excel!)

Let’s go back to the concrete example. To write the condition that sales have to fall betwen $100K and $200K for the salesman to receive a 12% commission, we’d write the following in cell C6:

=IF(AND(B6>=$B$2, B6<$B$3),B6*$B$1,"No bonus")

Like this:

Translated into plain English, our IF statement now reads, “If B6 is greater than or equal to B2 and B6 is less than B3, then multiply B6 by B1. If not, then put ‘No bonus’ into the cell.” In the first case, our salesman didn’t meet the $100K requirement, so the AND function returned a false, so the IF statement put “No bonus” into the cell. By the way, in our case, we only had two conditions to meet, but if we had more, we could just keep adding them into the list of conditions in the parenthese after the AND function.

Here’s how the results look if we do this for all the salesmen in our example:

There are two other logical functions, and , which work the exact same way:

=OR(first condition, second condition, …, etc.)
=NOT(first condition, second condition, …, etc.)

For example, we could actually rewrite our IF statement above using the OR condition if we’re careful. Here’s how it would look:

=IF(OR(B6<$B$2,B6>=$B$3),”No bonus”, B6*$B$1)

Like this:

Again, in plain English, what we’re saying is, “If B6 is less than B2 or B6 is greater than or equal to B3, put ‘No bonus’ into the cell. Otherwise, multiply B6 by B1.” We’re basically testing for the opposite conditions in our OR statement as we did in our AND statement. And you’ll get the same results as above with the AND statement, as seen below:

You might be able to picture now how you could start using multiple AND, OR, and IF statements nested altogether to test all sorts of complicated conditions in Excel. But keep in mind that cells in Excel are really meant more for doing calculations than programming, which is really what you’re getting into once you start putting in complex IF statements. (I’ve read somewhere you can nest up to 7 IF statements in Excel, though I’m not sure to which version(s) of Excel that limitation applies.)

In particular, coming up with a correct list of conditions to test for, in the right order, that don’t result in a false positive test (e.g. a condition accidentally being met that you didn’t mean to) is a big concern in Excel. The other problem is usability. Even if you were able to come up with a complex IF statement that did what you wanted it to, imagine you put the spreadsheet away and revisited it again in a year’s time when you had to update annual sales figures and calculate bonuses again. Here’s an example of a complicated nested IF statement with no other logical operators:

=IF(A1<20, A1*1, IF(A1<50, A1*2, IF(A1<100, A1*3, A1*4)))

Think about it: would you really want to spend an extra hour or two decoding that and figure out what you needed to change or update a year later? Similarly, if you had to use a worksheet that someone else wrote that contained a bunch of complicated IF statements, wouldn’t you go nuts? I would!

The statement above is actually even in “reverse” order of what you might normally expect. (In case you’re wondering, I pulled this example from the bottom of a webpage here. It provides the original reader’s question, though why he or she wanted to calculate that, I’m not sure!)

I’ll follow up this post with one on conditional formatting, and I’ve had a request to explain VLOOKUP, HLOOKUP, and pivot tables. Feel free to suggest other tutorials you’d like to see, and I’ll try my best to get to them!

***************************************************

Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss


***************************************************

777 Feedbacks on "How to use nested IF statements in Excel with AND, OR, NOT"

ramana

if a=m or a=c or a=t then 25485.
what is the correct formula
thanks



KYE

hello, this has helped me lots but im still struggling, wondered if you could help.

i have an IF formula at work that calculates if your completion date is within the time needed for a pass, but i want it to be overiden by another cell (interim response) so that if its sent to the interim before completion but not actually completed on time it still shows as a pass

this is the formula i have to work with,

=IF(ISBLANK(A3),””,IF(OR(F3>B3,AND(ISBLANK(F3),TODAY()>B3)),”fail”,”pass”))



Jayesh Jamtani

It really help me lot.Thank you…………..



Jayesh Jamtani

pls i need help from anyone… i have numbers like

33 55 66 54 …
55 44 33 34 …
55 55 76 33 …
55 65 55 66 …
33 33 44 23 …
55 22 22 33 …
what formula ll i use to choose yes for any row that has any of its 3 columns above 50 and others no
i ve tried this but it didnt work

if solve msg me on wht’s up no 9898509009 (India)



Mkile

how do I get the IF statement to return more thn 1 answer. eg

Bob 20
John 0
Ben 0
Kevin 10

returns the ones greater than zero in one cell [Bob,Kevin]



Martin

Can someone tell me what is wrong here? In P8 and S8 i have 00:00 or xx:xx as in 01:24 or 05:30 ect ect..

I need it to look in P8 and S8 if there is anything is should make it 00:30 and if they are 00:00 it should be 00:00

=OR(IF(P8>0;”00:30″;”00:00″);IF(S8>0;”00:30″;”00:00″))



Kattrina

Please help!

I need a formula that looks at a cell.EG A1

If A1 says “card” or “mail order” then it needs to take the value off another cell (B1) and times it by 0.03. If however A1 says “defaults” it needs to take the value in B1 and times it by 0.015.

This is what i have so far: it doesn’t work :(

=IF(A11=”Card”,b1*0.03,IF(A1=”Mail Order,B1*0.03,IF(A1=””Defaults”,B1*0.015))



Abumazin Al Siddiqui

Hello
can you please help me in this formula
=IF(OR(A14=”A”,A14=”B”),”Approved”,”Approved As Noted”)+IF(OR(A14=”B”,A14=”D”),”Approved As Noted”,”Rejected”)

thanks



Bala

KATTRINA

Use this formula

=IF(OR(A1=”card”,A1=”mail order”),B1*0.03,IF(OR(A1=”defaults”),B1*0.15,B1))



MILAN

Hi. Can you please help me with following formula? I have two formulas and I would like to combine them into one. This is what I am trying to do:
AGE DAYS RESULT
<10 10365020 >7300 RED =(TODAY()-3650),”GREEN”,”ORANGE”))
And this is the second formula in D6 to D15 column: =IF(B6=””,””,IF(B6>=(TODAY()-7300),””,”RED”))
There might be a different way how to solve it as well. Any help will be appreciated. I have one more question: is it possible to assign a color to the text within the formula?



MILAN

OK, that does not look correct, I hope this one will show better:
Hi. Can you please help me with following formula?
I have two formulas and I would like to combine them into one so I can have it all in one column.
I am trying to sort machinery into tree grups (Green,Orange,Red) by age.

AGE (Years) AGE (Days) RESULT
<10 10365020 >7300 RED

MACHINE 01 01/01/2014 GREEN
MACHINE 02 01/04/1998 ORANGE
MACHINE 03 05/01/2003 ORANGE
MACHINE 04 12/12/2005 GREEN
MACHINE 05 12/12/2004 ORANGE
MACHINE 06 22/08/2005 ORANGE
MACHINE 07 14/05/2004 ORANGE
MACHINE 08 05/12/1979 ORANGE RED
MACHINE 09 12/12/1994 ORANGE RED
MACHINE 10 04/04/1990 ORANGE RED

This is formula in the C6 to C15 column: =IF(B6=””,””,IF(B6>=(TODAY()-3650),”GREEN”,”ORANGE”))
And this is the second formula in D6 to D15 column: =IF(B6=””,””,IF(B6>=(TODAY()-7300),””,”RED”))
There might be a different way how to solve it as well. Any help will be appreciated. I have one more question: is it possible to assign a color to the text within the formula?



So

Hola! necesito una formula que traiga de la hoja 2 lo siguiente:
en la hoja 2 tengo 3 columnas personas fechas y codigos.
si la persona coincide con la persona que esta en la hoja 1 y se repite y tiene más de un codigo igual en una misma fila que traiga la fecha más antigua.

col 1 col2 col3
pepe 23/05/2015 S1
pepe 01/03/2015 S1
pepe 30/08/2015 S1

en el ejemplo debería traer la fecha 01/03/2015



Ajay`

=IF(D3=0,D3=5,D3=5,D3<10),5} it ignores this logic everytime, not sure why. Tried using different excel sheet, but still the same error.



Nicola

I’m relatively new to nested statements and this should be easy but I cannot make it work. I need a formula for the following :

If cell 1 or cell 2 is equal to or more than 5 and cell 3 is also equal to or more than 5 then return Y (blank if not). Cells 1 -3 are the result of a formula.

Any help would be most appreciated.

Thank you.



Dhanya

My requirement is our requirement is if C2=”fL” or E2=”SC raising” then look for value in J2. If that is <=3 "met" else "not met". Can someone help me with the above

What I have tried is

IF(OR(C2="SWGFL",E2="SC Raising"),(if(J2<=3),"Met","Not Met"),"NA")

Though the above formula is not giving any error, it is not giving correct result.



Dhanya

Sorry the formula I tried is
IF(OR(C2=”fL”,E2=”SC Raising”),(if(J2<=3),"Met","Not Met"),"NA")



Jim W

I have 2 spreadsheets. One is a listing of products with prices for each level of volume sales. The other is a summary where I want to be able to have someone put in the product and the sales volume, and have the sheet automatically put in the sales price. I can’t figure out how to do this They look like the following:

Spreadsheet 1:
1-10 10-100 >100
Prod 1 $10 $8 $6
Prod 2 $15 $12 $ 10
Prod 3 $20 $16 $14

Spreadsheet 2:

Prod Name Quantity Sales Price
Prod 1 15 ?
Prod 2 62 ?
Prod 3 42 ?

How do I get Spreadsheet 2 to automatically fill in the Sales Price from Spreadsheet 1 based on the Product Name and Quantity provided?

Thanks



karan patil

dear sir,

total units
1–20
21–30
31–80
81 above

pals. provide the if function this is case
automatically units calculate by slab wise example 100 units
1) 1–20 ===20
2) 21–30===10
3)31–80===50
4)81 above ==20
sir user the units calculate by this slab wise



IQBAL

hello
can anybody show the below conditional formula
IF EMP RESIGNS =2 AND =5 AND = 10 YEARS ELIGIBLE FOR FULL AMOUNT TO RECEIVE
IF EMP TERMINATED ALL CASES ELIGIBLE FOR FULL

RESIGN COLOUM = A
NO OF YEARS COLOUM = B
AMOUNT COLOUM = C



IQBAL

IF EMP RESIGNS =2 AND =5 AND = 10 YEARS ELIGIBLE FOR FULL AMOUNT TO RECEIVE
IF EMP TERMINATED ALL CASES ELIGIBLE FOR FULL



cay ghep rang

I think this is among the so much significant info for
me. And i am happy rewding your article. However want to remark
on few normal issues, The weeb site tasfe is wonderful, the articles
is really excellent : D. Good process, cheers



michael

hello. I am trying to build a sheet that identifies a particular sales rep based on state and number of employees.

So it would be something like “If this state or this state or this state” AND less than this number of employees, then this rep

but if more than this number than this rep

And I would need to do that for several different combinations

Is this even possible in Excel? Thank you



nha khoa uy tin

I blog quite often and I really appreciate your information. The article has really pesaked my interest.
I am going too take a note of you blog and keep checking for new information about
once per week. I opted in for your RSS feed as well.



Sean

This answered a question I had perfectly. Thanks for the guide.



Divya

Hi Nicola,

Sorry for the late reply.

Below is the formula for your question (If cell 1 or cell 2 is equal to or more than 5 and cell 3 is also equal to or more than 5 then return Y (blank if not). Cells 1 -3 are the result of a formula.)

I have assigned the Value in cell A1 – 7, A2 – 5, A3 – 8

In B1 =IF(AND(OR(A1>=5,A2>=5),A3>=5),”Y”,” “)



Nanda Dhami

I like it. I want to join the website now.what can i do?



pre pay mobile pop3 cheap

Spot on with this write-up, I absolutely believe that this
amazing site needs far more attention. I’ll probably be returning to see
more, thanks for the information!



Comments

Please Leave a Comment!





Please note: Comments may be moderated. It may take a while for them to show on the page.