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

Corporate finance, Excel function 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


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

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

How to use the IF function in Excel | Experiments in Finance

[...] The first part (here) will go into the basic IF statement, and subsequent parts, such as how to use nested IF functions with other logical operators, will describe how to use it in slightly more complicated scenarios or with additional features. For example, the reader’s question had to do with using an IF statement given two conditions, e.g. if a number falls between two other numbers. I address those in the separate post in the link above. [...]



Sarah

This was extremely helpful. The formula was explained very well. I am totally new to writing formulas in XL and I could easily understand the instructions. Thank you sooooo much!!! This just saved me a ton of time!



Ricemutt

Glad to be of help!



Ray

This is Brilliant; I have been trying to combine an “IF” with an “AND” for months. This is the first example I could understand and use.



RIZZAY

I have been trying to figure out how to set two conditions for the “true” portion of my IF statements for a long time. I finally found the answer on this site. Thank you!



Maria

Thanks, thanks, thanks !!! Time and time again I come back to this formula (I should know it by heart as much as I use it :)) because 90% of the analysis I do are multiple conditions!! Thanks a million again!!!!

-M



Cindy

Much easier to explain what I’m trying to do.

I download a trial balance from my general ledger package and put it in one tab of an excel file. On the other tabs is different sections of a tax return, based upon the account number that is certain I would like to pull the amount that coincides with that account. Because the first of the year all accounts may not be used and the placement changes throughout the year I would like to use an if statement. If (account number is B1 = the account number somewhere in the trial balance tab) put in the amount of column C1, otherwise 0.

Is that possible? Thank you, Cindy



Ricemutt

Hi Cindy – It sounds like what you probably need is Excel’s VLOOKUP function. Unfortunately, I haven’t gotten around to writing a how-to post on it yet :(



JOHN LODGE

Hi, I am at a basic (very basic) level, having taken a two day a week course over 24 weeks, and I can ( I think ) just about understand how this works. I have printed it and I shall be taking it into class tomorrow. If we get the IF,AND,OR functions, I shall be prepared.
Thanks, from the U.K.



Dickson

I was trying to find a alternative solution to write more the 7 IF statement for a perticular cell, I have an urgent work to use excel to create a timetable system. Without this option i couldn’t proceed



Chris B

Its obvious it is unfair that the salesmen reaching over £200k didn’t hit there bonus. Is tehre a Max out function as in they achieved over £200k there entitlement is 12% of £200k?



Mansha

Its is a great teaching site. i have learned many things from this site i am very thankful to all the hardworkers and makers who work really hard for the biggners and learners
thank You
Bye
Mansha Yousaf



James

Thanks, very helpful and well written



Asaed Azawy

Thank you for the huge support you do giving ot the interisters, consider me one of your students as an excel spree
Yours Truly
Asaed Azway



Salman Aslam

I WANT SOME USE FUL THING FOR THIS BUT I WANT MORE IF FORMUALS AND ALSO SOME SALARY FORMULAS. THANKS TAKE CARE



Queen Hange

so difficult this stuff ha!



Umesh Malhotra

If have two brokerage options for buy option stock, i.e., the brokerage will be calcualted as 1% of the total premium paid, i.e., for $7000, brokerage will be $70, but if the premium paid is $4000 then the brokerage will be charge on minimum brokerage creiteria that is $50.

In this case, how can I use nested If.

thanks



d. latona

thanks, have you done an explanation of the Vlookup function?



TINA

This site is intuitive and written in plain English. Thanks for doing a better job than Microsoft can at explaining how to use their software!



J Wallond

Fantastic! This is something that I have wanted to be able to do for a long time. I have just used it for some conditional formating. Works a treat!

Merry Christmas to you



Raj

Nice work. Was pointed to your site in a web search for using or with if. Thanks. Very clear pointers.



Rich

Nicely done Ricemutt, really easy to follow and helped with a complexed worksheet I’m developing.

For my own purposes it might be nice to include an example of using IF and OR in the same formula.

Thanks for all your hard work – nice to see people have been using this valuable resource for over a year now :)



harmain

hello sir it great to learn from you it getting excel easy i am glad to say u have a brillant commnad on excel so sir please suggest some thing on macros or language on c so sir when its on u only



Lynn

Need help! What is wrong with this IF statement?

=IF(A25>30000,”25%”,
IF(A25>40000,”33.3%”,
IF(A25>60000,”50%”,
IF(A25>80000,”66.6%”,”no payment”))))

Only the “25%” and “no payment” appears even though there are amounts greater than 40000?

Any comments are appreciated!

Thanks
Lynn



Ricemutt

You’ve nearly got it, but you’ve got the order mixed up, I think. Right now, the logic says if something is greater than 30K, stop. So even if you enter 90K in cell A25, Excel goes through the IF statement, tests the first condition (does it exceed 30000?), finds it’s true, so it shows 25%. This means that if you type in anything greater than 30000, it will automatically default to 25%. If you type in something greater than 80000, you will automatically get “no payment”.

You’re nearly there. All you have to do is reverse the order of the logic, like so:

=IF(A25>80000,66.6%,IF(A25>60000,50%,IF(A25>40000,33.3%,IF(A25>30000,25%,”no payment”))))

I think this is probably what you’re looking for, but let me know if it’s not.



manoj

This is a nice article thanks for the author who has given such a clear picture of using these functions…



Lynn

Thanks for your help, Ricemutt! :)



David

Hi,

I have two cells, one with a variance and the other with a variance %. I am trying to come up with a flag for these two cells. Can someone tell me why this isn’t working?
IF(AND(IF(OR(D5>100000,D50.2,E5



David

Sorry,

this is the formula that doesn’t work:
IF(AND(IF(OR(D5=100000),IF(OR(E5=.2),911,”"))))



David

IF(AND(IF(OR(D5=100000),IF(OR(E5=.2),911,”"))))



amar

I am trying to create a formula with 4 variables.

Bascially I have a cost price table where I have different prices based on quantity (x) then split by Adults, Kids and Toddlers (y).

I would like to be able to enter the quantity, then specifiy whether it is an Adult or kids or toddlers and for the formula to give me the cost price.

at the moment I am using “IF” and then “Vlookup” which works fine with 2 (y) variables but I can’t get it to lookup the third (Toddlers).



Thabo Pitso

Could you assist with using if statement for an issue opened and closed on different times and dates. E.g Opened time 13:30:12, closed time 18:32:00; date 12/01/2007, closed date 07/12/2007.



Barney Five

I’m trying to use one condition to cause two operations, such as add to two separate cells to 1] make a counter, and 2] add figures as they are calculated. I know how to do it in BASIC, but like you said, Excel is different. Also, can you loop a function, like the GOTO in BASIC?



SALAMA

i’m sorry to tell you that the function beloow didn’t work ..
=IF(A25>80000,66.6%,IF(A25>60000,50%,IF(A25>40000,33.3%,IF(A25>30000,25%,”no payment”))))

to design some thing i need to to the check for some x value suck taht :

if x x > 10 ,”long col”
if x>23, “change dimension”

how could i write this as an if statement in excel ..



Alisa

Hi! How can I nest an if(iserror) statment within an if(or) statement?



Bill Alquist

You have produced a very helpful site. Thank you. Here is my question: I am trying to use Data Validation feature in Excel. This formula =IF((AND(D3>=1,D3<=4)),3,(IF((OR(D3=”N/C”,D3=0)),2,1))) works in a normal spreadsheet but in Data Validation I have a error meesage pop up if it doesn’t meet the criteria defined above. What I need to to is instead of returning 3, 2, or 1 as shown above I need to just return the Validation error message if rules are met above.



Lance

Is there a way to create a function within a nested IF function? EX: If the data in d5 =”FT” and (e5>4, return e5*15). There are about four conditions that I am trying to meet, but cannot get the formula right. Any advise would be greatful. Thanks!



Kenny

Thank you for this terrific example of using nested if statements in excel!

I wasn’t aware that you could use more than one formula per cell. I was trying to create a running tally in excel when certain conditions were met and was unable to do so until I read your Post.



Audrey Muleya

plz help me with this assignment on how to find the balance for scfool fees paid and those how did not pay
rply



Musharraf Amir

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

Nicely done, really easy to follow and helped with a complexed worksheet I’m developing.

For my own purposes it might be nice to include an example of using nested IF formula.

Thanks for all your hard work



Julie

Great have used this for equating exam levels
3A = 3
3B = 2
3C = 1
4A = 4 etc

Is there a limit to the number of IF statements, as I cannot get past 8.

=IF(B10=”3a”,3,IF(B10=”3b”,2,
IF(B10=”3c”,1,IF(B10=”4a”,6,
IF(B10=”4b”,5,IF(B10=”4c”,4,
IF(B10=”5a”,9,IF(B10=”5b”,8))))))))
I need to great levels from 1A through to 7A



Shobhit

Hi there..a wonderful article. I have a problem. If say A1 is blank then B1 should also be blank else the vlaue in A1 should come in C1. also the formula is being written in c1. Haven’t found a way to do this. PLz help.



Shahin Hossain

Can anyone tell me how to write a statement that determines if a cell contains a certain word within a sentence and based on a criteria from another cell, input either “Y” or “N” in a different cell? For example, input “Y” in cell C1 if cell A1 = “AH” and cell B1 contains the word “Double” within a sentence such as “This is double the money”.

Any help would be greatly appreciated.

Thanks



Aaron

HI i have a question on the “Nest no more than seven functions You can enter, or nest, no more than seven levels of functions within a function.”

I need to use alot of “IF” then i have this error “The formula you typed contains an error”

I use this for Making Range
>=85 A+
85 to 84 A
75 to 76 A-
and so on….
Pls help



Aaron

This is my formula:

=IF(A1>=85,”A+”,IF(A1>84,”A”,IF(A1>79,”A-”,IF(A1>74,”B+”,IF(A1>69,”B”,IF(A1>64,”B-”,IF(A1>59,”C+”,IF(A1>54,”C”,IF(A1>49,”D+”,”F”))))))))

Pls help thank…



Ricemutt

@Aaron: Unfortunately, I think you’re seeing the limitations of Excel. It simply won’t allow more than 7 IF statements in a formula. You’ll have to come up with a different way to iterate your…grades (?) like filtering it twice and having a first set of IF statements section off As from Bs from Cs from Ds, etc. and then doing it again to section off As into A+’s, A’s, A-’s, etc. Ugly, but would work. You may also be able to lay out your data differently to use IF statements more efficiently. Hard to tell from just the comment you left though.



Ashi

its gre8…….
it really help me………



Aaron

hi Ricemutt, thank alot for reply i will try it out thank thank



Roya Kuriyama

Hi. I’m trying to use conditional formatting to highlight titles that contain (not = to) the word “Dir” or “Director.” How can I do that? Look forward to some guidance.

Thank you!



Misty

You rock–thank you!! I’ve been banging my head against the wall for 2 days trying to figure out an IF statement and, with your info, I had it done in 15 minutes.



Liz

is there anyway to combine these two formulas without having to record a macro?:
=COUNTIF(‘Sheet 1′!F5:F711,”>=01/01/2007″)-COUNTIF(‘Sheet 1′!F5:F711,”>31/12/2007″)

=COUNTBLANK(‘Trade Marks’!D5:D711)



jai

I have a query regarding macros.
I want to use macro to cut short the long process of copy-paste of large data.
Kindly, help me.
Following is the data which is in tabular form:
Unit Capacity Prod Desp Percent
Chanda 83.33 96.53 96.53 115.30
Wadi 72.50 64.36 64.68 84.78
Vizag 41.67 36.40 32.51 64.50
Binai 116.67 Nil Nil Nil

This data is for month, column A to E, similarly there’s data for year in column F to J.

This is just a small amount of data from my excel sheet which consist 14 sheets per workbook.

What I want to do is, disperse this data in rows. To explain further, the above data requires in following manner:
For Month For Year

chaCAP 83.33
chaPRD 96.53
chaDES 96.53
chaPER 115.30

wadCAP 72.50
wadPRD 64.36
wadDES 64.68
wadPER 84.78

vizCAP 41.67
vizPRD 36.40
vizDES 32.51
vizPER 64.50

binCAP 116.67
binPRD Nil
binDES Nil
binPER Nil

Now I have to do copy-paste manually for each unit, for each company, for each sheet and for each workbook.

Is there any short-cut, macro etc. to minimise the processes that I do?

It will be of great help, if you can suggest me a simple way.

Waiting for your reply.
Thankiing you.



TOMMY

Hi
I am trying to do a statement to say something like this

=IF(AND(F2>=3000,F2=3000,G2<3500),0,1)

Do you know what im tring to say ?
Any help would be well appreciated
thanks



TOMMY

Hi
the second part of my formula didnt come out …this should be on the end ! BUT IF(AND(G2>=3000,G2<3500),0,1)

Do you know what im tring to say ?
Any help would be well appreciated
thanks



lovemath2000

thankssssssssss for all



lovemath2000

i want substract that
2/10/1985 from 1/10/2008
how i can do that



lovemath2000

hi,
help pleas.
how i can use excel to substract
date 22/5/1985 from date25/7/1992



Jay

Hi,

I have a formula that I need to add cells for the formula to check before displaying a result. The formula is the following:

=IF(BZ2=0, ” “,IF(BZ2=1,” “,IF(BZ2=-9,” “,IF(BZ2>20,”XXXX”,”XXXX”))))

For example on the first condition “=IF(BZ2=0″, I want the formula to check from B2 thru BZ2, how would that be entered?

Thank you!



Jay

Forgot to mention, I thought I could add a simple B2:BZ2, but it doesn’t work.



dipak

=IF(OR(F4=H4,S4=”H”,U3>=5700),U3+150,IF(OR(F4=H4,S4=”H”,U3>=4200),U3+125,IF(OR(F4=H4,S4=”H”,U3>=3600),U3+100,IF(OR(F4=H4,S4=”A”,U3>=4700),U3+125,IF(OR(F4=H4,S4=”A”,U3>=3800),U3+100,IF(OR(F4=H4,S4=”A”,U3>=3350),U3+90,IF(OR(F4=H4,S4=”B”,U3>=4380),U3+100,IF(OR(F4=H4,S4=”B”,U3>=3390),U3+90,U3))))))))

how can do it



Brian

hi I am stuck trying to make a function that returns multiple results and I would be extremely appreciative if someone could help me out. My problem is that I have a time sheet with all the employees hours and days off. What i am trying to do is create a function that extracts both the days off and puts them in one cell with a comma in between. So far I have created a nested function with 7 IF functions in it, one for each day, but the function will only return the first day, not both. This is the function as I have it so far.

=IF((P2=”OFF”),”MON”,IF((Q2=”OFF”),”TUE”,IF((R2=”OFF”),”WED”,IF((S2=”OFF”),”THU”,IF((T2=”OFF”),”FRI”,IF((U2=”OFF”),”SAT”,IF((V2=”OFF”),”SUN”,”")))))))

Please help me, thanks



Ann

Not sure if this is the correct area to ask the question, but here goes.

=IF(AND(Complete!$AE2>=DATE(2005,10,7),Complete!$AE2<=DATE(2006,10,6)),Complete!$AK2, “”)

I have the above formula in A2 in Worksheet2. I want A3 to look at the previous formula and say Hummm… that is $AE2 so my formula should look at Complete$AE3.

When I copy The formula down the 400 rows I need, it does do it, So now, AE3 looks at Complete$AE3; however, if I insert a row in the Complete Worksheet, the $AE3 Moves down and now looks at $AE4. I thought if I used $AE$2, $AE$3, etc. it would stay static, And, it does, but when I insert a row in the Complete Worksheet, that particular row never gets acknolwedged or shows up in Worksheet2. And, there are times, I need to resort the Complete Worksheet, and I’m hope the formula, for hey look at AE2, then AE3, the AE4, etc. without resorting or every changing would resolve that issue. I’ve looked at NEXT statements but either don’t understand them or haven’t found the correct one on the web. I’ve also thought o IF, Else but can’t figure that out with my current formula.

I’d appreciate any help.



Nana

Thank you. This was so helpful!!



chitra

The tutorial was really helpful and it taught me the IF formula in a very easy manner.

However I wanted to learn how to link data in one particular sheet with another sheet in excel. Can you give me a small tutorial on the same?



Tarun

It’s fantastic site to learn excel.



Tony Parker

I have two cells that I would like to flag when they both move in the same direction, either both positive or both negative. I’m using the following statement, but am getting an error:

=IF(OR(IF(AND(F72>0,F135>0),IF(AND(F72<0, F135<0), “Explain”,”"))))

Please help. Thank you.



ricemutt

Without knowing what error message you’re getting, my guess is that you’re looking to do the following. Try it and see if this does what you’re looking for….?

=IF(OR(AND(F72>0,F135>0),AND(F72<0, F135<0)), “Explain”,”")



Tony Parker

RiceMutt, thanks for your help. The formula worked perfectly.



Sammy

May someone help me I know I have to use a so-called IF function. What I am trying to do is this: =IF(c1<=1;”4900000″;IF(c1<=2;”3500000″;IF(c1<=3;”2660000″;IF(c1<=4;”2100000″;if(c1<=5;”56000″;if(c1<=6;”140000″;if(c1<=7;”70000″;if(c1<=8;”35000″;if(c1<=9;”21000″;if(c1<=10;”9800″;if(c1<=11;”84000″;if(c1<=12;”49000″;if(c1<=13;”29400″;if(c1<=14;”18200″;if(c1<=15;”21000″;if(c1<=16″9800″;if(c1<=17;”8400″;(“5600″)))))))))))))))))).
If one would help ne rewrite to avoid the max 8 IFs, I would realy appreciate.

Thanks



Sammy

sorry the IF function was so long with 18 if and only a few wer pasted. help me out



Brian

I am trying to create a formula that will take numbers from 1 to 16 (found by a sumation formula in column “F”)and convert them as follows:

14-16 = 10
12 and 13 = 9
10 and 11 = 8
8 and 9 = 7
6 and 7 = 6
5 = 5
4 = 4
3 = 3
2 = 2
1 = 1

Thank you for any help you can give me.



Ricardo Rodriguez

Hi Guys,
I am trying to create a database where I’m pretending to have Model # and its associated description as well as associated price. I try to use micros entering in Sheet2 all the Model #’s in column A, Description in column B and price in column C.
What I want to do is in Sheet1 create some formulas such a way that when I type the model # of the product I am looking for in columnA, it automatically presents associated Description and price in the next Columns B and C, respectively.
I already tried that using nested IFs in one cell, but the problem, as all you know, is the limited IFs funtions that you can nest in one cell, and since I have around 200 different models that I have to enter, I look at VLOOKUP but it is not working the way I wanted.
Does some one have any ideas how to resolve this problem.

Your help will be highly appreciate it.

Ricardo



AAA

I m preparing a result card.

In result card preparation I have to make formula that shows the position of students they have in class room.

Please tell me the formula which can meet my requirement,.

A
2) 256
3) 694
4) 256
5) 4563
6) 486



Isha

Thanks for your help.



Mike

Hello,

I need help combining 2 formulas into 1.
Any sugguestions to this…

=IF(AND(ISBLANK(D3),E3<2.001,(E3*C3*0.015)<29),29,E3*C3*0.015)

=IF(AND(ISBLANK(C4),E4<2.001,(E4*D4*0.015)<29),29,E4*D4*0.015)

Thx



Ravi

Sir,

We have following Incentive Calculation system, how can we count in Excel (formula)
Time req between incentive Amt
100 109.59 1600
90 99.59 1800
80 89.59 2000
70 79.59 2600
60 69.59 3000
less than 60 4000

Plesae…



Jasper

It has to be possible. Can anyone assist with placing all four of these statements in a single cell, or am I just going about it all wrong with AND.

=IF(AND(C6>=220,C6=191,C6=190,C6<=209),”MM”,”")
=IF(C6<=189,”UN”,”")



ricemutt

@Jasper: What you’re trying to do doesn’t make sense. Your AND statement is saying if C6 is greater than 220 and it’s equal to 191 and it’s equal to 190 and it’s less than 209, type in “MM”. C6 can’t be all four of those things at once. Do you mean to use an OR statement?



Jason Chan

If I used more than 50 for the Gas then I should see I did not spent more than 40 for fun , otherwise I am in trouble.

Gas B2
Fun C2



Christopher Pearsall

Great site. I am trying to learn how to use excel IF THEN Statements with AND to create a child support creation spreadsheet for child support up to 30,000 of monthly income. I don’t think it has been done yet. The pages of variables are for incomes in $50 intervals and from 1 to 6 children in the household in order to determine the total amount of child support.

I know how to say it in english but I’ve been to so many sites and this is the only one that even remotely seems to help.

If you assume that I name my cells then it would sound like this.

If CHILD=1 and INCOME>=1800 and INCOME<=1850 then SUPPORT=440.

It seems simple I suppose but I’m just not grasping it. If anyone can help, feel free to post on this board. What a great way for people to help each other! Great board!!! Great Lessons! Now all I need is to grasp it!



ricemutt

@Christopher: Try the following:

=IF(AND(CHILD=1, INCOME >=1800, INCOME <=1850),440,”NONE”)

When using IF statements, you always need an “else”. I wasn’t sure what your “else” result should be, so in the equation above, it will put “NONE” in the cell if the conditions you mentioned above aren’t all met. You can replace that with whatever figure you need.



Christopher

Ricemutt,

Thank you. You know, I’m not sure what my “else” is.

CHILD must equal 1 through 6. So, I think that is why I am confused. I have to run a test to see what CHILD is first. Then based on what CHILD is (either 1,2,3,4,5 or 6) the
income calculations must be performed.

Is there any way to say that the “else” is to go to Sheet 2, Cell A1 and perform the function there?

I’m beginning to realize this is not going to be as easy as I had hoped. My “else” is likely to be a function in a cell either on the same sheet or on the next sheet. Is that even possible?

I think once I get that figured out, then I just have to keep testing and have lots of variables to type in.

Now I can understand why no one has tried what I am attempting.

If you are able to help me with this then I might actually make it through this.

Phew… my brain has been fried tried to learn things other than addition and subtraction on Excel today….

Chris



ricemutt

@Christopher – I don’t think there’s a way to make Excel go to another sheet/cell as an “else”…at least, not without some complicated macro programming. Are the income conditions consistently between 1800 and 1850 or do they vary by # of children? If they don’t vary, you might be able to use a dropdown menu for the child part.



Christopher

The income conditions actually go from $800 to $30,000 by increments of 50 dollars and the amount of child support varies for each of those increments for 1 children all the way up to 6 children. Printed out it is about 16 pages.

I was hoping to have the spreadsheet identify the number of children by CHILD=1 through CHILD=6 and then once the CHILD value is identified to have the spreadsheet jump to whatever sheet has the numbers on it for that many children but it would have to test each 50 dollar increment. So if the income value doesn’t fall between $800 to $850 then it has to test to see if it falls between $850.01 and $900 until it reaches the right income value it falls within. Then it prints out the State’s amount of child support designated by state law in another cel. Hmmmm… maybe I’m over my head here. I’m trying to do what some have said just can’t be done…

Chris



Keith

Hi,

Like many of the above folks, I also had been struggling with the syntax of multiple conditions with the IF statement. Your explanation and example should be included with Excel’s built-in help.
The other question I have relates to on-line banking. I use Quicken, not because I really like it’s flexibility, but because of its ability to connect to my financial institutions to get transaction updates. Is there any way I can program Excel to link up with my banks and credit card companies to download the latest transactions into my own budget spreadsheets?
Thanks,



Diana

8. On the Bonuses worksheet in cells F3:F31 (Bonus column), calculate bonuses for CT (consultants) and PM (project managers). Using a nested IF calculate the following (Hint: do not use the function AND because if they employee is not a consultant (CT), they MUST be a Project Manager (PM):
a. Consultants receive $100 bonus
b. Project managers have their bonuses based on how much under budget their project is (column L of the Project Info table):
i. 40% or more under budget gets $1000
ii. From 20% to less than 40% under budget gets $500
iii. From 0% to less than 20% under budget receives $250
iv. And all projects over budget (or less than 0%) receive nothing
I need a formula for this……. please



shane

I need to write an excel function that figures the commision of a stock broker if he has a flat fee of 19.95 and any stock buyer that purchases over 100 shares is charged .05 cents for every stock over 100. I am stuck. could you please help???????



Edward

Thanks for your very helpful page. I believe I know how to use IF and other functions to evaluate several variables to determine the status of a case. Now I want that status to trigger the display of a couple of paragraphs applicable to that status. Is there away to go: IF A is true, put Text A in the box; IF B is true, put Text B in the box, and so forth?



chris

I’d like to see the following:

Investor puts in $10
If revenue is equal to or less than $10, investor receives 100% of revenue
If revenue is greater than $10 then investor gets 10 then any additional remaining sums would be split 50/50.



Aspiring Entrepreneur

Keep up the good work!



mark

thanks for this. it was really a great help!



Red

Thank you!

You solved the puzzle written in pompous authoreze. The college text I am using is useless, even though it cost $150. It is about the size and weight of a 1904 Sears, Roebuck & Co. catalog. I am thinking of using its pages for the same purpose (think out house).



Wanie

Hi,

I need help to compare numbers of part that same in two different list and it will appared the amount at ecah list that being compare.



Roy

Tryingto figure:
The commission is 6% on all sales that are at least 20% above cost.



chuck

I am trying to do a pretty simple IF. I need to update spreadsheets after a special paste. ex. if a1=”cc1″ then b1=”cc2″ if a1=”cc2″ then b1=”cc3″, and so on “cc1″ through “cc6″ Can you help me with this? Thank you Chuck



al

is there a way to set a priority with an if statement? i tried with setting parenthesis but did not work. My if statement works but checking a cell and based on that provides eithr value a or b, but i want a 3rd condition to override the entire if statement if another value is placed in a different cell. example:
starts in K11
=IF(I11=0,B11,I11-J11) this works but i want to override this so if l11 = True then the value in K11 becomes 0.



ricemutt

@Al – Excel prioritizes from left to right. Give this a try and enter it into K11:

=IF(I11=”true”,0,IF(I11=0,B11,I11-J11))



Zachary

I have a question. Here is the scenario. I am downloading and existing spreadsheet with item numbers. now i want to say in excel if the item number in column A equals ### than the corresponding price in that row would be entered onto a specified cell.

thanks for the help
-zach



Veronique

I need to do a complicated if-then series. I am under the impression that there can only be a total of 7 if-then statements for one formula. Is this correct? I need the formula to work by giving a different number if it is one of 11 ranges. Thank you!



Yvette

I would appreciate if anyone could help with a formula to calculate the percentage of a rebate earned if a customer meets both requirements of products. See below

MA product at 80% and PA product at 90% will earn a 4% rebate, then if MA product at 88% and PA product at 97% will earn a 5% so on and so forth. Any suggestions are greatly appreciated.



Ralph

This made a huge difference. If I can, I am trying to make a formula work and I’m having a headache from it.

Here it is:

=IF(AND($B$2<”5.9″, $B$9=”1.012″),$B$2-”0.1″)

Very basic but it does not give the right answer.

What I am trying to do is on Cell $F$19 I want to check IF B2 is 5.9 or less AND B9 is 1.012 THEN B2 minus 0.1

Thanks



Kallu

Hi there.Cen somebady hlep me to find one excel formula.
Example: i fix this =SUMIF(Sheet1!D:D,Sheet2!C5,Sheet1!S:S) with this formula I have sum for one year,In sheet1 i have data for one year.
How cen I get this data only for one month from sheet1 to sheet2.
Thanks.



Arnold Njagi

Hi This has very helpful.
I couldnt get it anywhere in the Excel Help
Thankyou so much



Sam

=IF(OR(AND(L11=Policy!F7,’Industry Volatility Sheet’!H11>Policy!J7),AND(‘Industry Volatility Sheet’!L11=Policy!F8,’Industry Volatility Sheet’!H11>Policy!J8),AND(‘Industry Volatility Sheet’!L11=Policy!F9,’Industry Volatility Sheet’!H11>Policy!J9)),”BREACH”,”OK”),IF(OR(AND(L11=Policy!F8,’Industry Volatility Sheet’!H11>Policy!H8,’Industry Volatility Sheet’!H11Policy!H9,’Industry Volatility Sheet’!H11<Policy!I9))),”Alert 1″,”OK”)



Carol

i’m trying to use the IF(AND… functions in order to AVERAGE the relevant cells,

e.g., 3 columns: Quantity, Category, Vendor….
what is the AVERAGE Quantity for the Category=”nails” and the Vendor=”acme”…
or the AVERAGE Quantity for the Category=”hammers” and the Vendor=”alpha”



Eduardo delfin jr.

nice try….



mark

How do I count the number of cells that are flagged by conditional formatting?



ROD

COUNTIF will work…
your conditional formatting “condition” must be in quotes like below. If any cell between E5:E16 is less than 4, then count that cell and this yields the total number of cells that qualify.
=COUNTIF(E5:E16,”<4″)



joe reed

would like a email address from u so i can send a example excel sheet so i can better explain my problem



Nazar

hi

Its a very easyist way to learn about excel and and which concern from finance..
and thanks to all team..
who did work hard for us

Thankssssssss



Essam AKlan

This is great description. Thank you



Elizabeth

I need help. I have tried everything I can think of to get this formula to work. Can someone help please.

In the January table’s Packing column. enter a condition that displays the value Double if the order is more than 9 units sold AND the order is not in state. Use the calculated columns Units Sold (Column C) and In State(Column G) in your condition.



scott barrett

I would like to know how i can count the number of cells that have a certain range of numbers. for instance, how many cells contain a number greater than thirty but less than 40.



Corkman

Trying to work a fommulae
if passenger wants single ticket charge 33 or return charge 33. Say ticket type is in C7
We are using if(or(C7=”s”,C7=”D”),33,66)

but formulae doesnt work. Any ideas
Noel



francesco

Thanks so much! very helpful and well written!



francesco

[for Noel]

in my Italian version, I must use “;” instead of “,” between the logic arguments and between the “if_true” and “if_false”.
I must change also “if” with “se” (if in Italian) and “or” with “o”; so, control wour version language of Excel.

so, your example becomes:

=se(o(C7=”s”;C7=”D”);33;66)

good luck!



Chris

Hi,

i am a novice in excel formula and would very much appreciate your kind assistance here.

My sample data in excel spreadsheet consists of 1 row and 13 columns. teh cell at each of the column has figures in it. what i want to achieve is as follows:

Start from the LAST column (column 13) and check the figure in that cell. If the value is not equal to 0, return a expected value of 0 in a new column (Column 14). Else if the value at column 13 is equal to 0, then proceed to check the column beside (Column 12) and so on until the FIRST value that is not equal to 0 comes by. After that, return a expected value at column 14 for the number of cells with 0 that has been encountered before a cell with 0 value is found. Another conditon that must be satisfied is that when the values in all 13 cells are 0, return expected value of 999.

Example: If column 13 has value 0, column 14 will show 0. If column 10 has value 0 and coulumn 11-13 has value = 0, then column 14 will show 3. If column 1-13 has value 0, then column 14 will show 999.

Is it possible to use if-else statement instead of VBA to achieve the results? As i know very little about VBA and I will have massive data to be imported to excel which makes copying of formula much easier if the formula is via excel. I do not know how to make all the cells in column 14 to have te same logic if VBA is used. And also, I know IF else statement is restricted to 7 nested IF statements whcih might not be able to achieve my desired results due to the referencing to cells at multiple columns required. How can i get around this?

Sorry for the many questionsasked as I am really limited in my excel skills. Hope to receive your reply very soon.

many thanks



Chris

Hi,

Some typo error in the objective. Correct details as per follows:

Start from the LAST column (column 13) and check the figure in that cell. If the value is not equal to 0, return a expected value of 0 in a new column (Column 14). Else if the value at column 13 is equal to 0, then proceed to check the column beside (Column 12) and so on until the FIRST value that is not equal to 0 comes by. After that, return a expected value at column 14 for the number of cells with 0 that has been encountered before a cell with not equal to 0 value is found. Another conditon that must be satisfied is that when the values in all 13 cells are 0, return expected value of 999.

Example: If column 13 has value not equal to 0, column 14 will show 0. If column 10 has value not equal to 0 and columns 11-13 has value 0, then column 14 will show 3. If column 1-13 has value 0, then column 14 will show 999.

Sorry for the confusion. Thanks again.



johnson

Hi

Was very helpful

Thanks



Duda Capili

Pls. help me my professor gave an assignment to me:

0-69 = Not Valid
70-74 = Failed
75 – 79 = Need Improvement
80 – 84 = Good
85 – 89 = Very Good
90 – 100 = Excellent

How can i do this if for example i type 0-69 in a cell the answer will be “not valid” , then if 70 – 74 is “Failed” and so on. But my formula should be only written in one cell. How,s that?. Pls help me im really having a hard time.



Duda Capili

Pls. help me my professor gave an assignment to me:

0-69 = Not Valid
70-74 = Failed
75 – 79 = Need Improvement
80 – 84 = Good
85 – 89 = Very Good
90 – 100 = Excellent

How can i do this if for example i type 0-69 in a cell the answer will be “not valid” , then if 70 – 74 is “Failed” and so on. But my formula should be only written in one cell. How,s that?. Pls help me im really having a hard time.

Tulungan nio naman ako dito nakikiusap ako.



MOhamed

Can i Use “IF(AND ” & “IF(OR ” in the same formula ?

i.e. I need to get same result for 2 different formulas “every single one of them contain IF(AND ”
like =IF(OR( “formula1 if(and ” , “Formula2 If(and ” ), True,false )



Shahrokh

How can I expand the limit of 7 IF statement in a formula? I’ve to include 12 month in the statement, not just 7 month.

How can I expand the following formula to meet the need, meaning to include 4 more month?

=IF(E5=”Dec”;Tracking!E6;IF(E5=”Nov”;Tracking!D6;IF(E5=”Jan”;Tracking!F6;IF(E5=”Feb”;Tracking!G6;IF(E5=”Mar”;Tracking!H6;IF(E5=”Apr”;Tracking!I6;IF(E5=”May”;Tracking!J6;IF(E5=”Jun”;Tracking!K6;0))))))))

Thank you,

Shahrokh



Erik

What if I have three criteria which need to be met. Can I still use the AND function? How about pairing it with COUNTIF? My story problem is this: Return the count of students who are in Class A, are Seniors and do not have a text book.



damario

this formula doesnt give correct results pls help
=IF((I11>$S$4),I11/M11,IF((M11>$S$5),I11/M11,(IF((OR(O11=$S$2,O11=$S$3)),I11/M11,0))))



vj

I am trying to shorten a Weighted Average formula, Basically trying to get an overall score based on the combined scores of the 3 individual components scorecard. The weighted Average should ignore any blank or zero values and calculate the weighted ave for the remaining 2 components,,the formula goes like this,,IF(AND($G$81=”",$G$101=”"),$G$61,IF(AND($G$61=”",$G$81=”"),$G$101,IF(AND($G$61=”",$G$101=”"),$G$81,$AC$19)))IF($F$61=”",$F$81*$F$6+$F$101*$G$6,IF($F$81=”",$F$61*$E$6+$F$101*$G$6,IF($F$101=”",$F$61*$E$6+$F$81*$F$6,$F$61*$E$6+$F$81*$F$6+$F$101*$G$6))) //THIS long version works. The simpler version below did not work as it did not ignore zero ($E$6*G61)+($F$6*G81)+($G$6*G101) //Please can you help me with an alternative to the long version formula above?? thanks so so much



Anthony

I have a schedule of values that needs to be updated monthly. My question is how do I update one cell and then have it automatically update two others.

I have a value of “1″ in cell I3 for the inital month. On the following month, I want to update I3 +1 so it displays “2″ and when I do this I want whatever values are in column “E” to display in its respective row and column “D” and have “E” revert back to “0″ in the respective row.

Confusing enough? HAHA

Thanks for the help



vj

Hi Anthony, can you send sample data, so it is more clear as to what you need?
vj



Angie

Hi I need help with this formula … I am trying to assign different audit ratiings based on the mean of majors and minors.

So for … Major Minor
Unacceptable > 1
Satisfactory 0.1 – 1 > 1
Exceptional <= 0.1 1,”Unacceptable”,IF(AND(E12>0.1, E12=1+G12>1,”Satisfactory”,IF(E12<=0.1,G12<=1,”Exceptional”)))

HELP!!!!



Elie

i have a simple question about the IF statement:
how many If statement can i add in a single formula? it’s giving me an error at the 8th IF statement allthough it’s correct
can it be just a mistake i’m overlooking?



gary

I am using if statements and not sure if they are right.

I require a cell to state pre start, pre closure, post closure if the the following dates are enter in another cell:
Pre Start for everyting dated before 01/06/09.
Pre Closure for everything between 02/06/09 and 27/09/09
Post Closure for everything 28/09/09

Any help will be great.

Cheers



WILLIAM ALLEN

i AM trying to check for a date in each of the five colums and if there is a date in each of the column then in the last column I would like the word “complete” print in the next cell.



Daniel

You are AMAAAZZZING!! this formula helped me master the solution to my spreadsheet problems that i’ve been struggling with for months. You made this crystal clear and I thank you sir! I wish i could buy you a beer!



Nick D

Hi,

Thanks for the tutorial. It almost solves my issue but not quite.
This is what I want to do:
I need to give an employee a bonus in the form of rounding up his weekly commission to $ 500 if his total net sales are equal or greater than $2,500. The result that I’m basically trying to get in the cell is “0″ if the employee doesn’t reach at least $ 2,500 in sales and “$ 500 -(minus) the value of two other cells combined (his regular commission for the week) if he reaches $ 2,500 or more in sales.

Thanks,

Nick D



Paige

I am trying to figure out why this statement won’t work and I can’t figure it out…HELP!!:

=IF(F3=1,F3=4, F3<10)), “80″, “120″)))

What I am looking for is to figure out how many vacation hours people get, F3 in my formula is the number of years they’ve worked there:
0-1 year = 0 hours
1-3 years = 40 hours
4-10 years = 80 hours
10+ years = 120 hours



Gary

I think one of these statements is what I have been looking for.

I have two columns which are labeled V and W that I am trying to work with. Column V is labeled Contacted and Column W is labeled Contact date. What I want to occur is that when I enter a Y in column V, the contacted column I want the date to be placed into the adjacent column which is W, contacted date but only if it is an empty cell. If it already has a date or information in it I do not want it to be changed. How do I write this formula?



Greenmantle

@Paige

Try this:

=IF(F3<1,”0″,IF(F3<4,”40″,IF(F3<11,”80″,”120″)))

Should do the trick

@ Nick D
How about:

IF(A1<”0″,”0″,(500-(B1+C1)))

Where A1 hold the amount of sales, B1 and C1 are the two cells that combined give his regular comission

Nice tut… tx



pat

hi, i am trying to creat a formula that when a particular cell says “nil” 38 other cell turn blank..can this be done..thanks



Andrew

Hi, there. I am trying to create a formula in Excel that I’m not sure will work. I’m trying to use if statements, but no luck. Here’s what I’m trying to do:

I want one cell to be based on a few rows of two columns, so like if D3 = 1, +B3. This works if I put Cell=IF(D3=1,+B3). I want to do this for every row in the columns, though, so like Cell=IF(D3=1,+B3),IF(D4=1,+B4),etc. Is this possible? Will I need to create another spreadsheet to handle this in the back-end?



Paula

I have part of the formula working but, need to have part of it replaced with yes or no according to which of the criteria is met.

Here is the detail of what I am trying to accomplish along with the formula I have so far:

Cell A = WEEK 1
Cell B = WEEK 2
Cell C = WEEK 3
Cell D = WEEK 4
Cell E = WEEK 5
Cell F = Average Monthly
Revenue

Cell A1 = Week 1 Cell A2 = 3292.57
Cell B1 = Week 2 Cell B2 = 5466.61
Cell C1 = Week 3 Cell C2 = 3046.40
Cell D1 = Week 4 Cell D2 = 13638.36
Cell E1 = Week 5 Cell E2 = 0.00
Cell F2 contains 27,562.15 using the following formula =SUM(A2:E2)*4.333/4

Cell G1 = 10,000
Cell H1 = 12,000
Cell I1 = 16,000
Cell J1 = 20,000
Cell K1 = 20,500
Cell J1 = 25,000

Cell H2 contains YES using the following formula
=IF(AND(F2>=H1,F2>=J1,F2>=M1),”YES”,”NO”)

Cell J2 contains YES using the following formula
=IF(AND(F2>=H1,F2>=J1,F2>=M1),”YES”,”NO”)

Cell M2 contains YES using the following formula
=IF(AND(F2>=H1,F2>=J1,F2>=M1),”YES”,”NO”)

The part I’m having problems acheiving is, if the 2nd criteria is met then I need the 1st criteria to return NO. Then if the 3rd criteria is met I need the 1st and 2nd criteria to return NO.

I would greatly appreciate any help anyone could possibly give to me.

Thanks,
Paula (stressed to the max)



Josh

Great site – thank you. I have a question though. My goal is to write a formula for a tiered bonus for salary. For example, a salary of $150,000 may be bonused as follows: 5% for first $25,000, then 10% for second $25,000, 15% for third $25,000, etc. Can anyone help me to write this? I tried using the original formula on this site which was great; however, I don’t think it accounts for the changing % by tier. Thanks for any input. – JL



POULOSE

i am trying to make a time study format in excel .i am facing issue in (if) function please help me and reply

matter

eg: =if(A4=”gtpt”,”get 2parts together”)

same like this having 42 set format have to connected & continued ….

i am achived 9 only after that it is not working please let me know that how to continue this upto 42 nos

thanks
poulose



pat

hi, i,m trying to get a17 to remain blank unless c16 exceeds 16500, in which case it should say”exceeds max”
if b13 is blank then c16 is also blank, otherwise it is =c13+c14=c15

what i have done which doesn’t work is
c16////=IF(B13=”",”",C13+C14+C15)

c17////=IF(C16>165000,”exceeds max”,”")

what this produces is “exceeds max” even if b13 is blank.

Any suggestions would be appreciated..thanks



Matt

I’ve been an advanced user of Excel for years. . . but had a brain freeze.

Thanks for a quick refresher.



Dale

Okay I need 1 cell to have value A or value B. ie:

if(or(A1=”blue”, A1=”red”, B1=”1″, 60.00,IF(B1=”1″, 50.00)))
Whats wrong with that?



Dale

Basically I want C1 to look at A1 and if A1=”blue” or “red” and B1 = “1″ “2″ or “3″ return 60, 150 or 270
If A1 = anything else and B1 = 1, 2, or 3 return 50,100, 150. and if B1 is blank of course ignore it.



Shelly

I am trying to write a formula that says if A10 is not blank then cells B10, C10, D10, E10, F10 should have either text or numbers in them. Can anyone help?



Mahendran

Dear Friend,

can we use more than 7 if conditions in the xls? if yes please advise me how to do.

thanx/mahendran



Diana Kreider

I am trying to add two cells if criteria is met:
If cell a1 euqls active and cell b1 equals 1 then add



Stephen kelly

I have a spreadsheet where I list weekly figures – what I need to be able to do is search the list and accumlate monthly figures which requires checking for each of the 12 different months, then check the correspong cells that relate just to the specific month and then sum them to give a monthly total – any ideas?



Sarah

Thank you so much for taking the time to post these Excel Function tutorials! You may have just helped me land the job I have been trying to get, since graduating with my masters’. Your time and effort is truly appreciated. :)

-Sarah



gary wasson

I need to conditional format cells based on two columns. Col A is text, ex. ‘BASE’ col B is number. Need a formula to say, if A= Base and B is <75 then color B ‘red’
how is the written? Thank for any help



Tony Parker

Hi, I need a formula that will sum a group of numbers in Column D when Column A = 400 and when either the Left 3 digits in Column B = “B07″ or the Left 5 digits in Column B = “B1906.” This is the latest formula I tried but without success:

=SUM(IF((A:A=400,AND((LEFT(B:B,3)=”B07″),LEFT((B:B,5)=”B1904″)),D:D)))

Any help will be much appreciated. Thank you.



Timothy Mahoney

Hi,

Is it possible in an IF statement to add things together, something along the line of if

A1 = A2 and B1 = B2, then add C1 + C2, and place it in column D, or if B1 doesnt equal B2 then multiply C1 * 1 and place it in column D ?

In plain english im trying to say if invoice number and cpt codes match, then add line payments together, or if cpt codes do not equal then multiply line payment by 1.

Thank You for any help or insight you can provide,
Tim



John Boland

Found your site today, spent loads of time readingit, great info thanks. I am looking to for exel to tell me that if
A1= same number as in B1 then C1 should be same as what is in E1, can you help with that one.
Kepp up the good work
thanks
john



leslie

@John B: This one’s pretty straightforward, although you need an “else” condition. Try putting the following formula in cell C1:

=IF(A1=B1,E1,”no match”)

With this formula, C1 = E1 if A1=B1, and if A1 doesn’t equal B1, then it says “no match”. You can change the “else” condition to be whatever you’d like.



Timothy Mahoney

could somone please help me with this formula, i made a comment above on June 5th, that shows what i am trying to do. The first part of the if statement is correct, but i am having trouble with the second part i need to put a not equal to sign between W2 and W3, can someone let me know what, or how to type the symbol and if this formula will work afterwards thanks so much. – Tim Mahoney

=IF(AND(I2=I3,W2=W3),AF2+AF3), IF(OR(I2=I3,W2><W3),AF2*1)



leslie

@Timothy: You’ve almost got it. Instead of >< the not equal sign is . :)



John Boland

Thanks Leslie, appreciate it.



Eric

8 “If’s” limitation work around for excel.

Heres how to do 64 “If’s” in excel formula. Most people wont need that many as I only needed 24 but it can be done.

http://www.mrexcel.com/forum/showthread.php?t=377642&highlight=statement+delimited&page=2



Logan

Thanks, that was very helpful. I appreciate your taking the time to type up the information and screenshots.



Joe

I am trying to calculate a scorecard for our department. In order to get a “true” average, I do not want to include fields that default to 0% or 100%. I have 4 items that are bring scored, two are permanent, two are “if’s.”

*Score1(permanent)
*Score2(If value is less than 1, do not include in the average)
*Score3(If value is less than 1, do not include in the average)
*Score4(permanent)

Basically, if either 2 and 3 (or both) are less than one, do not include them in the average.



Mc Gyver

This is great! Thanks for the help!



Paula

I want to compare three cells in a worksheet (currency), so I’m using an IF statement with a nested OR to flag me when any of the three cells don’t match the other two. That’s working fine. My problem is that I don’t want to see a flag if the difference is less than $1.00. How can I write that into the following statement?

=IF(OR(B12C12,B12D12,C12D12),”P”,” “)

In case you’re wondering, I’m using the capital P as my TRUE flag in the Wingdings2 font which produces a check mark.

Thanks for your help!



Paula

Nevermind! I figured it out. I’m using the absolute values rather than simple subtraction in the formula. Here’s the ammended formula in case anyone had the same question:

=IF(OR(ABS(B12-C12)>0.5,ABS(B12-D12)>0.5,ABS(C12-D12)>0.5),”P”,” “)



sunil

i learn excel forumula
sum
if
sumif(
and or
etc



Pallab

I am stuck with the following problem. Say I have four columns: column B has market category either A, B, or Z; column c has the record date; column D shows book closure date. Column c and D are mutually exclusive. Now I want to calculate in column E where say 3 days will be deducted from Column c if column b has “A” or “B” and column c has certian date; and 7 days will be deducted from either column c or d (whoever has value in the cell) if column c has “Z” and either column c or d has dates.



hira

this is totally waste of time, it is not helpful for me



Karl Edwards

Hello,

Excellent site and great examples by the way.

I have been trying to work out a rather complex formula where there are 3 different classifications of employee (probationary, regular and supervisor), 4 different work types (forenoon, afternoon, night and crowd control) and a different pay rate for each employee classification for each work type (12 in total). These rates are then applied to total hours worked for each employee for each work type.

It wasn’t until I looked closely at some of your examples that the penny dropped and I figured out where my error was. In order to give a little something back I have written a bit of an explanation that I hope will be of assistance to others…

Example Formula (broken down into 3 lines (from the original ‘nest’)…
IF (A1<100, A1*3, A1*4)
IF(A1<50, A1*2, IF(A1<100, A1*3, A1*4))
=IF(A1<20, A1*1, IF(A1<50, A1*2, IF(A1<100, A1*3, A1*4)))

Actual Formula used…

=IF(C11="P",(SUM(E11*$L$5))+(SUM(F11*$L$6))+(SUM(G11*$L$7))+(SUM(H11*$L$8)), IF(C11="R",(SUM(E11*$L$10))+(SUM(F11*$L$11))+(SUM(G11*$L$12))+(SUM(H11*$L$13)), IF(C11="S",(SUM(E11*$L$15))+(SUM(F11*$L$16))+(SUM(G11*$L$17))+(SUM(H11*$L$18)))))

Explanation…

Create each of the formulas separately and test them.

Before the final bracket in the first formula insert a comma and insert the second formula.

Before the second final bracket in the second formula insert a comma and insert the second formula.

i.e. move back 1 bracket place for each formula to be inserted…

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

NB to Author* I have colour coded the formula so it can be clearly shown which parts belong to which actual formula. If you want a coloured example, please send me your E-mail address and I will forward it on to you for your use.

Regards

Karl
Dragonstar



Rick Longstreet

Not sure if anyone can help me figure out this IF statement but if so, I’d be greatly appreciative. I’m trying to figure out the following:

I buy Security 123456789 on these dates @ stated Price:

3/31/2007 @ 15
6/30/2008 @ 20
12/31/2008 @ 18
3/31/2009 @ 22
6/30/2009 @ 25

I’m looking to build a statement that says IF I bought this security in 2009, I want to know the last price I purchased it in the previous year. Then in ’08, I want the last ’07 price etc etc.

Any help would be much appreciated. Thank you
Rick



Andrew

Think you for this wonderful site. It had been in my favotate for over a year. Now I have one complicated, to me that is. So I come to the experts online.

I have a EDI from FedEx which shows all the billing details and I have a PTR report (above the line charges). Ultimatly I need to find the shipping name of a matching invoice number AND the charge. If these 2 items match, I need the formula to enter the Shipper found in the FedEx file. On the PTR, the Invoice number is in the mifddle of the transaction discription, so I can use the MID function to extract it. Here’s what I have but it’s not entering the Shipper. PLEASE HELP!!!

=IF(AND(A2=Fedex!$B$2,C2=Fedex!$A$2),Fedex!C2,”")

Debt TXN Discription mid Shipper
$39.47 S 1266458901 test 1266458901

Invoice Gross Sender
1266458901 $39.47 Andy



Michael

This was a great help! I had to do a little tweaking but your site help me a ton. Multiple greater than, less than and mulitple worksheets.

=IF(AND(AB2>=’Title Page’!$I$6,Data!N2<='Title Page'!$I$5),"Y","N")



Noorullah

I have a Daily sales Report. Now after every15days or 30days rates are changing.every time i have to change the rate manually.so iwant a formula to solve this problem



OverKnight

I’m stuck on the syntax for this. I wrote it out here in plain English to make it easier to understand what I need to do.
If F1>G1, OR if F1>0.15, ”Yes”,”No”
BUT if F1G1 and F1>0.15 are true, the result should be Yes. But if F10.15, the result should be “No”.

Thanks very much for any help.



OverKnight

Sorry about the incomplete posts above. It seems to cut off after a certain number of characters, at least for me. In any case, I was able to figure out what I needed to do.
Good site – thanks very much.



Karen

Can any tell me what I have wrong here?

If(And(IsBlank(A19),IsBlank(A20),IsBlank(A21),T11*M18)),
If(And(A19>0),IsBlank(A20,IsBlank(A21),Sum(T11)-Round(A19,2))),
If(And(A19>0),A20>0,IsBlank(A21),Sum(T11)-Sum(Round(A19,2)+Round(A20,2)),
Sum(T11)-Sum(Round(A19,2)+Round(A20,2),Round(A21,2))



the new self - professed excel jedi

Thanks to your help, I was able to string 6 if/and statements together to calculate federal payroll taxes for the whole company instantly! Thank you for your step by step directions!!! :)



Shereese

I am trying to calculate sales price and sales tax but I don’t want the tax to show if it is under $100. I came up with this but can’t get it not to show.

=IF(,”B13<100,"no tax",+B13*$C$13)

help

Also I created labels on excel and want to print 1 at a time so we don't waste a whole sheet, have any suggestions?



Fernando

como puedo agregar mas de 9 if o si en una celda sin que me envie un error



Leslie

@Fernando – Con Excel 2003 no se puede agregar mas de 7 “if” en una celda sin recibir un error. 7 es el limite de Excel 2003. Con Excel 2007, no hay limite de “if”s.



del

I was trying to solve an IF problem and google’ed to this page. This was so helpful. Thank you.



Raj

Excellent and very lucid way of explaination. Thanks a ton buddy



Andrew

Thank You very much for your help



Linda Shaw

Thanks v much! I’ve just managed to achieve

=IF(AND(A30),”Less”,IF(AND(A3<=0,B30,A3>C3),”OK”,IF(AND(A3>0,A3<C3),"Less"))))

which I never would have managed without your clues!

Linda



ms

pl help me,

my problem is 10=1,11=2,3,13=7,8.14=9,10,15=11,12 and so on upto 76=199,200.

above condition in cell the person got suppose get 13. it eual 7,8.

pl give the solution



Confused???

Hi There,
I have been working on a problem for a class that I am taking, and have run into a bit of a problem. It involves the game of craps… Ont the beginning role 7-11 wins, 2-3-12 loses and all numbers become the “point”. As a first statement this is easy:
IF(C4=7, “win”, IF(C4=11, “win”, IF(C4=2, “lose”, IF(C4=3, “lose”, IF(C4=12, “lose”, “point”)))))

However, I have to write another if statement that can be dragged accross for multiple roles that indicates whether the next role makes the point, roles a 7 and loses, or roles any other number that essentially has no effect….

My question is how can I write an if statement to intuitively know what the point is four roles after the point has been established, assuming it hasn’t been roled?

ex: 7 – winner, 3 – loser, 4 – point, 6 – nothing (how can the if statement at this point recognize that the point is 4), 8 – same problem as previously, 4 – winner, new game beginning

any help you could provide would be greatly appreciated…



PS

Thank you!



Akram

Hai ,

Please can you provide the more example to clear vlookup consept…

Thanks for understandin.
Akram



Chiman

Thank for your time to explain all this. It really helped me.



HK

thanks for your time and efforts. very useful.



PATO

I have a big problem! Any help will be appreciate it!

=IF(C9=’sheet2′!B5:B104,’sheet2′!D5:D104,” “)

What Im trying to say is:

IF C9.from.sheet3 = any.value.from.B5.to.B104.from.sheet2 THEN give.value.from.D5.to.D104 ELSE print.nothing

The problem is it doesnt print anything no matter what. Any ideas?



Macca

Hi,

I have struggled to find out how to get the following table into a formula so that it could turn a result on my scorecards into our ratings systems

Scorecard Result Rating
0% NA: No Performance
1%-79% DE: Does not meet Expectations
80%-99% PE: Partially Meets Expectations
100%-109% ME: Meets Expectations
110%-119% EE: Exceeds Expectations
>120% SE: Significantly Exceeds Expectations



Kelly

I am an advanced user and have developed a tool for use in communicating marketing programs. I nest IF functions with VLOOKUPS to calculate the dates the agency must provide art.

Currently this particular formula is:
IF(B28>$BC$1,VLOOKUP(B28,’Long Lead Deadline Calendar’!$A$5:$B$45,2,FALSE),IF(C28>$BC$1,(VLOOKUP($D$4,’Short Lead Deadline Calendar’!$A$5:$C$500,3,FALSE))))

However, I now need to add a third element into the mix. How can I write the formula to take the following into consideration:
- B28 > bc1 BUT returns an error
- C28 > bc1 BUT returns an error
- if that happens, then take the date provided in B28 and subtract 42 days

Help! This one has me stumped!

Kelly



Adrian

Spent an hour and a half getted confused over my nested ‘if’ statements. Spent 5 minutes reading your blog, and got the correct functionality on my spreadsheet in 2 minutes.

Excellent blog!



noble

I have a list of students name, marks OFdifferent subs, total etc.
I want a function to print the name from range A2:A18 .having highest total( range – G2-G18 )



Linda

How do you show a if function if you want a discount using Y or N if they have a Ngive .01% discount if Y no discount



Ken

TO: PATO
FORMULA: =IF(C9=’sheet2?!B5:B104,’sheet2?!D5:D104,” “)

The formula is simply incomplete. I crunched the math… Try this:

=IF(Sheet3!$C$9=(MAX(COUNTIF(Sheet2!$B$5:$B$55,Sheet2!$B$5:$B$55)))>1,Sheet2!D5,”")

Be sure to change all the values to what range you want. Just place the formula in EACH cell you want to have the value in. (Paste the code then copy using the black square in the bottom right to copy the code to the cells you want to respond – the formula will update itself during the copy).

Basically this code searches for duplicates within C9 and column B then returns each individual value that is shown to the D column (or nothing if no duplicates).

Hope this helps. Good luck!



raj

hi
pls send me excel formuls step by step
use



Hoji

Here’s a list of many of the formulas, these are pretty easy to follow.

http://www.cpearson.com/Excel/Topic.aspx



Amy in Accounting

Hi there,
I am trying to work on a timesheet spreadsheet. The cells receive data from another sheet but on Thursday and Friday, the end of the week, I want the spreadsheet to seperate any overtime to be paid. J column shows 8:00 format time worked, K column multiplies J by 24 to get 8.0 hours format and L column is the calculation of overtime. Row 18 is Thursday and row 20 is Friday. Employees have to manually change the data in columns C through H for Friday so that they can manually enter overtime as a seperate entry in column L. Unfortunately, I can only change the formulas, not the actual layout because it is a mandated timesheet. In other words, I cannot change the spreadsheet layout, etc. I’d like it to automatically split out overtime, like I have done for annual/sick leave etc. I achieved this until I realized that some people get to overtime on Thursday, so after successfully entering a formula for Thursday I tried the following formula for Friday:
=IF((SUM(J8:J20)*24)>40,((J22*24)-40),0)+IF(L18>0,J20*24,”IF”)
the spot where I typed “IF” I would like the formula to refer back to the first IF logic. How do I achieve that? It seems that having two days of overtime makes it more difficult. Any suggestions for column K would also be helpful.
Thanks,
Amy



- b

Thanks for this information! My officemates and I were having trouble figuring out the proper syntax for AND and OR.



GUEST

Amy in accounting, the “IF” must contain the original formula (a cell cannot reference itself, it’s the same as dividing by zero). Hope it helps.

=SUM(IF((SUM(J8:J20)*24)>40,((J22*24)-40),0),IF(L18>0,J20*24,IF((SUM(J8:J20)*24)>40,((J22*24)-40),0)))



grateful

Thank you very much for this tutorial. I am taking an excel class and had difficulty figuring out how this works. Had an exam today and aced it because of you! Thanks a mil!



nilofar

it a very great teaching methad but i need ur hep i put a formula =if(f5>=15000,”200″, if(f5>=9000,”150″, if(f5>=6000,”80″, if(f5>=3000,”20″,”0″)))) in f5 to f30 but i want to sum all the f5 to f30 coulam but i can do this i don’t know what formula i can apply plz help me.



Farah

Is there any easier way?



KEN

To: Nilofar

Use SUM(range) and in place of range, highlight the cells you want added, rows or columns only.

To: Farah

There are multiple tutorials and examples of formulas online. If you cannot find a solution online, don’t be afraid to experiment! Start with the simple IF function and try various simple maths, then move on to incorporating Excel formulas to do the math for you. Check out the posts above, they can help more.



Santosh

Hi,

My name is Santosh and i would like to know how to use the nested if statement for the aged breaks which should provide me the details as below.

Below is the example which has been done manually.

Age breaks Age Buckets
6 0 – 7 Days
9 8 – 15 Days
15 8 – 15 Days
25 31 – 60 Days
74 61 – 90 Days
98 91 -180 Days
62 61 – 90 Days
55 31 – 60 Days
101 91 -180 Days
365 181 – 365 Days
755 > 365 Days
189 181 – 365 Days

Request you to help me out in identifying how to use the nested if formula for the same.

Awaiting your prompt response.

Thanks and have a nice day



Sukh

Hi,

I cant get this to work,

I want row of 4 columns, that contain Pass or fail to show in a 5th column as
If all are pass then pass otherwise fail. However if any of the first columns are blank i dont want anything to appear in the 5th column as I need to do a count of all the passes and fails in a seperate table

Pass Fail Pass Pass Fail



Shiva

I am trying to retrieve a data calculated on 4 various sheets which are again further sub-divided to 3 columns in each sheet with result as “Accepted/Rejected” in sheet 1 which is further linked with 14 categories, with IF function, I was successful for 7 categories, please can some on solve the above…Thank you..

=IF(B10=”LOW/MINOR/SIMPLE-E PQC”,IF(‘Criteria-E PQC’!H37=”ACCEPTED”,”ACCEPTED”,”REJECTED”),IF(B10=”MEDIUM-E PQC”,IF(‘Criteria-E PQC’!Q37=”ACCEPTED”,”ACCEPTED”,”REJECTED”),IF(B10=”CRITICAL/HIGH/COMPLEX-E PQC”,IF(‘Criteria-E PQC’!Z37=”ACCEPTED”,”ACCEPTED”,”REJECTED”),IF(B10=”LOW/MINOR/SIMPLE-E PDQC”,IF(‘Criteria-E PDQC’!H37=”ACCEPTED”,”ACCEPTED”,”REJECTED”),IF(B10=”MEDIUM-E PDQC”,IF(‘Criteria-E PDQC’!Q37=”ACCEPTED”,”ACCEPTED”,”REJECTED”),IF(B10=”CRITICAL/HIGH/COMPLEX-E PDQC”,IF(‘Criteria-E PDQC’!Z37=”ACCEPTED”,”ACCEPTED”,”REJECTED”),IF(B10=”LOW/MINOR/SIMPLE-P PQC”,IF(‘Criteria-P PQC’!H58=”ACCEPTED”,”ACCEPTED”,”REJECTED”),”N/A”)))))))



julio

Muy bueno la verdad que me a ayudado mucho …..estoy bastante agradesido….gracias por todo ….



Carolyn

I am trying to use IF/AND/OR statements to assign a value based on the highest letter in a 3 fields. If D shows in any of the fields, display AVPN. If D does not show in any field but C does, display VPN, if D or C does not show in any fields, but B does, display SSL. If D, C or B does not show in any field, but A does, display No SIR. If all fields are blank, no value. Here is my first attempt: =IF(OR(G13=”D”,G14=”D”,G15=”D”),”AVPN”),IF(AND(G13″D”,G14″D”,G15″D”),AND(OR(G13=”C”,G14=”C”,G15=”C”),”VPN”)). Thanks for any help you can provide.



Kang Ebua

I have a problem of embedding IF function. How can I embed one IF function within another.



zvone

Hi, I need to have blank cell in which is waiting the number which represent value
of leter that i wil enter in this cell. Let it be: z has numerical value 7, I want the cell A1 to be so prepared that when I write z in it it activate with other cells numbers across 30 cells (4raws*15columns), so we have 27 letters of alphabet all with diferent value
and cell hould be ready to accept any of them what wil give us in column 16th the sum of 15 cells in raws and sum of 4 cells
should not be greater than 100 nor less than 100. That is god’s claimation for a compiler of new names for all population
based on chaldean numerology. They say
that morefunc.xll may help in this regard. Thanks and happy answer.Byby….!!!!



owen

is there a way for another cell triggering the other cell to change its color pattern, for example, if I put a value or text in cell A1, B1 would have a correponding color pattern…conditional formatiing is impossible since i need seven conditions,,,say if A1=monday…B1′s pattern would turn green.. and so on….thanks in advance



Jay

hey guys can anyone plz tell me how to make a formula with IF and AND..
i must have a formula that will do the calculation of: a student will pass the economics course if his average grade is above 65 or the grade of his 3rd exam is above 80…
can anyone plz help!!



prafulla

=IF(OR(AND(F72>0,F135>0),AND(F72<0, F135<0)), “Explain”,”") Plz give me right soluton,Thank u.



BRAJESH

I have to arrange in a cell A to D condition given below:

if Cell A = boy,Cell B = Urban & Cell C= Gen then Cell D = “A”
IF Cell A = boy,Cell B = Urban & Cell C= OBC then Cell D = “B”
IF Cell A = boy,Cell B = Urban & Cell C= SC then Cell D = “C”
IF Cell A = boy,Cell B = Urban & Cell C= ST then Cell D = “D”

Please help me to arrange this?



Neeraj Sharma

this is so helpuful



MICHEAL B

Hi my dear friends ,please help me out to deal with if fuction when i am under situation.

Please refer the apppended table which is source file .
S_no Name
1 MICHAEL
2 MANI
3 MURUGAN
4 KUMAR
5 JASMINE
6 JOSE
7 SAMIR
8 SANTOSHE
9 RAJIV
10 RAGU

From this table i need to pull the details

Requirement
1. if customer name is more than “5″ character and also the customer name should contain charact “E” , the “*” symbal should be placed from the position of the character “E” found till the position of 100.

In case if not , the Name should appear in Name coulm without any modification

Here i used to get the deatail like this.

=IF(AND(LEN(VLOOKUP(A2,SOURCE!A:B,2,0))>5,SEARCH(“E”,VLOOKUP(A2,SOURCE!A:B,2,0))),((REPLACE(VLOOKUP(A2,SOURCE!A:B,2,0),SEARCH(“E”,VLOOKUP(A2,SOURCE!A:B,2,0)),100,” * “))),VLOOKUP(A2,SOURCE!A:B,2,0))

I got the output like

S_no Name
1 MICHA *
2 #VALUE!
3 #VALUE!
4 #VALUE!
5 JASMIN *
6 JOSE
7 #VALUE!
8 SANTOSH *
9 #VALUE!
10 #VALUE!

but i need to have the name instead of #Value if the condition doesn’t match.

I used the formuals in 2003 version

Request you to help me on this

Regards
Michael
9962021008



MICHEAL B

HI Carolyn,

Pls find the solution

=IF(OR(G13=”D”,G14=”D”,G15=”D”),”AVP”,IF(OR(G13=”C”,G14=”C”,G15=”C”),”AVPN”,IF(OR(G13=”B”,G14=”B”,G15=”B”),”SSL”,IF(OR(G13=”A”,G14=”A”,G15=”A”),”NO SIR”,” “))))



MICHEAL B

HI RAJESH

pls find the solution

=IF(AND(A1=”BOY”,B1=”URBAN”,C1=”GEN”),”A”,IF(AND(A1=”BOY”,B1=”URBAN”,C1=”OBC”),”B”,IF(AND(A1=”BOY”,B1=”URBAN”,C1=”SC”),”C”,IF(AND(A1=”BOY”,B1=”URBAN”,C1=”ST”),”D”,”NOT FOUND”))))



MICHEAL B

Hi prafulla

pls find the solution

=IF(AND(A1>0,B1>0),”expalin”,IF(AND(C1>0,D1>0),”explain1″,”"))



MICHEAL B

hi, jay

please clarify your full query since i am not able to understand exactly what you want



Rolando

I see that for the solution for prafulla you mentioned the solution twice: (well, actually you listed it as two different things by putting explain and the other one as explain1), but if they were both the same answer for both the first and second “if and” conditions, is this the only way to do it–by mentioning the answer twice?

I am curious about this and was trying to do exactly what she asked, with different values though and with one answer, and was able to do it thanks to your solution of putting in the answer twice. I am still however curious about having the answer posted twice.



MICHEAL B

hi Rolando

Please find the solution as requested,

=IF(AND(A1>0,B1>0)+(AND(C1>0,D1>0)),”explain”,”")

but anybody is able to help me on my query posted yesterday



MICHEAL B

HI JAY

pls find the solution

=IF(OR(A1>65,B1>80),”Student will pass”,”Not able to pass”)



MICHEAL B

HI Sukh,

Pls find the solution

=IF(AND(A1=”PASS”,B1=”PASS”,C1=”PASS”,D1=”PASS”),”PASS”,IF(A1=”",”",”FAIL”))



Rolando

Thank you very much Michael. I experimented by putting in a – in place of the plus, but it didn’t make a difference. I also experimented putting in a * and later a /, which both then gave me the else part / false part of the equation, which was the wrong answer. As I was looking over an older code that Excel was suggesting to correct my mistake, it had suggested to put a * in the place of the plus, but like I said, that was giving me the wrong answer.

So, is there a page you can point me to or can you briefly explain if the -, *, and / would play a role in the solution you gave me or in a similar equation, and if so, what would that be? For example, in the solution you gave me, the + serves the purpose of an OR or another if within an if.



MICHEAL B

HI Rolando

=IF(AND(A1>0,B1>0)+(AND(C1>0,D1>0)),”explain”,”")

IF A1 AND B1 CELLS OR C1 AND D1 CELLS OR A1,B1,C1,D1 CONTAIN ANYTHING MORE THAN 0,
IT GIVES “EXPLAIN” AS RESULT OTHERWISE BLANK SPACE

———————
=IF(AND(A1>0,B1>0)-(AND(C1>0,D1>0)),”explain”,”")

IF A1 AND B1 CELLS OR C1 AND D1 CELLS CONTAIN ANYTHING MORE THAN 0,
IT GIVES “”EXPLAIN”" AS RESULT IN CASE IF THE VALUE A1,B1,C1,D1 CANTAIN ANYTHING MORE THAN 0, OR THERE IS NO VALUE IN A1,B1,C1,D1 CELLS ,THE RESULT IS BLANK SPACE
——————
=IF(AND(A1>0,B1>0)*(AND(C1>0,D1>0)),”explain”,”")

IF A1,B1,C1,D1 CONTAIN , THE RESULT IS “EXPLAIN” OTHER BLANK SPACE
————

=IF(AND(A1>0,B1>0)/(AND(C1>0,D1>0)),”explain”,”")

THIS IS LIKE * ONLY BUT BLANK SPACE WILL NOT COME.IT WOULD BE #DIV/0!



Khurram inam

please use if statement to conclude fee amount through Transaction Amount .

Transaction Amount Fee

Rs. 1-1000 Rs. 50
Rs. 1001-2500 Rs. 100
Rs. 2501-4000 Rs. 150
Rs. 4001-6000 Rs. 200
Rs. 6001-8000 Rs. 250
Rs. 8001-10000 Rs. 300



Khurram inam

please solve this equation as soon as possible and reply me earlier.



MICHEAL B

hi Khurram inam

Pls find the solution

=IF(OR(A2<=1,A2=1001,A2=2501,A2=4001,A2=6001,A2=8001,A2<=10000),"Rs 300","Not Available"))))))



MICHEAL B

Hi Khurram inam
Please ignore earlier one,

Kindl find the correct one

“=IF(OR(A2<=1,A2=1001,A2=2501,A2=4001,A2=6001,A2=8001,A2<=10000),"Rs 300","Not Available"))))))"



MICHEAL B

hi Khurram inam

Please ignore the above two solutions which is not functioning properly.

This is perfect formula

=IF(AND(A2>=1,A2=1001,A2=2501,A2=4001,A2=6001,A2=8001,A2<=10000),"Rs 300","Not Available"))))))



MICHEAL B

“=IF(AND(A4>=1,A4=1001,A4=2501,A4=4001,A4=6001,A4=8001,A4<=10000),"Rs 300","Not Available"))))))"



MICHEAL B

hI Khurram inam

Extremely sorry

There is a issue in this website because exactly what i try to send the formula for what you asked,The system converts to some other formulas.This is first time i am facing such issue,

If you don’t send your mail id ,i will forward the forward to your mail.



BxCapricorn

Macca:

Assuming the number is in cell A2, the formula would be:

=IF(A2>120,”Significantly Expectations”,IF(A2>=110,”Exceeds Expectations”,IF(A2>=100,”Meets Expectations”,IF(A2>=80,”Partially Meets Expectations”,IF(A2>=1,”Does Not Meet Expectations”,”No Performance”)))))



BxCapricorn

After reading a lot of your questions, I decided to develop and share a Nested IF filter or two I developed, on my blog.

http://bxcapricorn.blogspot.com/2009/12/nested-if-matrix-solution.html

Hope this helps you work with this great Excel tool.



MICHEAL B

=IF(AND(A2>=1,A2=1001,A2=2501,A2=4001,A2=6001,A2=8001,A2<=10000),"Rs 300","Not Available"))))))



Khurram inam

Tranaction Amount Charge
1 to 1000 50
1001 to 2500 100
2501 to 4000 150
4001 to 6000 200
6001 to 8000 250
8001 to 10000 300



Khurram inam

at most outer colum to left transaction amout is specified and in other colum charges are nurated please find charges amount though transaction amount.

Regards,

khurram_inam@yahoo.com



Khurram inam

Tranaction Charge
1 to 1000 50
1001 to 2500 100
2501 to 4000 150
4001 to 6000 200
6001 to 8000 250
8001 to 10000 300



ALPO

Hi,

I am trying to make some nested IF statements for the following based on:

2 variables – Color or B&W (cell A1)
followed by
3 other variables – 1,2, or 3 (cell A2)

The variables are in 2 different cells
IF Color AND 1, then A
IF Color AND 2, then B
IF Color AND 3, then C

IF B&W AND 1, then D
IF B&W AND 2, then E
IF B&W AND 3, then F

Would appreciate any help you can give, thank you.



J Potter

I work as the subcontract supervisor at my job and I’ve been in charge of creating invoices on Excel for all of our subcontractors. Invoice workbooks contain multiple worksheets which all contain multiple nested If And Or functions and can be quite complex. The only person in the office who can answer my Excel formula questions is the Director and I hate bothering him with them. I found this page and it answered my question perfectly so I did not have to ask for help today so Thank You for sharing your knowledge!!



Yazan

im using a timesheet that shows and follow the IN and OUT timing for the workers, time IN is 8:00 AM and out is 05:00 PM. I want the IF formula that i can use to show that if the worker comes after 8:00am and leave earlier than 5:00pm



Yazan

im using a timesheet that shows the IN and OUT timing for the workers, time IN is 8:30 AM and out is 05:00 PM. I want the IF formula that i can use to show when i enter the times it happens if the workers come “LATE” after 8:00am and IF they leave “EARLY” than 5:00pm, otherwise “OK”



MICHEAL B

hi Yazan,

Pls find the solution

=IF(A2>8,”late coming”,IF(B2<5,"Yearly leaving","ok"))



MICHEAL B

HI Khurram inam

Pls find the soltion

=IF(AND(A2>=1,A2=1001,A2=2501,A2=4001,A2=6001,A2=8001,A2<=10000),"Rs 300","Not Available"))))))



MICHEAL B

HI Khurram inam,

Pls find the solution

=IF(AND(A2>=1,A2=1001,A2=2501,A2=4001,A2=6001,A2=8001,A2<=10000),"Rs 300","Not Available"))))))



MICHEAL B

HI Khurram inam,

still i am facing the error i will send you the formula in email id



MICHEAL B

HI ALPO

PLS FIND THE SOLUTION

=IF(AND(A1=”color”,B1=1),”A”,IF(AND(A1=”color”,B1=2),”B”,IF(AND(A1=”color”,B1=3),”C”,IF(AND(A1=”B&W”,B1=1),”D”,IF(AND(A1=”B&W”,B1=2),”E”,IF(AND(A1=”B&W”,B1=3),”F”,”NOT FOUND”))))))



Ron

I am a basketball coach trying to create a game rating based on stats. I am trying to create an IF AND statement to rate a players shooting performance. Cell D2 contains the players shots, cell D4 their %. I would like to have them earn 10 points for a % over 69.99; 6 points for a % over 49.99; 3 points for a % over 32.99; -3 for a % between 20.02 and 33; and lose 6 (-6) for a score under 20.01. I would appreciate any help anyone can give in correcting my formula: =IF(D2>0,AND(D4>69.99%,+10,AND(D4>49.99%,+6,AND(D4>32.99,+3,AND(D4>20.02,-3,AND(D4<20.01,-6)))))) If I add SUM at the beginning the formula shows 0; if I put quotations around "10" it says I am using a wrong value; currently it just says FALSE. Thanks for your help.



Ron

I forgot to say that if the player does not take a shot, then their score would be zero.



MICHEAL B

Ron,

pls find the soltion with exaple table

State Shots Percentage Points
A 87 87.00% 10 POINTS
B 17 17.00% -6 POINTS
C 20 20.00% -6 POINTS
D 28 28.00% -3 POINTS
E 48 48.00% 3 PONTS

=IF(C2>69.99%,”10 POINTS”,IF(AND(C2>49.99%,C232.99%,C220.02%,C2<32.99%),"-3 POINTS",IF(AND(C20),”-6 POINTS”,”ZERO”)))))



MICHEAL B

Dear Ron,

sorry pls ignore the above
because the system coverts to some others formula i suspect that there is the problem persisting in the website



MICHEAL B

RON,

pls provide your email id so that we are able to send you



Ron

Micheal B, my e-mail address is racmac@mchsi.com. There are other stats that players will earn game rating points for. I thought I would add those in later. I would be happy to try to send my spread sheet if that would be helpful. Right now if D2=0 (D2 being shots attempted) thenAC2=0 (where the IF AND formula is), C2 is shots made, D4 is the percentage of shots made and what I am trying to use to award points for. I don’t know if D4 having a formula in it messes the IF AND formula up or not. There would be a similar formula for 3 point shots and another for free throws. Thanks Ron



areena kamran

ABC Corp. Payroll System

Employee Name Basic Salary House Rent Conv. Allow. Gross Salary Tax Net Salary
Rashid 20000 6000 1000 27000
Ashraf 12000 3600 600 16200
Qamar 30000 9000 1500 40500
Hanif 45000 13500 2250 60750
Arshad 10500 3150 525 14175
Mazher 24000 7200 1200 32400
Rehan 16500 4950 825 22275
Fahad 39500 11850 1975 53325
Saher 34500 10350 1725 46575

Formulas:
House Rent = 30% of Basic Salary
Conv. Allow = 5% of Basic Salary
Gross Salary = Basic Sal. + House Rent + Conv. Allow

Tax Calcualtion:
If Gross Salary = 10000 and = 20000 and = 25000 and = 30000 and = 35000 and = 40000 and = 45000 and = 50000 and = 55000 and = 65000 then Income Tax = 14% of Gross

Net Salary
Gross Salary – Tax



areena kamran

calculate only tax of gross salary and reply me at kamranawan1@hotmail.com

thanks



MICHEAL B

Ron and Khurram inam

i sent the excel formulas to your email id on 07/01/201,Request you to check both of you.



Marsha

Hello,
I have combined some cells and now have some with duplicate values within the one final cell.

Using formula:

=(MID(I4,SEARCH(“[",I4)+1,SEARCH("]“,I4)-SEARCH(“[“,I4)-1))&”;”&H4

How can I amend that to not pull in H4 if that value is already present in I4?

Thank you.
Marsha



Walid

Hi,

Thank you for this tips, I found it very helpful especially because I’ll have final exam tomorrow and I was looking for someone to help in this.

Thanks ever so much!!

W.Shalabi
Libya



Markus

Hi,

First of all, thanks for the website, all the insight, and taking the time to answer the questions of all these people (including myself)!!

Now onto my issue. I’ve created a nesting statement:

=IF(AND(TODAY()>=(E1-60), TODAY()=(E2-60), TODAY()<(E3-60)), ETC.

where the reference cells (E1…) are a series of dates. There are approximately 15. The issue lies in the fact that the result needs to appear in one cell only. Therefore, I run into the issue of exceeding the allotted number of nested criteria. I'm using 2007 version, not 2003. What do I need to do to get around this? Thanks for your time!!!



Markus

OK??? Let’s try this again. The formula I’ve written:

=IF(AND(TODAY()>=(E1-60), TODAY()=(E1-60), TODAY()=(E2-60), TODAY()<(E3-60), E2))) ….ETC



Markus

OK…something is wrong with this website.

It rewrites what I’ve typed in the comments box?!?!?!

What is going on?????????????????????? And how do I actually get my formula to show up correctly, as I’ve written it????????????????



Derek

I am trying to make a spreadsheet that i can use to calculate scores for a physical test that we use in my work. I am trying to use IF formulas and VLOOKUP. I am using VLOOKUP to give a value for a certain time. The problem i am having is that the scores change with differant age groups. The age ranges are 17-26 and 27-39. Here is the formula I started on and I can’t figure out how to get it to work. =IF(OR(D2′CFT SCORES’!D59,VLOOKUP(‘CFT SCORES’!G2,MTC!E5:F190,2,FALSE)))
D2 is the age
D60 is where i put the number 27
D59 number 26
G2 is for the time the individual gets
H2 is where the score value (and this formula is)

Please help this would make alot of people lives alot easier.



Derek

=IF(OR(D2′CFT SCORES’!D59,VLOOKUP(‘CFT SCORES’!G2,MTC!E5:F190,2,FALSE))) Sorry this is the whole formula for the comment above.



Stephano Gondwe

sn Student name gender year 1 year 2
1 john male 63 78

2 mary female 45 23

3 Ally male 22 90

4 asha female 13 55

5 andrew male 36 25

from the above table i’m instructed to make a formula based on average 55-100(pass0 35-54(supplement) and 0-34(fail) how do I write the formula?



Sultan Mahmood Hashmi

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

Is corret functions. It is helpful where two results. But when Three effects generate due three logical text. For example =IF(OR(B6$B$2),”Decrease in salary”,”No bonus”, B6*$B$1)

Please tell me Any function can ful fill this requirement of me.

Sultan Mahmood Hashmi
092313458010



shereen

hi
am trying to do an if statement in my it sba which needs to find the discount of persons in a band that pays with full payment gets a 10 percent discount and people who pays with installments, two installments, and if you the persons pay by installment they will get an interest of 15%.
how can i do it



Barbara Larman

IF(H2>=”DM00”,”Group2″,IF(H2>=”MGR01″,”Group2″,IF(H2>=”MGR02″,”Group2″,IF(H2>=”MGR03″,”Group2″,IF(H2>=”MGR04″,”Group2″,IF(H2>=”MGR05″,”Group2″,IF(H2>=”MGR06″,”Group2″,”Group1″)))))))

I want to take a group of items and either put them in group 2 or group 1. Can anyone help with this? I would appreciate any assistance!
Cheers.
bj



crawdad101

Great site and resource, thank you.

I am having trouble with the following formula, which clearly is wrong:

=IF(OR(B2=5),AND(A2=2,B2>5),2)

My intended logic is :

IF (B2=5 OR (A2=2 AND B2>5)), then this cell’s value becomes 2.

I was hoping you might be able to shed some light on this one, I’ve been staring at it so long my eyes are bleeding. Thank you.



manish

how much “if” condition use in one formula?



Leslie

@Crawdad101: Try this:

=IF(OR(B2=5,AND(A2=2,B2>5)),2,0)

(The value of the cell if the conditions aren’t met currently defaults to “0″ but you can change that value as you need to.)

Hope that helps.



Leslie

@Barbara – I’m not sure exactly what you want to do from the comment you left (not enough info), but if you send me your file, I can take a look. You might need to use an array function.



Carolyn

Great site…my intent is…if F7 or F5 = yes, then add G9 and I9. if F7 and F5 both equal yes, the multipy I9 by 2 and add G9. If F5 and F7 equal No, then leave show G9.

=IF(OR($F$5=”Yes”,$F$7=”Yes”),G9+I9), IF(OR(AND($F$5=”Yes”,$F$7 =”Yes”),((2*I9)+G9)))),IF(OR(AND($F$5=”No”,$F$7 =”No”),G9))

Thank you for any help you can provide.



mahmoud

Great site…. nice to meet this site.
please can help me with make if statement. to this formaula:

if c2 between 0 to 3 = 6 min
if c2 between 3.1 to 5 = 5 min
if c2 between 5.1 to 8 = 4 min
if c2 between 8.1 to 12 = 3 min
if c2 between 13.1 to 35 =2 min
if c2 between 35 to 100 = 1 min

please help me to make one if statement for this condition
thank u and i waiting u



Don

Please help with trying to make this formula work.
=IF((A4-C4)>0,(A4-C4)+IF(B4-D4<0,ABS(B4-D4),0))

Thanks



Carolyn

I figured my out and wanted to close my request. Here is my solution:

‘=IF(AND($F$5=”Yes”,$F$7=”No”),G11+I11,IF(AND($F$5=”No”,$F$7=”Yes”),G11+I11,IF(AND($F$5=”Yes”,$F$7=”Yes”),I11*2+G11,IF(AND($F$5=”No”,$F$7=”No”),G11))))



Lynn

I would like to calculate tax if correctly deductedusing the IF function.

The Tax bracket are

Taxable Income Tax Rate

R 0 – R 132 000 18%

R 132 001 – R 210 000 R 23 760 + (25% of amount above R 132 000)

R 210 001 – R 290 000 R 43 260 + (30% of amount above R 210 000)

R 290 001 – R 410 000 R 67 260 + (35% of amount above R 290 000)

R 410 001 – R 525 000 R 109 260 + (38% of amount above R 410 000)

R 525 001 and above R 152 960 + (40% of amount above R 525 000)

Primary Rebate R 9 756

I would like a formular that will incorporate the tax brackets.

For instance, an employees annual salary is R150000.00. Tax is culculated as,R132000 will be taxed at 18% while the balance R18000 will be taxed at 25%.

Please assist



Caldo

Good Tutorial, thanks.
However, in the first example with AND() there is a “)” missing in both the formula and the excel screenshot, that might be a bit confusing to some people.



Sumon

thanks this site is really helpful for learnning perpose…



Bryan

Thankyou for the site. Just been tinkering with excel formulas for the last two days while quiet at work.
I had been struggling to understand the help files when combining IF/AND statements.

10 mins after reading your tutorial I came up with the formula below for a cable volt drop calculator.
The logic in the statement is sound and I hope it helps any other beginner like me.
Any comments on how to refine the statement further would be appreciated.

Thanks again.

=IF(AND(C4=”C”,C7=1),VLOOKUP(C9,CurrentC_refc_single,2,FALSE),IF(AND(C4=”C”,C7=3),VLOOKUP(C9,CurrentC_refc_three,2,FALSE),IF(AND(C4=”E”,C7=1),VLOOKUP(C9,CurrentC_refe_single,2,FALSE),IF(AND(C4=”E”,C7=3),VLOOKUP(C9,CurrentC_refe_three,2,FALSE),IF(AND(C4=”D”,C7=1),VLOOKUP(C9,CurrentC_refd_single,2,FALSE),IF(AND(C4=”D”,C7=3),VLOOKUP(C9,CurrentC_refd_three,2,FALSE)))))))



Zach

Was wondering if you could change the color if your =if used the false, or some other indicator that shows you used false without depending on a certain value.

Meaning i cannot use a normal conditional formatting, because its not going to be above or below a certain number every time…it will change.



Jessica

Hello,
Would like to have following formula…i tried and tried and tried but I keep getting errors. can you help?

If G2 contains 11 or 17 then enter N/A or else enter ADD

Thank you



Andrew Skipsey

Jessica,

=IF(G2=11,”N/A”,IF(G2=17,”N/A”,”ADD”))

Should do the trick :-)



Leslie

@Jessica: I think what you need is

=IF(OR(G2=11,G2=17),”N/A”,”ADD”)



Andrew Skipsey

Leslie’s splitting hairs a touch there, but they both work equally well Jessica.



Lindsey

Help! I have a workbook with a sheet for each month. Each month has usage data by username, but only the users who have usage are shown and it changes from month to month. One more sheet has all the usernames in the whole workbook, but now I need a formula that will put their usage for each month in one place. I have tried the following, but it does not work as expected =IF(COUNTIF(‘Dec. SA’!A:A,usernames!C2),’Dec. SA’!B:B,”")



hari

=IF(A1<132001;A1*18/100;IF(A1<210001;23760+(A1-132000)*25/100;IF(A1<290001;43260+(A1-210000)*30/100;IF(A1<410001;67260+(A1-290000)*35/100;IF(A1525000;152960+(A1-525000)*40/100))))))
dear lynn this should solve your tax problem



hari

ZACH you can still use conditional formatting if return value is false. conditional formatting to highlight cell rule to text that contains give the value F



HH

=IF(AND(A15=”Ex-Silo”,A5=”WMAZ”,(VLOOKUP(Sheet4!A2:E270,3,FALSE))),0)

I need to know waht is wrong with this Of-formula? Can you please help me



vipul

cool info !!!!!!!!!!
Helped me in exam in lot



vipul

IT was just answer paper to my questions



Mike

Heyy. Im having some problems with excel. I need to satisfy 3 functions.

- if one part is discontinued, Do Nothing
- if a part is not discontinued, is in high demand, and stocked less than 50%, Order
- if a part is not discontinued, is not in high demand, and is stocked at less than 25%, Order

so basically
I dont know how to write it with AND and OR in the same go.

I think it should go like this

IF(OR(first argument),(second argument- criteria 1 and criterai and cirteria 3),(3rd argument-criteria 1, criteria2,criteria 3) then (true) “Do Nothing” otherwise false “order”

helpppp

helpppp



Linda

Hi. I have a spreadsheet with age values in column T. I want to create a column right next to it converting the values from column T into age groups. I’m having trouble writing the proper IF statement for this. The age groups I want to create are as follows:
<10
10-19
20-44
45-64
65+
If anyone could help me out with this, it would be much appreciated!!



Jessica

@Andrew and Leslie,
thank you both for your help in the past. I have anpther question. Is following possible in excel:
IF backgroundcolour is “light yellow” then….



Muhammed Rafeek

respected sir,
I would to know more datalis about Excel Like if & other contitional Functions,formal& other tricks regarding execl 2007.so pls sent your valid information.



Anthony

Thank you.
I went to a lot of web sites, and could not find the answer. I appreciate your explanation and examples.



tarantula

I was looking for a way to compare three variables (smallest of three variables) using IF construct of Excel. And that too with out creating any temporary variable/column during the calculation. I have come up with this formula..

If(a > If(b>c,c,b), If(b>c,c,b), c)

Please let me know if you know a better way to perform the above mentioned operation/calculation.. (I know mine’s a crappy way to do the thing)



maria

hey I need some help combining if statements to apply to single cells. I have created the following 4 statements but now need to combine them – any ideas how I can do this?

=IF(C825, C8100,C8200, “2500″,)



Michael

whats wrong with this function:

=IF(C7=B7,”Same”,IF(B7>=C7,”Less”,”Greater”))



Jerry C

@MICHAEL – your formula looks absolutely Ok. I tried it in and initially got some error messages as well. Try first substituting the “same”, “less”, etc with nulls, i.e. empty quotes “”, then type in some numeric values and see what happens.
Good luck



Jerry C

@MICHAEL, had another look at your problem again – you are using the wrong quotes around your text. You should use the double quotes (above the number 2 on your keyboard) rather than pressing the apostrophe twice to get double quotes. That should fix your problem.
Enjoy your Excel!



Ajeesh

I need help for find out formula on excel
Sr Date Group Qty
1. 01.02.10 Orange 10
2. 02.02.10 Apple 20
3. 02.02.10 Orange 30
4. 03.02.10 Orange 10
5. 03.02.10 Applle 20
4. 03.02.10 Banana 20
in this type of table how i can list group wise qty in in specific date



rYAN

Hi,
I have date in column b and weekday no (1,5) in column c , Column D has services. IF column D:D (sheet 1)=column A:A(sheet2) AND Column C:C=1, B-3,C:C=2,B-4 and so on. I need to display last friday before the date value in coloumn B. Anyhelp would be much appreciated. I have tried : =IF(and(B6=Summary!C:C),Summary!M9=1,Summary!L10-3,IF(Summary!M10=2,Summary!L10-4,IF(Summary!M10=3,Summary!L10-5,IF(Summary!M10=4,Summary!L10-6,IF(Summary!M10=5,Summary!L10-7,0))))) but it doesnt work.

Thank you very much advance



Jesus Aguirre

I am trying to pass information to sheet2 (to SUM the amounts in column L4:L250 of sheet1 IF the row has the code 001 in column B4:B250 and also has the code 639100 in column C4:C250. This formula I am using is not working:
SUMIF(Sheet1!B4:B250”=001”,AND,Sheet1!C4:C250”=639100”,Sheet1!L4:L250)
What is wrong?
Thanks for your help!



Dave

I just want to say that “=AND” is pretty much the greatest thing that has ever happened to me. Thank you!



Jack Walshe

I need to classify companies as follows -

$10B as Medium Cap
>$50B as Large Cap

Can I use the IF function?



Jessica

Hi all,
is following statement possible in excel?
if K2 starts with “-” then …
Appreciate any comment.



Jenn

does anyone know how to write a formula for the following problem?
I have a list of numbers. If any of those numbers end in .01, .02, .03, etc. through .09, then it should be “false”



Anne

Jenn – (may 19) I think you’d want to use something that expresses a range.

=IF(AND(cell>0,cell<.1),"False","Okay")

I'm sure there's a more efficient way to do it, and that I probably have it formatted incorrectly, but that's the concept I believe.



Bruce

HELP!! I am trying to write a formula that evaluates the formula field and another field and then sets the formula field based on the condition…for example
Formula field = A1
Value field = B1

Condition:
if B1 is null, A1 is blank
If B1 is not null, set A1 to todays date
If A1 is not null and B1 is not null, don’t change the value already in A1

Every time I try to evaluate the formula field, I get a circular reference :(

Any suggestions? Thanks



Bruce

Added to my first comment:
My formula that I get success works, EXCEPT, It keeps updating the date field to the day the spreadsheet is open when A1 and B1 already have values:

Current formula
A1 cell
=IF(B1=”", “”,Today())



Krishna Murthy

Hi,
My intension is to add the values of Column I whereever Column B value = A and Column E Value = Network of sheet1 and want this to be populated in a cell of Sheet2.
the Formula
IF(AND((‘Unplanned’!B:B,”*A*”),(‘Unplanned’!E:E,”*Network*”)),SUM(‘Unplanned’!I:I),”"); Pelase Help



JOEY

question: I am building excel tables and I would like to use a formula that can identfy the “first in a series” of numbers to be negative. I need this in order to reveal the dates that certain debt balances will be paid off. Are there “first in series” formulas, or some other way to identify a date in which a debt schedule first shows a negative balance?



aw2636

How should I write an if statement for the folloing:

IF 0<X<90 OR 180<X<270 THEN then the optput should be B. On the other hand, IF 90<X<180 OR 270<X<360 then the optput should be S.

Note that there are two contions for every case. If one of the two conditions in case one satisfies, then the output should B, The same for case two.

Thank you in advance.



Andrew

Great Forum! I have a question can anyone help? I am trying to tell excel that if the value is the same as a certain cell, then input “5″, if not, then input 0 (that part i can do). However, i want it also to recognize that a blank entry is NOT a “0″. Currently the formula is: =IF(B31=C31,5,0). Now i want it to know that if B31 is empty then C31 should be “0″.



Ken in Dom. Rep.

Great forum
I need help
I’m dealing with grades. I need to know how to write a formula for Excel 2007 to identify which of the 4 grades (in cells G6 and H6 for 1st Semester or G36 and I36 for 2nd Semester ) are more than 65 (Below 65 is failing hence more that 65). There will only be two that will be more than 65 either G6,H6 1st Semester or G36, I36 2nd Semester and in cell H37 will add them together and divide by 2 for a Semester Average. One of these cells will possibly have nothing because normally two grades are used for the average but a second test is given to get a passing grade so it’s possible the the student failed the 1st Sem. but passed the 2nd Sem. I hope this is not confusing. I have tried to give as much detail as possible for my confusion.
Great forum and thanx in advanced



Jeff Elder

OMG… AND()

I was so frustrated by the limits of IF(). I didn’t know that AND() existed. Thanks much!



Keith Lewis

Guys, I’m looking for some help please: -

The following IF statement is one that I have managed to put together, and it works!

However the second statement needs to be ‘nested’ in somewhere and I’m a having a problem as to where.

=IF(AND(D119>=$E$113,D119=$E$113,D119<$F$114),C119*$K$114))

The Item to be added: -

=IF(A119="Apples") then run the argument above.

or

=IF(OR(A119="Apples","Oranges","Pears") then run the argument above.

The idea being that my first argument can only apply when cell A119 matches the second argument.

Warm wishes to all, Keith



Haroon Khan

Total Average
2 68.8%
3 73.9%
0 67.7%
0 58.4%
0 60.6%
3 67.6%
plz provide the solution of rank, if maximum value of total and maximum value of average > then than total & avg



James Kimball

I keep getting a #DIV/0! message when C26=0 and I have the following formula =IF(OR(C26=0,D26/C26>=1),1,D26/C26). i wrote the formula to eliminate the #DIV/0! and give me only a 1 where the D26/C26 is equal to or greater than 1. By itself IF(C26=0,1,D26/C26) gives me 1. Why can’t I combine the two logical tests? If I understand the “OR” formula it should give me 1, correct? What is wrong with my formula?



MICHEAL B

Is there any way to send an email to one person every day night from excel instead of using email id i.e Yahoo or Gmail ?

Please find an example.this is details of information staff how much they process the request daily. This has to be sent to my higher authority every day around 7 pm.

Staff Total processed
Mike 101
Raam 10
Siva 60
Mani 93
Mohan 4

But my question is “Is there any way to send an email with the above details to particular email id without manual intervention i.e Logging in “Email id” and draft the email to particular person ?

Request you to give me a solution for the same

Regards
Michael B
9962021008



Lyman

Hi, thanks for the nested IF formula, it helped alot but I ran into a problem, because I need a large number of IFs and I exceeded the limit.

What I am looking to do is
if C8 is within 30-49 and f8=2 then number is 1, if c8 is within 30-59 and f8=2.5 then number is 1. etc. is there way to do this with excel?



Sergio San Roman

=IF(D8=”cash”,G8,G8/75%)

but i want it to read if D8 is = to the word cash or flat or even or sub ? please help, thanks



Sergio

=IF(J8>23%,”……..Okay………”,”ERROR ***BM”)

I also would like it to read if J8>23% then type the word okay but if its less then type the work error but if the entire formula is J8<65% then enter the phrase "question the cell"



Richard player

I have created a spreadsheet that calculates income over expenditure and then transfer either the credit or the debit balance to the next month (new spreadsheet). The problem I have is that the credit figure transfers ok as a positive figure, however if the account is overspent and clses with a – negative figure say-£5000 when it is trnasferred into a column just for debits carried forward it remains as a minus figure. How can i keep it at the same amount but without the – minus sybol so that when new income / expenditure is added the account blanaces again. I have inserted =IF(‘MAY 10′!S5<0,'MAY 10'!S5,0) in the formla.



chris

I have made an invoice, I need to choose from a drop down list and have that put the cost of the item selected put in the next column, please help



Sue M

THANK YOU! I was working on a rather complicated IF statement, at least for me, and this website helped me figure it out. YEA! Thanks. Have a great day!



Melinda

I’m wanting to create the following statement.

If a cell (b7) has text, then copy the text to cell b33. If b7 is blank, then leave cell b33 blank. I’ve tried various If statements and cell b33 shows me the IF statement. Hope you can help.



Andrew Skipsey

Melinda,

Sounds like you don’t actually need an IF statement.
Just go to Cell B33 and then Type: =B7

Thats pretty much all you need to do – unless I read your post wrong.

Andrew



Megan

How do i write the following conditions in an excel formula for an if statement?

if cell G2 is less then cell F2 AND is more than or equal to F2-5 mark as “upcoming”, if not mark as “current”

I have tried a few things and can’t seem to get it right.



Angela

need help urgently…I’m still at work !
Trying to create formula. Here are the values
M1 is current base salary
P1 is % to midpoint
Q1 is 90% of midpoint
R1 is 125 of midpoint
Y1 is new base salary.

I need formula as follows:
If P1 is less than 90% AND Y1 is greater than Q1, return value of “midpoint”, but if
If P1 greater than 115% AND Y1 is greater than R1, then return “highpoint”



Paul

Thanks, the info was plenty of help, although I think I am in over my head. I’ve created a working if statement:
=IF(OR(ABS(D6-D5)/0.31<=0.09,ABS(D7-D6)/0.31<=0.09),D6,"")
but want to factor in if the D6-D5/0.31 and D5-D4/0.31 are either greater than or less then 0, return D6. I was also going to do the same with cells D7-D6 and D8-D7 etc..Can this be done? Please help.



Robin

Not sure if this is an IF/AND/OR challenge or not but here goes.
My problem – as the offical time keeper for the state, trying to filter some 300+ names of athletes and their running times and keeping their data up to date as individual clubs run their events.

LookUp seems to be limited in that it will only return the first instance of a name, which is a hassle if there are multiples of the same surname, or worse, where there is the same first name/surname but come from different clubs.
ie – my master datalist of athletes might look like this:

Location Surname First Time 1
City smith John
City bloggs Jack
Country bloggs Jack
Country smythe John
City sommers Fred
Country smith Jan

Where I need to enter times in against their names.

On a given weekend, I may only get back the following results:
Location Surname First Time 1
City smith John 60.00
City bloggs Jack 65.50
Country bloggs Jack 55.60
City sommers Fred 75.00

As you can see, I have created this challenge such that there are 2 Jack Bloggs, one from the City and one from the Country.
I am limited (by the sports association) to using Excel, as everyone has this. Is there a way that I can get Excel to ‘look up’ as set of results and populate the times to the actual person based on multiple factors (Surname, First Name, Club).
Hope this makes sense! It all seemed so simple when I started this!



Sathish

I m looking for a formula in excel 2007. I m preparing a subcontractors work tracking details. details like Subcont name, item, issue date and delivery date. Different subcontractor have different lead time and for different items. Hence i would like to incorporate a formula for calculating the delivery date for X subcontractor and for a Y product by key in the issue date.



Waleed

I,m looking for a formula in which i want to some value to a numeric figure, like If the cell value is 50 then (5×10) and the others like 48 then (3×10 & 2×8), my numeric values are from 1 to 50 and i want to give different division of each numeric. Thanks



Jessica

Hi everyone,
how can I phrase following: If time between AE2 and AF2 is more than 24 hours then insert “!” ? The date and time format in cloumn AF and AE is “dd.mm.yyyy hh:mm” and I have to keep it that way, please can anyone HELP?



Rachel

Can anyone help me with this formula:
=UPPER(IF((H6>89),”a”,IF((H6>79),”b”,IF((H6>69),”c”,IF((H6>59),”d”,”f”))))), but I need to add another formula to it stating, “displaying the word “pass” when students are taking the course for credit only and pass, which is rounded average of at least 65″. You’ll probably need more info than that to figure it out what I’m asking but any help would be greatly appreciated. Thanks.



hussam

trying to figure out the IF and AND functions. supposed to determine number of vacations based on:
15 days for FT employees w/4 or more years employment
10 days for FT employees w/2 yrs but less than 4
5 days for FT with 1 year but less than 2
0 days for all other
Any help?



Little

I have 30 centers (in a drop down list), with 30 different mileage associate with them, I am trying to create a formula for mileage cell that can show the right mileage when you select the right center. I have link this to other spreadsheet, so I need it to show the right mileage here.

Centers #of miles Mileage
Center 1 14
Center 2 31
Center 3 36
Center 4 32

could anyone give me some idea how can I make it work please. Thanks a lot.



Virgilio Larralde

=IF(a2=1,5, 11, 19, 22, 24, 28, 32; (C02); (IF(a2=2, 3, 8, 10, 14, 18, 25, 26; (C01); (IF(a2=4, 7, 20, 23, 27, 30, 31; (C03); (IF(a2=9, 12, 17, 21, 19; (C04); (IF(a2=2, 7, 40, 12; (C05); (-99))))

This is more or less what I want: if the number in A is 1 or 5 or 11 or 19 or 22 or 24 or 28 or 32, then display “CO2″, if not, then if it is 2, 3, 8, 10, 14, 25 or 26 then display “C01″, if not, then if it is … so on so forth…

Thanks for your help! :)



CHARBEL ABDO

i NEED TO KNOW IF I WANT TO KNOW HOW TO DO THIS EXERCICE:

iF EXAMPLE: B1130,D11=”HIGH”,”E11=LOW”,”PLOWHORSES”
B11<30,D11="LOW","E11=HIGH","PUZZLES"
B11<30,D11="LOW","E11=LOW","DOGS"



jo

ok i got this if(A1=10,0,If(A1=11,1)If(A1=12,2) but i’m getting an error saying “you entered to many argument “.
all i want is to have a condition for A1 from 10 to 18 . if anyone can help i’ll really appreciate



Joseph

Anyone have an idea how to do this in a nested IF function:
Using the information contained in B21,
no tax is applied on amounts below $0, 35% is applied on all amounts between $0 and $5,000, and for all amounts over $5,000, the rate is 35% on the first $5,000 and 48% on the remainder.



Melissa

Fantastic job! I code in VBA and have always had a difficult time trying to make Excel do what I need when I have to use it. I’ve been on many sites, and have never found such a clear and concise explanation of the If/And/Or statements within Excel. I will be BACK! Thanks so much!



Lisa

Fabulous, easy to understand explanation! Great job, Ricemutt! I especially like when someone has a long formula and it’s written out exactly how you’d say it… it helps in the understanding of how the formulas are written.



fee

Hi, wonder if you could help me, i need to know how to put this into a formula…

if yearly sales(a1) are less than 500 (B1) then need to order 25 more, if the stock level(c4) is lower than 50(d6) u should order 50(d6)

i have just made examples out of the cells…..cant get it to give me the correct answer!!



Navaneeth

I have a problem with the below nested if formula in excel.

=IF(D2″”,1,IF(E2″”,2,IF(F2″”,3,IF(G2″”,4,0))))

This checks and returns the value only for cell D2 and not other cells. Please could you help me on this…



CDT

Hi there

Probably a very easy question and hopefully someone who watches this can help me, if not oh well. When you are trying to find the max value so =max(blahblah) how do you get it so if the max value comes to a Name instead of the value the cell holds. So =IF(MAX(O6),”random Name”) But have this happen for multiple cells. SO in a series of data I.E O6,P6,Q6) etc and you want to know the name of the max of those values.

Thanks



maissa

hi can anyone help me plz
i have to found a solution to this problem: i have 3 if with three else and i want to write them in excel
if f2=1 then 0
end if
if f2=2 and G2<5 then
60000+30000*G2
else
60000*30000*5
end if
if f2=3 and g2<5 then
30000*G2
else
30000*5
end if

so can anyone find me a solution for this …thanks a lot :)



Tom Schroeder

Let?s say that A1 is a drop down menu containing the following: pv1, pv2, pv3, spc, sgt, ssg, sfc and the list is pulled off sheet #2 in the workbook. B1 and C1 contain the months (numbers only) based on the date placed in to B2 and C2 and today?s date in A3. I.E. ( if B2 shows 12/01/2009, and today?s date is 12/01/2010 in A3 then B1 should show 12mo) D1 will also be a drop down menu containing the following: PASS, FAIL (also pulled of sheet#2).
So If A1 shows PV1 and b1=>10 and is showing 10 and c1=>12 and showing 12 and d1=PASS then E1 will show ?GOOD? HOWEVER, If A1 shows PV3 then b1 must be=>12 and c1=>24 for e1 to be ?GOOD? . Ever time A1 changes, B1 and C1 requirement will also change. So to become a pay grade of pv3 one must have a minimum of 12 mo in B1 and 24 mo in C1.
Requirements for B1 and C1 IF A1 changes ( THE NUM in B1 and C1 ARE TOTAL MONTHS BASED ON THE DATES IN B2 AND C2 AND TODAY?S DATE)
A1 B1 C1
Pv1 10 12
Pv2 12 24
Pv3 24 36
Spc 36 48
Sgt 50 60
Ssg 62 80
Sfc 90 100
Well it?s a long shot how you can help.
Thanks
Tom S.



Will

I am having a problem with this formula and hoping someone can help. Here is the scnario:

Column A Column B
Bob Yes
Jane Yes
Jane No
Bob No
Bob Yes

Basically I want the calculation in Column “C” to read:

Count If column A = Bob AND column B = Yes.

In a nutshell, I want the formul to count the # of “Yes” for Bob.

Is this possible? If so, how would the formula be written? Thank you.



Annie

Is there a way to do an IF statement using date ranges??

For example:

If the period in column A is > 12 months, then put Y. With the period being in a date format (01/12/10)??

The closest I’ve managed to get is putting the actual date 12 months ago as number format in the IF statement. This just means I have to update the formula every month.

Any help would be much appreciated.



kiran

if Mr A is 50 then he is average
if Mr A is between 50 to 70 then he is Good
if Mr A is between 71 to 100 then he is V good.

Pls suggest.



Tim

I am trying to use a nested IF statement.
I have a list of names in 7 columns. This list is a representation of the people who have added comments to a field in column 8. What I am trying to do is to find a given name and then return the cell contents from the previous cell. As an example, Cell D1 contains the value I am looking for, but I want to display the contents of Cell C1 within cell A1. I want to do this for several thousand rows. Can anyone help?



Barbara

I am working on a workbook with multiple worksheets. Worksheet 1 (Organization) remains constant and is:
A B C D E F G
1 Group # Group Name Contact Phone Contract Yes/No period
2 1 AAA Little Boy Blue 555 Yes Jan-Feb
3 2 BBB Mary Mary 444 No Jan-Dec
4 3 CCC 3 Pigs 333 Yes Mar-Jun
5 4 DDD 3 Bears 222 Yes Mar-Jun
6 5 EEE Goldilocks 111 Yes Mar-Jun

Worksheet 2 is January with the same title row where I want to be able to populate the rest of the worksheet based on what the number in Column B is so that C through G will auto fill. The B Column does not always appear each month, i.e., IF JAN B2 = ORGANIZATION B:B THEN C2= ORGANIZATION C:C AND D2=ORGANIZATION D:D, ETC.

Any help you can give will be greatly appreciated!
Thank you.



MANOJ MANKALA

I am working on a workbook with multiple task (conditions) to be used at single click.
i have a file ready with the answer by normal formulas but wish to do the same by “if” formula combining all the task in 1 formula

How do i send the file for your reference as question and explain”g would be difficult on this comment page

Please help..



Helen

I am trying to create an IF AND formula with the following critera:

If the agent has a Combined Offer Rate (cell F8)88% payout is as follows:

Platinum Status (“y” in Cell L2), earns $4.50 per sale
Gold Status (“y’ in Cell L3),earns $4.00 per sale
If no status in L2 or L3 payout rate per sale is based on Conversion rate (cell G8):
G8 >=25 – $4.00
G8 >=21 – $2.75
G8 >=18 – $2.00
G8 >=15 – $1.5
less than 15 = $1.00

here is what I have so far:
=IF(AND(F8=25,4,IF(G8>=21,2.75,IF(G8>=18,2,IF(G8>=15,1.5,1))))))

I’m getting an error for logical test (f8) – any idea on what I’m doing wrong?

Thanks!
Helen



Jeff

I need help with the following if/and statement. Will post sample data and what i am trying to do but know how to relate it to cells.

Data… I have 10 products with 3 ways to pay. Annual, semi-annual and monthly. Depending on payment option and product there are different fees.

Concept… “If product 4 and monthly, then X fee, If product 4 and semi-annual then Y fee, if product 4 and annual then Z fee. I want this to work with all 10 products and 3 payment mods all in one formula. Can you help please???



mark

Can anyone tell me using the IF function whether or not you can make the second argument leave the existing value unchanged? In other words:

if(I want this value, this value, otherwise leave the cell alone)

Thanks



Cathy

Hi I need help with using 4 variables
This is the best I have been able to come up with. What i want is if A1=Enrolled or InProcess apply a value of 10, otherwise a Zero, or If A1= Rejected or DoNotProcess apply a value of Zero. Any help would be much appreciated.

=IF(N1=”Enrolled”,10,0,
IF(N1=”InProcess”,10,0,
IF(N1=”Rejected”,0,0,
IF(N1=”DoNotProcess”,0,0))))



Doll, RN

Thanking you in advance for your kind help. I’m assessing my Seniors’s physical abilities to exercise at home or in my fitness studio.

If I can get it right, I can print out their assessment & results so they may bring it to their MDs so she/he can see the Senior’s progress.

My formula has consisted of (p1 Assessment sheet), G10=IF(H4)=”male/female”, and (I4)=”the senior’s age”, and (G10) is their “ex.test result. It is then compared to “Male Scores” located on p2 or “Female Scores” located p3. The Male and Female Score sheets start with Age categories going across (B3:H3) and the exercise rage is directly below the Age Range going from (B4:H7). The second Table states: Below Average, Average, Above Average. I did try the free Army Tables, but I don’t understand their scoring system or their > or < to their ages categories. I've tried vlookup, match, and index, but to no avail. I keep getting erros (NA, Ref, etc.). Again, thanks so much; I really appreciate your help.



jerry

Wow,i’m impressed,anyway thanks,it helps me for my exams



Alex

I am trying to build a spreadsheet to keep track of “debts”. I am currently deployed in Afghanistan and play a lot of poker with the other soldiers during our down time. There are about 12 of us who play on a regular basis and I would like excel to keep track of who owes who money, since we don’t have cash out here we have to write everything down, but as you can imagine, it’s getting pretty tough to keep track of who owes who what so I’m trying to make it as simple as possible. So far I have the date of the game in column one, the winner in column two, the losers in column three and the buy-in in column four. Then I have everyone’s name in the sheet two and blocks under each of how much hey owe each person. The formula I have so far is =IF((Sheet1!B2=M1)+(Sheet1!C2:C5=J1),Sheet1!D2,0) which returned a value of $10 to one specific person which is great. However, I can’t get it to add up more than one game, which is why I’m here. I know this format is different than the example used in this forum, but somehow it works, so I’m wondering if it is a false positive or something else. Anyway, If I could get one formula to pick through the winner and losers and add up a tally that would ideal. Any help would be greatly appreciated. Thanks.



Mike Perreau

I am trying to develop a Swimming Pool ‘entry recording’ and subsequent invoicing system.

I am having trouble with a formulae that will calculate the contents of adjacent cells…
I have tried combinations of =IF(AND….;
=IF(OR…. but keep getting error messages.

Want to show you the portion of the spreadsheet…too wide to fit into this window;

Formulae tried:
=IF(AND(Q63=”To Inv.”),E63,IF(AND(Q63=”To Inv.”,H63),IF(AND(Q63=”To Inv.”,J63,”"))))))
E63,H63 & J63 are the text I want to select IF Q3 – “To Inv.”

Need to print out the relevant contact of cell E H or J 63 if it is populated. Your assistance much appreciated..

Have tried also OR combination – error message



jaycee

hi, i just want to know how to put a condition in excel. i am in the process of developing a computerized grading sheet using excel. the problem is i don’t know how to put a condition if for example not 75% of the class got the passing score in a quiz, the scores will be replaced by zero, otherwise, it will reflect the original score…please help me solve my problem..i badly need this as soon as you could help me…tnx a lot!



Ahesanali Patavat

i am searching a formula which is use to match exat value of two different cell and if condition fulfill then return third cell value in a different cell.

is there any formula or combination of formula available in excel so that i can achieve above tasks. (like IF & vlookup)

kindly revert to me as early as possible if there will be any solution.

Regards,
Ahesanali



Don F

Really appreciate this example, great explanation and very through. I know this information was written awhile ago, but it is probably the best example I have found about multiple IF’s / IIF / ELSE / ELSEIF statements for Excel.



owais

Thank u



sasikanth

I have the to capture the unqiue value from 2 different cells. see the below Exmple
I have 1 table with same name with different results. so i want the unique result in second table like “A” has 4 results (3 Done and 1 Not Done). These results should refelect accordingly in the 2nd table

Name Status
A Done
B Not Done
A Done
B Not Done
C Done
D Done
A Not Done
B Not Done

Result Done Not Done
A
B
C
D



diencephalon

It’s arduous to search out knowledgeable folks on this matter, however you sound like you know what you’re speaking about! Thanks



Gene

I need a simple “IF Statement” formula. I’m looking at two cells (C11 & C12)….my formula is in cell C14. Statement….If either of the cells (C11 & C12) is less than zero then I want a zero printed, if not, then cells C11 & C12 are to be added together. I’ve been using “and” and “or” in my formula and if excel accepts my formual I always get cells C11 & C12 added together.??????



Gene

I figured my issue…I was using a wrong cell reference. I think my formula would be =IF(C11<0,0,IF(C12<0,0,(C11+C12))).



chandu

I have a data of 300000 in 2007excel and unable to add or less any column…..

Pls replay me



John

After sending my question I studied the example and put it together. It went:

=IF(G6<$A$2, G6*$C$2, +$D$2)

Looks so easy now



Akhilesh kumar

Very Useful function
Thanks a lot……………….
Akki



Tarriff

I found this article very useful. The computation using the OR and And was a saver.



Christian

Please Help me w/ this PROBLEM..
it gave me a hard time making a formula of this…

If Age is greater than 100 and less than 5, you are going to display “Age is out of range”. If Age is greater or equal to 5 but not greater than 12, you are going to display “You are a pupil”. If Age is greater or equal to 13 but not greater than 16, you are going to display “You are a highschool”. If Age is greater or equal to 17 but not greater than 22, you are going to display “You are a bachelor”. Lastly if the age is greater than 22 and less than 100, you are going to display “You may marry if you already have a job.”

thanks…



Future Kate Criss

Hi, I’m 13 and I can’t get head or tail of this and I need it for IT homework. Do you know where I can find a simplified site for people my age??
Thanks!! Xx :) <3



B Viswanath

It’s good to understand to all excel beginners.



PRADIPARYAL

MY PROBLEMS IS EXCEL FORMULAS USE IN ONE SOLU EXP DIO BIKE FIXED AM 120000 AND MANYTIMES TYPING IS ALREADY FIXED



Shafiullah Harifal

Thanks a lot for your help regarding the explanation of the above functions. It were extreamlly helpfull and understandable examples. Since a long time I was trying to know about the use of these functions but no one could help me, but now there will be no question from my side regarding the above-mentioned problem.

Thanks



mihail

Thank you. I was battling some production files. It makes my life infinitely better



Steven Mai

I created this statement to use for looking up in box L3, if L3=”LT”, then use one formular which lookup from another sheet for result, if not, go to box M4 and use the formula there for solution. Since we can only use upto 8 IF’s, I extended the IF’s in another box (M4). Got it?

=IF(L3=”TL”,IF(K3<=TL!D3,TL!A3,IF(K3<=TL!D4,TL!A4,IF(K3<=TL!D5,TL!A5,IF(K3<=TL!D6,TL!A6,IF(K3<=TL!D7,TL!A7,IF(K3<=TL!D8,TL!A8,IF(K3<=TL!D9,TL!A9,IF(K3<=TL!D10,TL!A10,"SHIT")))))))),M4)

And here's M4: =IF(K3<=QD!C3,QD!A3,IF(K3<=QD!C4,QD!A4,IF(K3<=QD!C5,QD!A5,IF(K3<=QD!C6,QD!A6,IF(K3<=QD!C7,QD!A7,IF(K3<=QD!C8,QD!A8,IF(K3<=QD!C9,QD!A9,IF(K3<=QD!C10,QD!A10,"ShiiT"))))))))

Steven



M.King.

Nested If formulas in Excel is an absolute Godsend to me. I’d like to say it’s forte but just think i’ve been fascinated enough to want to get to the bottom of it. Yes formulas can be nested as much as 7 layers ‘deep’ but within those layers, i’m not sure if there is a limitation. Nesting AND/OR is very useful However as for those functions reading the opposite to how every1 thinks, it actually makes perfect sense to me. It’s Access expressions that i really struggle with because it’s so different. Some other Excel function i find really useful are SUM, SUMIF & VLOOKUP. Now, just for good measure, let’s see if any1 can wrap their minds around this:

=IF(B2=”",”",SUM(IF(SUMIF(Fixture_01!$D$3:$D$11,A2,Fixture_01!$E$3:$E$11)>SUMIF(Fixture_01!$D$3:$D$11,A2,Fixture_01!$G$3:$G$11),1,IF(SUMIF(Fixture_01!$F$3:$F$11,A2,Fixture_01!$G$3:$G$11)>SUMIF(Fixture_01!$F$3:$F$11,A2,Fixture_01!$E$3:$E$11),1,0)),IF(SUMIF(Fixture_02!$D$3:$D$11,A2,Fixture_02!$E$3:$E$11)>SUMIF(Fixture_02!$D$3:$D$11,A2,Fixture_02!$G$3:$G$11),1,IF(SUMIF(Fixture_02!$F$3:$F$11,A2,Fixture_02!$G$3:$G$11)>SUMIF(Fixture_02!$F$3:$F$11,A2,Fixture_02!$E$3:$E$11),1,0)),IF(SUMIF(Fixture_03!$D$3:$D$11,A2,Fixture_03!$E$3:$E$11)>SUMIF(Fixture_03!$D$3:$D$11,A2,Fixture_03!$G$3:$G$11),1,IF(SUMIF(Fixture_03!$F$3:$F$11,A2,Fixture_03!$G$3:$G$11)>SUMIF(Fixture_03!$F$3:$F$11,A2,Fixture_03!$E$3:$E$11),1,0)),IF(SUMIF(Fixture_04!$D$3:$D$11,A2,Fixture_04!$E$3:$E$11)>SUMIF(Fixture_04!$D$3:$D$11,A2,Fixture_04!$G$3:$G$11),1,IF(SUMIF(Fixture_04!$F$3:$F$11,A2,Fixture_04!$G$3:$G$11)>SUMIF(Fixture_04!$F$3:$F$11,A2,Fixture_04!$E$3:$E$11),1,0)),IF(SUMIF(Fixture_05!$D$3:$D$11,A2,Fixture_05!$E$3:$E$11)>SUMIF(Fixture_05!$D$3:$D$11,A2,Fixture_05!$G$3:$G$11),1,IF(SUMIF(Fixture_05!$F$3:$F$11,A2,Fixture_05!$G$3:$G$11)>SUMIF(Fixture_05!$F$3:$F$11,A2,Fixture_05!$E$3:$E$11),1,0)),IF(SUMIF(Fixture_06!$D$3:$D$11,A2,Fixture_06!$E$3:$E$11)>SUMIF(Fixture_06!$D$3:$D$11,A2,Fixture_06!$G$3:$G$11),1,IF(SUMIF(Fixture_06!$F$3:$F$11,A2,Fixture_06!$G$3:$G$11)>SUMIF(Fixture_06!$F$3:$F$11,A2,Fixture_06!$E$3:$E$11),1,0)),IF(SUMIF(Fixture_07!$D$3:$D$11,A2,Fixture_07!$E$3:$E$11)>SUMIF(Fixture_07!$D$3:$D$11,A2,Fixture_07!$G$3:$G$11),1,IF(SUMIF(Fixture_07!$F$3:$F$11,A2,Fixture_07!$G$3:$G$11)>SUMIF(Fixture_07!$F$3:$F$11,A2,Fixture_07!$E$3:$E$11),1,0)),IF(SUMIF(Fixture_08!$D$3:$D$11,A2,Fixture_08!$E$3:$E$11)>SUMIF(Fixture_08!$D$3:$D$11,A2,Fixture_08!$G$3:$G$11),1,IF(SUMIF(Fixture_08!$F$3:$F$11,A2,Fixture_08!$G$3:$G$11)>SUMIF(Fixture_08!$F$3:$F$11,A2,Fixture_08!$E$3:$E$11),1,0)),IF(SUMIF(Fixture_09!$D$3:$D$11,A2,Fixture_09!$E$3:$E$11)>SUMIF(Fixture_09!$D$3:$D$11,A2,Fixture_09!$G$3:$G$11),1,IF(SUMIF(Fixture_09!$F$3:$F$11,A2,Fixture_09!$G$3:$G$11)>SUMIF(Fixture_09!$F$3:$F$11,A2,Fixture_09!$E$3:$E$11),1,0)),IF(SUMIF(Fixture_10!$D$3:$D$11,A2,Fixture_10!$E$3:$E$11)>SUMIF(Fixture_10!$D$3:$D$11,A2,Fixture_10!$G$3:$G$11),1,IF(SUMIF(Fixture_10!$F$3:$F$11,A2,Fixture_10!$G$3:$G$11)>SUMIF(Fixture_10!$F$3:$F$11,A2,Fixture_10!$E$3:$E$11),1,0)),IF(SUMIF(Fixture_11!$D$3:$D$8,A2,Fixture_11!$E$3:$E$8)>SUMIF(Fixture_11!$D$3:$D$8,A2,Fixture_11!$G$3:$G$8),1,IF(SUMIF(Fixture_11!$F$3:$F$8,A2,Fixture_11!$G$3:$G$8)>SUMIF(Fixture_11!$F$3:$F$8,A2,Fixture_11!$E$3:$E$8),1,0)),IF(SUMIF(Fixture_12!$D$3:$D$8,A2,Fixture_12!$E$3:$E$8)>SUMIF(Fixture_12!$D$3:$D$8,A2,Fixture_12!$G$3:$G$8),1,IF(SUMIF(Fixture_12!$F$3:$F$8,A2,Fixture_12!$G$3:$G$8)>SUMIF(Fixture_12!$F$3:$F$8,A2,Fixture_12!$E$3:$E$8),1,0)),IF(SUMIF(Fixture_13!$D$3:$D$8,A2,Fixture_13!$E$3:$E$8)>SUMIF(Fixture_13!$D$3:$D$8,A2,Fixture_13!$G$3:$G$8),1,IF(SUMIF(Fixture_13!$F$3:$F$8,A2,Fixture_13!$G$3:$G$8)>SUMIF(Fixture_13!$F$3:$F$8,A2,Fixture_13!$E$3:$E$8),1,0)),IF(SUMIF(Fixture_14!$D$3:$D$11,A2,Fixture_14!$E$3:$E$11)>SUMIF(Fixture_14!$D$3:$D$11,A2,Fixture_14!$G$3:$G$11),1,IF(SUMIF(Fixture_14!$F$3:$F$11,A2,Fixture_14!$G$3:$G$11)>SUMIF(Fixture_14!$F$3:$F$11,A2,Fixture_14!$E$3:$E$11),1,0)),IF(SUMIF(Fixture_15!$D$3:$D$11,A2,Fixture_15!$E$3:$E$11)>SUMIF(Fixture_15!$D$3:$D$11,A2,Fixture_15!$G$3:$G$11),1,IF(SUMIF(Fixture_15!$F$3:$F$11,A2,Fixture_15!$G$3:$G$11)>SUMIF(Fixture_15!$F$3:$F$11,A2,Fixture_15!$E$3:$E$11),1,0)),IF(SUMIF(Fixture_16!$D$3:$D$11,A2,Fixture_16!$E$3:$E$11)>SUMIF(Fixture_16!$D$3:$D$11,A2,Fixture_16!$G$3:$G$11),1,IF(SUMIF(Fixture_16!$F$3:$F$11,A2,Fixture_16!$G$3:$G$11)>SUMIF(Fixture_16!$F$3:$F$11,A2,Fixture_16!$E$3:$E$11),1,0)),IF(SUMIF(Fixture_17!$D$3:$D$11,A2,Fixture_17!$E$3:$E$11)>SUMIF(Fixture_17!$D$3:$D$11,A2,Fixture_17!$G$3:$G$11),1,IF(SUMIF(Fixture_17!$F$3:$F$11,A2,Fixture_17!$G$3:$G$11)>SUMIF(Fixture_17!$F$3:$F$11,A2,Fixture_17!$E$3:$E$11),1,0)),IF(SUMIF(Fixture_18!$D$3:$D$11,A2,Fixture_18!$E$3:$E$11)>SUMIF(Fixture_18!$D$3:$D$11,A2,Fixture_18!$G$3:$G$11),1,IF(SUMIF(Fixture_18!$F$3:$F$11,A2,Fixture_18!$G$3:$G$11)>SUMIF(Fixture_18!$F$3:$F$11,A2,Fixture_18!$E$3:$E$11),1,0)),IF(SUMIF(Fixture_19!$D$3:$D$11,A2,Fixture_19!$E$3:$E$11)>SUMIF(Fixture_19!$D$3:$D$11,A2,Fixture_19!$G$3:$G$11),1,IF(SUMIF(Fixture_19!$F$3:$F$11,A2,Fixture_19!$G$3:$G$11)>SUMIF(Fixture_19!$F$3:$F$11,A2,Fixture_19!$E$3:$E$11),1,0)),IF(SUMIF(Fixture_20!$D$3:$D$11,A2,Fixture_20!$E$3:$E$11)>SUMIF(Fixture_20!$D$3:$D$11,A2,Fixture_20!$G$3:$G$11),1,IF(SUMIF(Fixture_20!$F$3:$F$11,A2,Fixture_20!$G$3:$G$11)>SUMIF(Fixture_20!$F$3:$F$11,A2,Fixture_20!$E$3:$E$11),1,0)),IF(SUMIF(Fixture_21!$D$3:$D$11,A2,Fixture_21!$E$3:$E$11)>SUMIF(Fixture_21!$D$3:$D$11,A2,Fixture_21!$G$3:$G$11),1,IF(SUMIF(Fixture_21!$F$3:$F$11,A2,Fixture_21!$G$3:$G$11)>SUMIF(Fixture_21!$F$3:$F$11,A2,Fixture_21!$E$3:$E$11),1,0)),IF(SUMIF(Fixture_22!$D$3:$D$11,A2,Fixture_22!$E$3:$E$11)>SUMIF(Fixture_22!$D$3:$D$11,A2,Fixture_22!$G$3:$G$11),1,IF(SUMIF(Fixture_22!$F$3:$F$11,A2,Fixture_22!$G$3:$G$11)>SUMIF(Fixture_22!$F$3:$F$11,A2,Fixture_22!$E$3:$E$11),1,0)),IF(SUMIF(Fixture_23!$D$3:$D$11,A2,Fixture_23!$E$3:$E$11)>SUMIF(Fixture_23!$D$3:$D$11,A2,Fixture_23!$G$3:$G$11),1,IF(SUMIF(Fixture_23!$F$3:$F$11,A2,Fixture_23!$G$3:$G$11)>SUMIF(Fixture_23!$F$3:$F$11,A2,Fixture_23!$E$3:$E$11),1,0))))

Best of luck, everone! :)



M.King.

Ohh… What the hell?? Most of my formula was cut out! =IF(B2=”",”",SUM(IF(SUMIF(Fixture_01!$D$3:$D$11,A2,Fixture_01!$E$3:$E$11)>SUMIF(Fixture_01!$D$3:$D$11,A2,Fixture_01!$G$3:$G$11),1,IF(SUMIF(Fixture_01!$F$3:$F$11,A2,Fixture_01!$G$3:$G$11)>SUMIF(Fixture_01!$F$3:$F$11,A2,Fixture_01!$E$3:$E$11),1,0)),IF(SUMIF(Fixture_02!$D$3:$D$11,A2,Fixture_02!$E$3:$E$11)>SUMIF(Fixture_02!$D$3:$D$11,A2,Fixture_02!$G$3:$G$11),1,IF(SUMIF(Fixture_02!$F$3:$F$11,A2,Fixture_02!$G$3:$G$11)>SUMIF(Fixture_02!$F$3:$F$11,A2,Fixture_02!$E$3:$E$11),1,0)),IF(SUMIF(Fixture_03!$D$3:$D$11,A2,Fixture_03!$E$3:$E$11)>SUMIF(Fixture_03!$D$3:$D$11,A2,Fixture_03!$G$3:$G$11),1,IF(SUMIF(Fixture_03!$F$3:$F$11,A2,Fixture_03!$G$3:$G$11)>SUMIF(Fixture_03!$F$3:$F$11,A2,Fixture_03!$E$3:$E$11),1,0)),IF(SUMIF(Fixture_04!$D$3:$D$11,A2,Fixture_04!$E$3:$E$11)>SUMIF(Fixture_04!$D$3:$D$11,A2,Fixture_04!$G$3:$G$11),1,IF(SUMIF(Fixture_04!$F$3:$F$11,A2,Fixture_04!$G$3:$G$11)>SUMIF(Fixture_04!$F$3:$F$11,A2,Fixture_04!$E$3:$E$11),1,0)),IF(SUMIF(Fixture_05!$D$3:$D$11,A2,Fixture_05!$E$3:$E$11)>SUMIF(Fixture_05!$D$3:$D$11,A2,Fixture_05!$G$3:$G$11),1,IF(SUMIF(Fixture_05!$F$3:$F$11,A2,Fixture_05!$G$3:$G$11)>SUMIF(Fixture_05!$F$3:$F$11,A2,Fixture_05!$E$3:$E$11),1,0)),IF(SUMIF(Fixture_06!$D$3:$D$11,A2,Fixture_06!$E$3:$E$11)>SUMIF(Fixture_06!$D$3:$D$11,A2,Fixture_06!$G$3:$G$11),1,IF(SUMIF(Fixture_06!$F$3:$F$11,A2,Fixture_06!$G$3:$G$11)>SUMIF(Fixture_06!$F$3:$F$11,A2,Fixture_06!$E$3:$E$11),1,0)),IF(SUMIF(Fixture_07!$D$3:$D$11,A2,Fixture_07!$E$3:$E$11)>SUMIF(Fixture_07!$D$3:$D$11,A2,Fixture_07!$G$3:$G$11),1,IF(SUMIF(Fixture_07!$F$3:$F$11,A2,Fixture_07!$G$3:$G$11)>SUMIF(Fixture_07!$F$3:$F$11,A2,Fixture_07!$E$3:$E$11),1,0)),IF(SUMIF(Fixture_08!$D$3:$D$11,A2,Fixture_08!$E$3:$E$11)>SUMIF(Fixture_08!$D$3:$D$11,A2,Fixture_08!$G$3:$G$11),1,IF(SUMIF(Fixture_08!$F$3:$F$11,A2,Fixture_08!$G$3:$G$11)>SUMIF(Fixture_08!$F$3:$F$11,A2,Fixture_08!$E$3:$E$11),1,0)),IF(SUMIF(Fixture_09!$D$3:$D$11,A2,Fixture_09!$E$3:$E$11)>SUMIF(Fixture_09!$D$3:$D$11,A2,Fixture_09!$G$3:$G$11),1,IF(SUMIF(Fixture_09!$F$3:$F$11,A2,Fixture_09!$G$3:$G$11)>SUMIF(Fixture_09!$F$3:$F$11,A2,Fixture_09!$E$3:$E$11),1,0)),IF(SUMIF(Fixture_10!$D$3:$D$11,A2,Fixture_10!$E$3:$E$11)>SUMIF(Fixture_10!$D$3:$D$11,A2,Fixture_10!$G$3:$G$11),1,IF(SUMIF(Fixture_10!$F$3:$F$11,A2,Fixture_10!$G$3:$G$11)>SUMIF(Fixture_10!$F$3:$F$11,A2,Fixture_10!$E$3:$E$11),1,0)),IF(SUMIF(Fixture_11!$D$3:$D$8,A2,Fixture_11!$E$3:$E$8)>SUMIF(Fixture_11!$D$3:$D$8,A2,Fixture_11!$G$3:$G$8),1,IF(SUMIF(Fixture_11!$F$3:$F$8,A2,Fixture_11!$G$3:$G$8)>SUMIF(Fixture_11!$F$3:$F$8,A2,Fixture_11!$E$3:$E$8),1,0)),IF(SUMIF(Fixture_12!$D$3:$D$8,A2,Fixture_12!$E$3:$E$8)>SUMIF(Fixture_12!$D$3:$D$8,A2,Fixture_12!$G$3:$G$8),1,IF(SUMIF(Fixture_12!$F$3:$F$8,A2,Fixture_12!$G$3:$G$8)>SUMIF(Fixture_12!$F$3:$F$8,A2,Fixture_12!$E$3:$E$8),1,0)),IF(SUMIF(Fixture_13!$D$3:$D$8,A2,Fixture_13!$E$3:$E$8)>SUMIF(Fixture_13!$D$3:$D$8,A2,Fixture_13!$G$3:$G$8),1,IF(SUMIF(Fixture_13!$F$3:$F$8,A2,Fixture_13!$G$3:$G$8)>SUMIF(Fixture_13!$F$3:$F$8,A2,Fixture_13!$E$3:$E$8),1,0)),IF(SUMIF(Fixture_14!$D$3:$D$11,A2,Fixture_14!$E$3:$E$11)>SUMIF(Fixture_14!$D$3:$D$11,A2,Fixture_14!$G$3:$G$11),1,IF(SUMIF(Fixture_14!$F$3:$F$11,A2,Fixture_14!$G$3:$G$11)>SUMIF(Fixture_14!$F$3:$F$11,A2,Fixture_14!$E$3:$E$11),1,0)),IF(SUMIF(Fixture_15!$D$3:$D$11,A2,Fixture_15!$E$3:$E$11)>SUMIF(Fixture_15!$D$3:$D$11,A2,Fixture_15!$G$3:$G$11),1,IF(SUMIF(Fixture_15!$F$3:$F$11,A2,Fixture_15!$G$3:$G$11)>SUMIF(Fixture_15!$F$3:$F$11,A2,Fixture_15!$E$3:$E$11),1,0)),IF(SUMIF(Fixture_16!$D$3:$D$11,A2,Fixture_16!$E$3:$E$11)>SUMIF(Fixture_16!$D$3:$D$11,A2,Fixture_16!$G$3:$G$11),1,IF(SUMIF(Fixture_16!$F$3:$F$11,A2,Fixture_16!$G$3:$G$11)>SUMIF(Fixture_16!$F$3:$F$11,A2,Fixture_16!$E$3:$E$11),1,0)),IF(SUMIF(Fixture_17!$D$3:$D$11,A2,Fixture_17!$E$3:$E$11)>SUMIF(Fixture_17!$D$3:$D$11,A2,Fixture_17!$G$3:$G$11),1,IF(SUMIF(Fixture_17!$F$3:$F$11,A2,Fixture_17!$G$3:$G$11)>SUMIF(Fixture_17!$F$3:$F$11,A2,Fixture_17!$E$3:$E$11),1,0)),IF(SUMIF(Fixture_18!$D$3:$D$11,A2,Fixture_18!$E$3:$E$11)>SUMIF(Fixture_18!$D$3:$D$11,A2,Fixture_18!$G$3:$G$11),1,IF(SUMIF(Fixture_18!$F$3:$F$11,A2,Fixture_18!$G$3:$G$11)>SUMIF(Fixture_18!$F$3:$F$11,A2,Fixture_18!$E$3:$E$11),1,0)),IF(SUMIF(Fixture_19!$D$3:$D$11,A2,Fixture_19!$E$3:$E$11)>SUMIF(Fixture_19!$D$3:$D$11,A2,Fixture_19!$G$3:$G$11),1,IF(SUMIF(Fixture_19!$F$3:$F$11,A2,Fixture_19!$G$3:$G$11)>SUMIF(Fixture_19!$F$3:$F$11,A2,Fixture_19!$E$3:$E$11),1,0)),IF(SUMIF(Fixture_20!$D$3:$D$11,A2,Fixture_20!$E$3:$E$11)>SUMIF(Fixture_20!$D$3:$D$11,A2,Fixture_20!$G$3:$G$11),1,IF(SUMIF(Fixture_20!$F$3:$F$11,A2,Fixture_20!$G$3:$G$11)>SUMIF(Fixture_20!$F$3:$F$11,A2,Fixture_20!$E$3:$E$11),1,0)),IF(SUMIF(Fixture_21!$D$3:$D$11,A2,Fixture_21!$E$3:$E$11)>SUMIF(Fixture_21!$D$3:$D$11,A2,Fixture_21!$G$3:$G$11),1,IF(SUMIF(Fixture_21!$F$3:$F$11,A2,Fixture_21!$G$3:$G$11)>SUMIF(Fixture_21!$F$3:$F$11,A2,Fixture_21!$E$3:$E$11),1,0)),IF(SUMIF(Fixture_22!$D$3:$D$11,A2,Fixture_22!$E$3:$E$11)>SUMIF(Fixture_22!$D$3:$D$11,A2,Fixture_22!$G$3:$G$11),1,IF(SUMIF(Fixture_22!$F$3:$F$11,A2,Fixture_22!$G$3:$G$11)>SUMIF(Fixture_22!$F$3:$F$11,A2,Fixture_22!$E$3:$E$11),1,0)),IF(SUMIF(Fixture_23!$D$3:$D$11,A2,Fixture_23!$E$3:$E$11)>SUMIF(Fixture_23!$D$3:$D$11,A2,Fixture_23!$G$3:$G$11),1,IF(SUMIF(Fixture_23!$F$3:$F$11,A2,Fixture_23!$G$3:$G$11)>SUMIF(Fixture_23!$F$3:$F$11,A2,Fixture_23!$E$3:$E$11),1,0))))



Mian M. Irfan

Sir,
Hope that you will be fine. i am facing problem in deriving a formula in excel from last two month having to conditions.
IF, AND will be used simultaneously in a formula.
here is an example for that
B11 = 4, c11= FCV and F11= PTC.
If c11=FCV and F11=PTC then i want the figure of B11 , in this case its 4 in other cell G11.
Plz help me in this formula



Dush

I need help in writing this formula what i am expecting is if I>100 which is the project code and if 90101 & 90110 is the project id if true then it should display A2 if false then it should show Y2…..Below is the formula i wrote please help =IF(AND((I23780>100),OR(90101,90110),A23780,Y23780))



Connie

Thank you very much this is exactly what I needed



deepak kumar

motes



deepak kumar

notes



Someone Sensible

Brilliant!! You know your article is fabulous when it helps people even 6 years later!! :)
God Bless!!



Cornu

Please help, I need to fill in the gaps of the different age groups to filter and pivot.

25 – 29

30 – 34

35 – 39

18 – 19
20 – 24

25 – 29
30 – 34
35 – 39
40 – 44
45 – 49
50 – 54
60 – 64
25 – 29

30 – 34

45 – 49



Sue

Hello! I love the way your responses are written and how easy they are to understand! That being said, I am still having a hard time identifying the exact formula I need in Excel:
For example, in cell E19, I am trying to dictate that if the quantity value in Cell E5 is blank or 0, that the dollar value in E19 should also be blank or $0; HOWEVER, if the value in E5 is anything but blank or 0, to use the following calculation that I would normally enter as =E6-(E10-2175). Seems pretty straight forward but I can’t get it to work out. I had gotten the formula to this point:
=IF(OR(ISBLANK(E5)=TRUE,”$0″),(E6-(E10-2175)))
Any insight you could offer would be greatly appreciated!
Thanks,
Sue



Jurgen Bach

Hi Mike,

Thanks for your site. I’ve had no problem nesting way more than 7 if statements. It’s not too difficult. And if you name your cells, or cell ranges, it makes formulas easy to look through.

Right now, I’m trying to figure out a way around Excel’s VBA limitation of 255 characters for the “formulaarray” function I’m trying to attach to a ActiveX button, so that I may enter the formula directly into a cell. Got any ideas? Right now, I have to send it to the cell with a macro in a form control, then manually add the equal sign.



cooper

Help with the IF statement.
What I am trying to achieved is:

IF C29>C30, then C29*8+5
IF C2912, then C30*8

I tried:
=IF(C29>C30, C29*8+5, IF(C2912, C30*8)))

Supposed if I enter C30=15, it should return 120.
However, it return 125.

Please help!!!!



cooper

Hi,

Please help me with the IF statement.
What I am trying to achieved is:

IF C29>C30, then C29*8+5
IF C2912, then C30*8

I tried:
=IF(C29>C30, C29*8+5, IF(C2912, C30*8)))

Supposed if I enter C30=15, it should return 120.
However, it return 125.

Please help!!!!

Thanks!



mike

This has been sooo helpful, thanks!



Abdul Raoof

Very helpfull, thanks a lot



Patrick

Hi there, I am somewhat new to if statements as well. I am trying to figure out how to write a statement that gives me a letter grade.
90 or better = “A”
80 to 89 =”B”
70 to 79 =”C”
60 to 69 =”D”
Less than 60 = “F”
I have tried so many times.
Cheers



Chris

This literally just helped me complete my final project for my IT class. The picture diagrams helped me bring together an a bi conditional IF statement and VLOOKUP. Thank you.



Chris

Hey Patrick, it sounds like you’re trying to do something similar to what I did, email me, nolencp@brevard.edu, I may be able to help you.



Joshua

This was a great help, I havnt worked with if is statements in almost a year, and at my new Internship I am required to work with them quite a bit, so this was a great way to refresh on forgotten skills.



Ramandeep kaur

sir we can calculate only 1 cell in this way. is there any way of calculating if formula on whole column at a time.. if yes then pls help me to do this



caroline

I need help. I am trying to use the IF statementthat will return acceptable or unacceptable if the percentage difference between two number is+/- 6. If the difference between the two is plus or minus 6 acceptable or unacceptable.



Nick

@ Jim

Send me your excel file, i’ll fill it out and send back. You can then track the formula to see how it calculates.

ngbaca@gmail.com



satish singh bisht

This is perfect. no comments becoz it beyondds of words.



Nathalie

Excel is giving me ” too many arguments for the below: help please

IF(L13=1.5,L13=2.5,L13=3.5,L13=4.5,L13<5.5,"E","F"))))))))



Nathalie

IF(L13=1.5,L13=2.5,L13=3.5,L13=4.5,L13<5.5,"E","F"))))))))



Anil

Pl let me know how to get a formulae for the following logic:
If a student scores +1 for every subject passed and scores -1 for every failed subject and he gets 0 for the subject exempted. How will the percentage be calculated ?? (Exempted subject should not be counted for denominator while arriving at the percentage)… Pl help..



Nick

@Nathalie

Each of your “L=x” is an argument, to which you can only have one per “if statment”.



Nick

@anil

You must have a pass/fail/exempt table that your pulling data from?



Prakash

You are simply Awesome…



Satya Singh

Awesome help. You article has really helped me thank you so much.



Tom

I have one column with numbers. Then a second column that may or may not contain numbers. The third column is the firt scolumn subratced from the second colmun. I do not want anything to show up in the third coumn if there is not a number in the second column. How do I write that? And can someone please use numbers. For each column a = 100. Column b = 50. COlumn c will also equal 50. Now a2 = 100 and column b2 is blank. column c2 will show 100, but I actually want it left blank since there is no number in b2. Any help will be appreciated!! Thanks



GARY

I give up! I have a single workbook with Sheets 1 & 2 in use. The IF function I’m trying to get to work is: =IF(N12=”TX”,Sheet2!A28:L28). The first part (N12=”TX” works just fine, is true & valid. The Sheet2 portion is the problem. I keep getting “VALUE!” errors. I’ve also tried naming that Sheet2 range and it still doesn’t work. Any ideas?



George

I have to function the following:

- up to 20,000 the commission is 50%
- from 20,000 to 50,000 the commission is 45%
- from 50,000 upwards the commission is 40%

many thanks!!



Isabel

You can put up to 60 nested if functions in one cell for Excel 2007 and later



Sunita R

I need help to build formula for below situation.

Quarter1 contains 3 months April, May & June.

If i select Q1, it should pickup the details for the completed month.

What’s formula for this.

Same way if i select quarter 2 or quarter 3 it should pickup the details for the completed month of that quarter.

Pls provide me formula for this.

Thanks,
Sunita



Chad

I am trying to create a formula that will count all of the Active and Inactive projects listed in column E for the correct case manager listed in column A. Any help would be great!

A E
Case Manager Active/Inactive
John Smith Active
John Smith Inactive
Jane Smith Active
Jane Smith Active

Total Active for John Smith=
Total Inactive for John Smith=



Brooke

Hi, I have been working on an IF function. I thought I was doing it correct, then I found out I need to only calculate this number every $50 it increases or decreases. Anything in between or over like $56 dollars over does not need to be calculated. This is what I currently have: =IF(OR($D$10>2100,$D$10<2000),(($D$10-$D$7)/$D$7),V15)
But this makes it calculate the percent change of anything over 2100 and under 2000. Instead I need it to only calculate the percent at every $50… so like at 2100,2150,2200 etc and likewise below 2050. Not at every single number that is above or below 2100 or 2000. Is this possible? Some kind of IF using increments..? Thanks



Brooke

NVM, Used a Vlookup. Got it to work.



ger

Hi need help. i created a time sheet (attendance data base). I already got the formula to compute tardy (late) columnk and under time (column l)that can determine the non-working days (i.e. Saturdays and Sundays so it wont compute either of the column k and l). My problem is I forgot to include the other 3 columns g;h and i which signifies for paid leaves. thanks



Michel

Thanks a lot. This really helps me . I am stuck with this from past many days.



deepika

sir plz tell how to calculate basic salary with using condition if.
if the dept & post of person is marketingt manager then 20000 & other dept manager salry will be 18000.then check if marketin officer then salary is 15000 otherwise 13000.



Jewel

IF(AND(C2>1/1/1922,C21/1/1946, C2<12/31/1964), "baby boomer", "generation X"))

Can you please tell me what is wrong with the formula above? I can't get it work. Thanks so much!



Jewel

IF(AND(C2>1/1/1922,C21/1/1946, C2<12/31/1964), "baby boomer", "generation X"))

Sorry. My previous one was missing some information. Here is the actual questions. What is wrong with the statement above? Thanks so much!



ZIZO

Hello, I am trying to do the following but it is not working:
=IF(A24,*0.12)))

IF the number is less than 4, it should multiplied by 10%, and if it is more than 4, then it should be multiplied by 12%, so i think there is something wrong with my formula, so would you please correct it for me?



Colleen

What am I doing wrong? I need my formula to show that if A1 and B1 on Sheet 1 match A1 and B1 on Sheet 2 then C1 on Sheet 1 should equal C1 on Sheet 2. I can’t get it. Thanks for your help.

=IF(AND(Sheet1!A1=Sheet2!A1, Sheet1!B1=Sheet2!B1), Sheet2!C1=Sheet1!C1, ” “)



Franco

I would like to ask, if its possible to have a constant number regarding using (Left and Right) formula. My problem is below

Example ” Willam vs. Goro” this is east I just do =Left(A1,6) then it came out to the next cell Willam.

But when I’m having a long name I need to count it before I got the right number, is there anyway that you can help me?

Thank you very much



Franco

I would like to ask, if its possible to have a constant number regarding using (Left and Right) formula. My problem is below

Example ” Willam vs. Goro” this is east I just do =Left(A1,6) then it came out to the next cell Willam. All I wanted is that when I used function Left(before this letter “VS.” will transfer to the next cell, also when using right after this letter “VS.” all words will be copy to the next cell.

But when I’m having a long name I need to count it before I got the right number, is there anyway that you can help me?

Thank you very much



Ann

I am trying to get the formula below work but excel only allows me to use upto 8 IFs, I know excel has its limitations but is there another function which I can use to make it work?
=IF(C2=401,Sheet2!K1,
IF(C2=402,Sheet2!K9,IF(C2=403,Sheet2!K17,IF(C2=404,Sheet2!K25,IF(C2=405,Sheet2!K33,IF(C2=406,Sheet2!K41,IF(C2=407,Sheet2!K49,IF(C2=408,Sheet2!K57,IF(C2=409,Sheet2!K65,IF(C2=410,Sheet2!K73,IF(C2=411,Sheet2!K81,IF(C2=412,Sheet2!K89,IF(C2=413,Sheet2!K97,IF(C2=414,Sheet2!K105,IF(C2=415,Sheet2!K113,IF(C2=416,Sheet2!K121,IF(C2=417,Sheet2!K129,IF(C2=418,Sheet2!K137,IF(C2=419,Sheet2!K145,IF(C2=420,Sheet2!K145,IF(C2=421,Sheet2!K153,IF(C2=422,Sheet2!K161,IF(C2=423,Sheet2!K169,IF(C2=424,Sheet2!K177,IF(C2=425,Sheet2!K185,IF(C2=426,Sheet2!K193,IF(C2=427,Sheet2!K201,IF(C2=428,Sheet2!K217,
IF(C2=429,Sheet2!K225,I(C2=430,Sheet2!K233))))))))))))))))))))))))))))))



Ann

I am trying to get the formula below work but excel only allows me to use upto 8 IFs, I know excel has its limitations but is there another function which I can use to make it work?
=IF(C2=401,Sheet2!K1,
IF(C2=402,Sheet2!K9,IF(C2=403,Sheet2!K17,IF(C2=404,Sheet2!K25,IF(C2=405,Sheet2!K33,IF(C2=406,Sheet2!K41,IF(C2=407,Sheet2!K49,IF(C2=408,Sheet2!K57,IF(C2=409,Sheet2!K65,IF(C2=410,Sheet2!K73,IF(C2=411,Sheet2!K81,IF(C2=412,Sheet2!K89,IF(C2=413,Sheet2!K97,IF(C2=414,Sheet2!K105,IF(C2=415,Sheet2!K113,IF(C2=416,Sheet2!K121,IF(C2=417,Sheet2!K129,IF(C2=418,Sheet2!K137,IF(C2=419,Sheet2!K145,IF(C2=420,Sheet2!K145,IF(C2=421,Sheet2!K153,IF(C2=422,Sheet2!K161,IF(C2=423,Sheet2!K169,IF(C2=424,Sheet2!K177,IF(C2=425,Sheet2!K185,IF(C2=426,Sheet2!K193,IF(C2=427,Sheet2!K201,IF(C2=428,Sheet2!K217,
IF(C2=429,Sheet2!K225,I(C2=430,Sheet2!K233))))))))))))))))))))))))))))))



Kim

Great Thanks to this website!!! I finally found the answer. Seek and you shall be given:)



Thomas

Hello,

I need a bit help with my formulas and of course as soon as possible :)

So, I need to calculate if the number “X” is between some other numbers. For example =IF(AND(“number x”=0);”1″;”out range”). This formula works, but I need to combine it with more formulas like =IF(AND(“number x”=30);”2″;”out range”) and =IF(AND(“number x”=90);”3″;”out range”) and =IF(AND(“number x”=180);”4″;”out range”) and so on…
When I try to combine those it gives me only result #VALUE.

I´m pretty powerless now with my own skills. Great thanks to that person who can solve this!

Thomas



Thomas

Hello again,

I made some progress, but I still cant get it working.. Now it works but with only few first “if” statements.

This is the formula I have made:

=IF(AND(HI3>0;HI330;HI390;HI3180;HI3365;HI31095;HI31825;HI33650);”8″;;)))))))

And the HI3 is always some random positive number.

Thanks a lot if someone can get this working. I think I´m close already thou.

Best Regards,

Thomas



Thomas

=IF(AND(HI3>0;HI330;HI390;HI3180;HI3365;HI31095;HI31825;HI33650);”7″;;)))))))



Thomas

=IF(AND(HI3>0;HI330;

HI390;HI3180;HI3

365;HI31095;HI31825;HI33650);”7″;;)))))))



Thomas

Seems that it wont let me paste this formula…
I´ll try once more!

=IF(AND(HI3>0;HI330;HI390;HI3180;HI3365;HI31095;HI31825;HI33650);”8″;;)))))))



Thomas

If you feel you are able to solve this problem, please send me e-mail and I will paste the formula which wont for some reason appear in the Forums.

Thanks,

Thomas



paresh

IF(C13<200001,"NIL",IF(C13<500001,"10%",IF(C131000000,”30%”,”False”))))



Renuka Prasad

Thanks a lot for getting me a job



andy

Excellent – worked perfectly, thanks.



Kulldeep

I have to prepare a calculation of CL & PL encashment if worker takes 1 leave in a month then we will pay him 1 PL (one day salary) encashed with salary, if he does not take any leave then he will get 2 days CL & PL encashment. if he takes 2 days leave in a month then he is not entiltled for any encashment of leave during that month. so there are three conditions please guide me if formula to apply.



NITESH

I want more to if function. I was trying is if function. But i dont understand.
Target:500 achievement:550,
incentive 1)500 rs.5500
2)550 rs.6000
3)600 rs. 6500
how do this works formula in excel. So Give your will make formula & send to me. pls



Santosh

Required excel if formula value is 5000 rs but first 200 rs rate is 10 rs then per 100 x 6 rs



zanycherub

what’s wrong with my formula? :(

=IF(98%100%, 1, IF(91%=97%, 1.25, IF(85%=90%, 1.5, IF(79%=84%, 1.75, IF(73%=78%, 2, IF(67%=72%, 2.25, IF(61%=66%, 2.5, IF(55%=60%, 2.75, IF(50%=54%, 3, 5)))))))))



Manjunath Gali

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

Hi Leslie

I am thankful to you for above formula..

I learned it and I am taking advantage of it..

You are great!!!!!



FORTUNE

I have gotten enough idea on if statement
thanxs alots



Jason

HI Leslie

Im needing help with my formula. My formula is used to work out commision payouts.

Example: If a sales rep achieves a sales figure within a particular target points, they will get a percentage based on the difference of their actual achievement less the min target amount. Ifthey earn below the minimum target point they will not earn any comm.
Min Target $270 000. Max Target $300 000. Commision percentage 7.5%
My formula works meeting 2 conditions below and within the range :
=IF(AND(D6>=$D$4,D6=$D$4,D6$d$5),($d$5-$d$4)*f11,”No Comm”))
Any suggestions on where I am going wrong in this?



Shawna

I am trying to set up an “If” statement that will calulate the number of “true” statements…for example I have 10 columns with different answers from a test and would like to set up a formula that will check if the answer is correct and to add up the number of answers are correct and display the number correct. Is there a way to do this with one formula or will I have to use 2 different ones?



popo

dadadadadada…..TOOOO LONG BRO! BLO BLA BLEE BLEEP BO CO!



Ashwini

Great, u r the best teacher, I know how it works but not able to explain these operators. You explanation was the best. I love it.



SARAH

I want to use the IF Function to display the word GOOD if the value in a cell is greater or equal to 10000 and the word BAD if the value is below 10000



mabsmith

Thank you very much, this was very straightforward and easy to understand!



DP

Cell A1 is Yes/No
(Condition in Cell B1)
if A1=”yes” go to Cell D5, if “No” go to E5

how i use this formula in Cell B1



Muzammil khan

can you tell me the formula for following

if profit is greater or equal to 2000 then progress is excellent, if profit is less than 2000 and greater or equal to 1000 with expense below 13000 then progress is Good else Poor



Experiglot

@Muzammil, it would be something like this:

=IF(D13>2000,”Progress is Excellent”,IF(D13>=1000,”Progress is Good”,”Progress is Poor”))

Thanks



Leslie

@Sarah

=if(a1>=10000,”GOOG”,”BAD”) when the value is in a1 !



Dipti

I want a help in using IF function for my records, so that it can be easy for me to filter the data.
If P = 0.10 then result should be 36.00, if P = 0.20 then Result should be = 40.00, if P = 0.35 then result should be = 45, if C = 0.50 then result should be 225 & if C= 0.51 & more then result should be 275.

But I don’t know how to set this. Please help.



Experiglot

@Dipti if P is cell A1 then

=IF(A1=0.1,36,IF(A1=0.2,40,IF(A1=0.35,45,IF(A1=0.5,225,IF(A1>0.5,275,0)))))



Dave

I have a spreadsheet with 3 columns
I need a nested formula that looks in Column (A) until it finds a specific value “severity 1” in column (A) and see if it true then to return the value that is in the same line in column (B).
To complicate it further I would like the ability to also look at a another column (C) for a specific value “Diesel engine” and then look at the column (A) for the a specific value “Severity 1” and return the value that is in the column that diesel engine is in that is in the same line as “Severity 1” value

Column A column B Column C Column D
TOTAL DIESEL ENGINE HYDRAULIC
Units on file 257 123 54
Number of samples 57 23 45
Units not sampled 194 584 365
Disassemblies 0 2 6
Secondary Maint. 7 4 8
Severity 0 29 56 65
Severity 1 16 23 65
Severity 2 6 9 8
Severity 3 5 5 4
Severity 4 5 2 3



Bruce

Thanks, your nested IF explanation was very helpful.



Lar

Hi, could you help me with a spread sheet function. I would like a spread sheet that would add totals from 40 different spread sheets, so any time that a name say John appears in the 40 spread sheets it would total all products and show them on new spread sheet.

Screw Nail Bolt John
5 1 9

Thank you very much, Lar



Zahid Iqbal

Thanks,
your example is very clear, easy and understandable



ANGGI

OKAY BRO



nasir

Beautiful web site, but i still want to learn



gookwook

thanks! this really helped me.



ibo

hi sire ….first i am from Iraq and i need your help in excel cause i need some think that i thing is possibly but the other don’t . any way i need to calculation of fines for each day of delay ….Accept greetings



Pete

Hi there,
I have tried sifting through the many posts to see if there has been anything relating to my query but cant seem to find anything that will help. I am writng an IF statement to calculate my pricing doing cost+(cost*xx%) depending on how much the cost price is the % mark up changes. Currently I have the following formula in;
=IF(OR(H2T3),”(H2+(H2*0%))”)
This is to work out if H2 (cost price) is greater than 0 and less than 10 then 0% is added. From here I need to then do the same for between 10.01 and 20 = Cost+(Cost*50%), 20.01 and 100= Csot+(Cost*40%) and 100.01 to 99999 = Cost+(Cost*30%) but I can only get the formula to show H2+(H2*0%) in the box after i have entered the formula nad press enter. can you help me out and let me know where Im going wrong with this formula please!?

Thankyou :)



Andy Smyth

Thanks for this, very helpful! Allowed me to calculate dimensions for a forklift design and hopefully this will become our company template.



kuldeep

=if(A2=AAPT Limited,or,(IBM GLOBAL),or,(Optus Mobile Pty Ltd),or,(Pacific Carriage Limited),or,(SEA ME WE 4),or(Southern Cross Cable Network),or,(TelstraClear Ltd),or(Uecomm),or(Vernet Pty Ltd),or(Visionstream),or(Vodafone),or,(Optus Networks Pty Ltd),”ANZ”,”NA”)



kuldeep

I need to correct this formula, I need the value if match the following name then print the ANZ otherwise print NA



fortune

pls am very happy for discovering this website, it make me to be who i want to be in excel. keep it on.



Maggy

I want to compare the Budget to the actual amount then say if it is over, under or equal. I have the over or under with =IF(B5>=C5,”Under”,”Over”), but how do I add the “Equal”???



Laakhani

hey gr8 help
it solved so much of manual work time for me.

thank you



arindam chatterjee

it’s very well explained. i would like to know how to calculate the difference between 2 dates with respect to months, days and weeks using function



nazia

thanx a loooooooooooot to make it so simple to understand .



Rachel

Hi! I’m creating a data entry page. I have 2 cells. If the answer is YES (or contains YES) for the first cell, then I would like the 2nd cell to shaded (to help the user know that info has to entered). It would unshade once an answer was input. Does anyone know how to do this? Many thanks!



sonu SIngh

My problem is – Calculate amount per connection as
Rs 0 for first 500 Calls
Rs. 1 for Next 300 Calls
Rs. 1.5 for Next 400 Calls &
rs 1.75 rest of calls

Total calls-1357

Pls solve my problem



Poulami Bhattacherya

Good site but not so clear its create confusion sometimes…



Karen van Rensburg

Please assist me with a formula for the following:

Monthly Pay Rate Tax Calculation
$0 – $3000 No Tax

$3001 – $4000 10% (Emoluments-3000)*0.10

$4001 – $10000 15% $100+ ((Emoluments-4000)*0.15)

$10001 – $15000 20% $1000+ ((Emoluments-10000)*0.20)

$15001 - 25% $2000+ ((Emoluments-15000)*0.25)

Thank you
Karen



Jay

I need some help with an IF statement:
Using IF statement, display a message if it’s necessary to reorder. If there are enough units on hand to meet the sales goal, do not display any message.

Books in stock: 5
Sales Goal: 22
Amount sold: 5
Reorder: 5

thanks in advance



Ambika

i m having to coloum one tp and ps ,i want to multiplie qty if tp then 10 or ps then 2 . how to use if formula



Deepak

I have a simple question as a beginner
I am putting a knockout draw for tennis,which contains 8 Teams and I put them in the Excel sheet as below:
Col A col B Col C
5 INDIA
7 PAKISTAN
9 WEST INDIES
11 SOUTH AFRICA
13 NEW ZEALAND
15 U S A
17 ZIMBABWE
19 SRI LANKA
Now if in a match between 5 and 7 , 5 wins.Here I want to advance 5 to column C-6.by simply writing 5 in column C.
Similarly in a match between 17 and 19 , 19 wins and advances< Here also I want to advance 19 to column C-18,by writing 19 in column C.
I do not want to copy paste the advanced team in column 'C"
Is this can me done by some macro or an excel formula inserted in Column C



anuj

How can I use IF statement if my condition is like

If the (User input Field)is =or greater then then
ESI deducted will be 4.75%
and PF Deducted will be 1.75%
and
Employee contribute employer contribute

A/c 1- 12% i.e (User input)*12/100= 780 3.67% i.e (User input)*3.67/100=238.55
A/c 2- – 1.1% i.e (User input)*1.1/100= 71.5
A/10- – 8.33% i.e (User input)*8.33/100=541.45
A/C- 21 – 0.5% i.e (User input)*0.5/100=32.5
A/C- 22 – 0.01% i.e (User input)*0.01/100=0.65



Mike Buck

I am working with projections and I need “If Then” statements in the tax line. I have nevery used “If Then” statements so I am seeking help with this. The cells to reference include the destination cell, the before tax earnings cell. and the tax multiplier cell. If the before tax profit cell is a negative number then the destination cell should be 0. The issue is accumulated loses being subtracted from profits until the taxable number becomes positive. I’m sure that I am not the first to do this so hopefully someone has already solved this.



thabo silvester thapelo

thank you for the explantions.



Himanshu mishra

use operator
=IF(A25<30000,"25%",
IF(A25<40000,"33.3%",
IF(A25<60000,"50%",
IF(A25<80000,"66.6%","no payment"))))



Angela

What IF/THEN statement can I use for this scenario?

One column needs to display one of 4 possible values based on the condition. Example:
-If Code=8, then Show value A1.
-If code= 3 or 4, then show EITHER L1 or M1 (whichever cell is populated)
-If neither L1 or M1 have populated values, then show value in K1

Thank you in advance.



SueH

Thank you for your examples. I was able to use them to put together a formula that will automatically calculate federal withholding for a payroll spreadsheet I created for a client. Lots of parentheses, but it works! Unfortunately I will have to modify it slightly if they hired a married employee but the bones are all there and it calculates correctly. I did not know about the If(And usage and that really helped. :)



Henry

You have simplified what i considered complex, now i understand it, thank you very much



Dangme

Please, I have values in excel I need to do some division with such that the lower value between any pair of values should be the numerator, e.g., On the row, if Cell A is larger than Column Cell B, then B/A; and if cell B in the Row is smaller than Cell D in the Column, then B/D; and if the values are same then the value returned is 1
Thanks



ambarish

It was really helpful for solving my problem. Now my lab personnel can save a lot of time in calculations.

Thanks and regards
ambarish



Faheem

Can you please help me solve this problem.

I need to calculate penalty on late delivery of goods. terms:
0.25% each day within 10 late days
0.5%each day within 20 late days
.75% each day within 30 late days
1% each day exceeding 31 late days.
late delivered goods are equal to the amount given below.
10000USD of goods received on 12th day
20000USD received on 25th day
15000USD received on 31st day
25000USD received on 75th day

please show me the formula in excel for calculation



Jennifer

I have an issue with the IF statement. I am working on a project for a client using Excel. He wants percentages and target numbers. The target number we are looking at is 0, and I need to figure out a formula that will decrease the percentage from 100% by any number over 0. For example: 10 would be 90%, but I don’t know the right formula to make this work properly.



Hugh

This is a good source for persons to understand Excel functions.



Satriyo

Thank you very much for the tutorial! I have been facing so much difficulties to make my sales commission table. With this IF function at last I can have them run automatically!!

Thanks Leslie! :D



agaba

huy ive a problem when computing grades of two different columns which have 2 subjects let say
PHYISCC and CHEM if i get an F in both sbujects i ll get a “retake” and if i get an F in one of the subjects i will get a “probation” and when i get no F in any of the subjects i will get a “pass”
help me formulate a formula for this information using nested if’s…thnx



agaba

ok tried this and it failed help mi and correct me..=IF(AND(C2=”F”,E2=”F”),”REPEAT”,IF(OR(C2=”F”,E2=”F”),”PROBATION”,IF(NOT(C2=”F”,E2=”F”),”PASS”)))



Jay

Scenario Manager…Need Help

——————————————————————————–
I was doing some research on how to create manager scenarios on excel and don’t seem so hard to do it, now my problem is that I not really sure how to use the data that I was given. I’m attaching the Data sheet with the information and here is the question:

Profit Scenario

Murry Lutz owns a small shop, Lutz Motors, that sells and services vintage motorcycles.Murry is curious how his profit will be affected by his sales over the next year.

Project Focus

Murry would like your help creating best, worst, and most-likely scenarios for his motorcycle sales over the next year. Using Scenario Manager, help Murry analyze the information in the data file

Thanks a lot

Lutz Motors
Tax Rate 0.4
Sales (Year 1) 20000
Sales growth 0.2
Price (Year 1) $ 10.00
Cost (Year 1) $ 6.00
Interest Rate 0.15
Cost Growth 0.05
Price Growth 0.03

Year 1 2 3 4 5
Unit Sales
Unit Price
Unit Cost
Revenue
Expenses
Before Tax Profits
Tax
After Tax Profits

Net Present Value



shailendra reshwal

Thank you for your examples. I was able to use them to put together a formula that will automatically calculate federal withholding for a payroll spreadsheet I created for a client.



Chipper

I need to make an if statement for a health program that involves oxygen saturation. I need it to say if A2=96,”Normal” I have tried multiple ways, please tell me what I’m doing wrong. This program helps people using inhalers to increase their lung capacity so that they can discontinue inhalers.



Chipper

Ok only half of my email went through

If
A296 Normal



Saeed Rasheed

All-in one papal Request
I need Microsoft Office 2007 formula
Exempla
(Condition one)
One call (A1) change the Value text so fist check (A1=Colum B) and second check (A1 = Colum B row, and “text” count
Me countifs use but sountifs is not support Colum only under “Text” working
= COUNTIFS (D: D,”=saeed”,C:C,”Delay”)
This formula good working but this problem (“=xyz”,CC:,”Delay”)
So you help me
My E: mail saeedrasheed@live.com
Thanks



Babs

I need to set up three conditions in MS Excel:

If Average of three numbers is 3, Then “High”, If the average is 2, Then “Medium” if the average is 1 then “low”
can anyone please help.



Taher

I’m trying to make this formula works, but failed :(.

I need help on that

=IF(AND(OR(C3=”CPM”,C3=”EPM”,C3=”WPM”),E3=”ALL”),”ALL”,(IF(E3=”ITC”,IF(C3=”CPM”,CI,IF(C3=”EPM”,EI,IF(C3=”WPM”,WI,IF(E3=”Mobily”,IF(C3=”CPM”,CM,IF(C3=”EPM”,EM,IF(C3=”WPM”,WM,IF(E3=”STC”,IF(C3=”CPM”,CS,IF(C3=”EPM”,ES,IF(C3=”WPM”,WS,”"))))))))))))))

Note that this is fior row3 and in row 4 values will be changed to C4/E4..etc

Also I have a defined names values such as CI/EI/WI/CM/EM/WM/CS/ES/WS

Please help me on that :(



prem

I have a time filed, which is having data like 01:00:00 AM to 24:00:00 PM.

I want to extract data based upon a time like 8:00:00 am to 17:00:00 PM with condition if-

If (time between ) 8:00:00 am to 17:00:00 PM it will show “Working Hours”
else
“NOt working Hours”



Jamie

i need to do an if statement that says, IF(D8=10,5%))))))) but it is too long, the 2% is the problem as i need to condense this to say if the quantity is between 5 and 9 then give a 2% discount. as you can see i have written them 5,6,7,8,9, separately which makes it very long, is there a way to write between 5 and 9, thanks a million if you can help!



Jamie

sorry that formula did not come out properly but as you may see i need a part in the formula that can include between 5 and 9 if that makes sense



Doug

This was very helpful. I do have an issue that I’m trying to solve. I have a single name that may pop up in different cells. for example. Doug may show up in cell A1 then in B7. I want to find this name and put it into cell D1. Now the name may not show up in A1 all the time it may only show up in B7 or C8. I need to locate the name and populate in a dirrerent cell. Can you help



Doug

=IF(K169,K16*2%,”")

Jamie here is the formula if you want to give 2% of cell K16 if the range is between 5 – 9



Treasa

I am trying to figure shipping charges, but the cell shows value so I know I am doing something wrong, but don’t know what it is. Please Help
=IF(S12=46,S12101,*15%)



Treasa

=IF(S12=46,S12101,*15%)



Treasa

My posts isn’t showing all of my formula. Let’s try it one more time and see if it will all go through. My apologies
=IF(S12=46,S12101,*15%)



Treasa

SHIPPING 1-45.99 = 8.25; 46-101=18%; 101+= 15%



Micheline

I’m making a monthly budget and I want the balance from one sheet to carry over to the next sheet. I’m not really sure how to do that.



Michael

I need to add up the numbers in a range, which in and of itself is simple enough to do. However, the following conditions must be met in order for the range to be added up properly.
1) The range must be summed from bottom to top.
2) If any of the cells in a corresponding column are equal to zero, the formula must stop adding if it reaches a cell containing a zero.
3) The formula must report back the sum from the bottom to the zero.

Is this even possible to do using IF and OR statements, or should I really being looking into a VBA project?



frank

hello please make it easier to understand thanks



Michael

Frank:
Column A contains a list of number. Each day a new number is added to the bottom of that list. Column B must add today’s number from Column A, plus the previous 6 numbers. If at any point, the formula in Column B reaches a zero in Column A, it must stop adding the numbers in Column A and report to Column C, today’s number plus the preceeding numbers until it reached the zero.
Example: Today=10, Tues=10, Mon=10, Sun=10, Sat=0, Fri=10, Thur=10
The result should be Today+Tues+Mon+Sun and stop adding because it reached a zero on Sat. It should report 40 to Column C

Michael.



Xlsx

Good evening.

My Columns are as follows:

A1-Criminal Name, B1-Crime, C1-Age, D1-Ratings, E1-Punishment

In the punishment column one will get 22 years life imprisonment if he fulfills the following conditions.

1. He must have done Rape OR Murder

AND

2. His Age should be >30

AND
3. His Ratings is >8

It should throw 22 years in the Punishment column only if the above conditions are met otherwise it should be Nil.

More Info on this:

1. Crime column includes Murder, Rape, Robbery, Assault, Kidnap etc
2. Age column ranging from 22-75 years.
3. Ratings column ranging from 1-10 points
4. There are 3400 records we have in the list

How to write an IF AND OR combination formula for this ?

You are welcome for any other info you need on this.

Thank you
mrxlsx



Muhammad Asif Shahzad

if a1 and b1 differance is in minus or credit value then credit or minus value come to h1 other wise come to g1



Muhammad Asif Shahzad

how can apply a formula at this

if a1 and b1 differance is in minus or credit value then credit or minus value come to h1 other wise come to g1



SNOSWAR

XLSX
Try this
=IF(AND(OR(B2=”Rape”,B2=”Murder”),C2>30,D2>8),”22″,”N/A”)
Add more stuff to the OR for the crimes you want.



SNOSWAR

PREM – try this
=IF(AND(A2>$D$1,A2<$E$1),"Working","Not Working")

Place the working hours start and end times in cell D1 and E1 (for example) and not in the formula. These cells and the data cells need to be formatted as 24 hour time.



SNOSWAR



sharon

This is very helpful for me to understand the syntax for AND,OR,NOT conditions.

I didn’t read all the posts, perhaps someone mentioned it already, but it seems wrong to me that a salesman who earns more than $200,000 would get no bonus.
Perhaps the formula should be changed to calculate bonus as max earnings * bonus % for those earning > 200000

e.g. cell C6 =IF(B6>200000,$B$3*$B$1,IF(AND(B6>=100000,B6<=200000),B6*$B$1,"No Bonus"))

Thanks again for this great website!



G.W.Gulawita

This formula is warking propaly but i canot go more than 64 characters please can you Help me

thank
Gulawita

=IF(C3=8,”Babecued Pork Spare Ribs”,IF(C3=11,”Batter Fried Baby Corn”,IF(C3=14,”Batteered Prawns”,IF(C3=4,”Chicken Spring Rolls”,IF(C3=1,”Sesame Prawn Tosast”,IF(C3=6,”Vegetable Sprng Rolls”,IF(C3=15,”Chcen & Sweet Corn Soup L”,IF(C3=16,”Crab & Sweet Corn Soup L”,IF(C3=155,”Hot and Sour L”,IF(C3=102,”Prawn & Cucumber Clear Soup L”,IF(C3=103,”Prawn Tom Yum L”,IF(C3=101,”Sea Food With Bean Curd L”,IF(C3=104,”Sea food Tom Yum L”,IF(C3=89,”Vegetable Hot & Sour Soup L”,IF(C3=156,”Vegetable Sweet Corn Soup L”,IF(C3=133,”Cuttlefish With Worcester Sauce L”,IF(C3=134,”Deep fried Cuttlefish L”,IF(C3=26,”Five Spice Cuttlefish L”,IF(C3=176,”Sail Fish (Koppara)L”,IF(C3=175,”Sea Bass (Modha) L”,IF(C3=35,”Braised Chicken With Cashew Nuts L”,IF(C3=107,”Chicken Fillet With Sate Sauce. “,IF(C3=31,”Chicken In Lemon Sauce L”,IF(C3=32,”Chicken With Black Bean Sauce L”,IF(C3=33,”Chicken with Dry Red Chilli L”,IF(C3=40,”Chicken With Kan Kun L”,IF(C3=41,”Chicken With Sweet/ Hot Sauce L”,IF(C3=34,”Chillie Chicken With Cashew Nuts L”,IF(C3=39,”Crispy Chicken L”,IF(C3=37,”Crispy Chicken With Garlic Sauce L”,IF(C3=128,”Kong Pao Chicken L”,IF(C3=106,”Orange Chicken Fillet L”,IF(C3=116,”Pan Fried Lemon Chicken Fillet L”,IF(C3=139,”Singapore Chillie Chicken L”,IF(C3=36,”Soya Bean Chicken L”,IF(C3=42,”Sweet & Sour Chicken L”,IF(C3=38,”Szechwan Style Chicken L”,IF(C3=49,”Devilled Pork L”,IF(C3=115,”Pork with Aubergine L”,IF(C3=46,”Pork with Black Bean Sauce L”,IF(C3=48,”Pork with Chicken & Pineapple L”,IF(C3=52,”Pork with Dry Red Chillie L”,IF(C3=47,”Pork with Kan Kun L”,IF(C3=45,”Pork with Mushroom & Vegetables L”,IF(C3=53,”Spiced Special Pork L”,IF(C3=51,”Steamed Pork with Hot Sauce L”,IF(C3=160,”Sweet & Sour Pork L”,IF(C3=56,”Beef in Oyster Sauce L”,IF(C3=57,”Beef with Black Bean & Chillie L”,IF(C3=60,”Beef with Cashwnut & Mushrooms L”,IF(C3=59,”Beef with Dry Red Chillie L”,IF(C3=58,”Beef with Kan Kun L”,IF(C3=169,”Devilled Beef L”,IF(3=55,”Sliced Beef with Mushrooms L”,IF(C3=124,”Sliced Beef with Satay Sauce L”,IF(C3=63,”Crab in Hot Butter Sauce L”,IF(C3=170,”Devilled Prawns L”,IF(C3=67,”Diced Prawns with Chillie L”,IF(C3=68,”Fried Prawns with Cucumber L”,IF(C3=109,”Green Beans with dry Prawns & Chillie L”,IF(C3=61,”Layered Crab Foo Yung L”,IF(C3=71,”PRAWNS IN HOT GARLIC SC.”,IF(C3=70,”Prawns With Garlic & Black Bean”,IF(C3=177,”Prawns with Kan Kun L”,IF(C3=66,”Singapore Chillie Crab L”,)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))



Reena

i how to u like



Reena

How did you get on



Nico

Hi Michael.
The easiness of the solution is depending on whether or not the numbers allocated to the days vary. (e.g. does Saturday always have the value 0 and the other days 10).

Most likely you are easiest of with a bit of VBA-code (a macro thus).

With the macro you can identify which weekday is today. based upon that you can “count” how many days are left till the day with zero value.
I would in that situation go with the “Select Case” structure instead of the “If-Then” one.

Of course the macro becomes more complicated when the values of the days fluctuate and the zero value day also varies.

I hope that this helps you a bit on the right track.

Cheers,

Nico.



Josh

People wondering for the Max Commission that should be given to those Salesmen who exceeded their 200k targets, formula is as below:

=IF(AND(B9>$B$3,B9$B$4,$B$4*B$2,”No Bonus”))



Pete Sheehan

I’m trying to use 2 IF AND statements as shown below. I keep getting a VALUE error. =IF(AND(B7=5,$B$8=”Downstate”),’Gas LRAC tab’!C57,0), IF(AND(B7=5,$B$8=”Upstate”),’Gas LRAC tab’!C58,0)

Can you NOT use 2 IF AND statements? Thanks



ravi

what is the difference between these two formulas 1.=IF((A39+B39)4,65,IF((A39+B39)>9,50,IF((A39+B39)>15,81,))))

2.=IF((A39+B39)4,(A39+B39)9,(A39+B39)<=14),50,75)))

IF A1+B1 4 but 9 but 15, return $75



ravi

IF A1+B1 4 but 9 but 15, return $75



Rohit

This is formula which is working properly but i want that result should be between -15 to +20 can any one help me.

=IF(S16>2.5,(S16*S19),IF(S16<-2.5,(S20*S16)))



PAVAN

=IF(B52>80000,”66.6%”, IF(B52>=60000,”50%”, IF(B52>=40000,”33.3%”, IF(B52>=30000,”25%”,”NOPAY”))))

or

IF(AND(B52>=30000,B52=40000,B52=60000,B52=80000,”66.6%”,”NOPAYMENTS”))))



Eshak

The explanation was greatly usefull and I was sucessfull in the use of “if” with “and”



Frank

it helped me alot. but i have another thing here

“15,000 if 2005 sales is greater than 25,000; else 7,500

and the 2005 sales is in E2 cell. so how do i make this in Excel language? ;)

if you really work this out it will be a pleasure :)



AX$

My Columns are as follows:
C4=Location, D4=Designation, E4=Cost.

The value in the cost columns depend on designation and location..i.e. onsite and offshore.

There are five designation available-salesman, marketting, operation, manager and PMO.

Cost for onsite are 2100, 2200, 2300, 2400, 2500 for salesman, marketting, operation, manager and PMO respectively.

Cost for offshore are 500,700,800,850,950 for salesman, marketting, operation, manager and PMO respectively.

So there are total 10 combination for which single logic is required.

How to write an IF AND OR combination formula for this?



Brad Peterson

You have to go nuts reading all these email rquests. Best of luck to you.



Chris Henderson

I agree with Brad! Thanks for the insight. Helped me with my problem. Easily understood!! Appreciated!



Ammad Khan

Hey fellows, how can I write this table in Excel in one Cell..
condition is if Cell B4 price is more then 1 to 1.99 then result should display 0.25, if price is 2 to 9.99 then result should disply 0.45



obay

i like your convrzation and your idias



Muhammad Shahbaz

Sir, I need help about “If function”, I want to make status pass and fail and if pass must be change its color green and fail red

thankyou



Satpal Singh

Collumn A shows DOB while B shows only age
as
A1
04 March 1984
A2
09 September 1980
B3
30
B4
34
Now I have to calculate the Age in years for all if Present DOB in A, if not present in A then show for B What formula should I use



allan

thank you for the write up, have just used the IF OR statement on a wee spreadsheet



Yogesh Dongare

good one



Kevin Evan

Any help on this would be appreciated. I am trying to calculate the commission based on the value of C1. Any help would be appreciated.
C1=$15
C2=$1,000,000
C5=$0
D5=.75%
B6=$.01
C6=$15
D6=2.00%
B7=$15.01
C7=$50
D7=2.50
B8=$50.01
D8=3.00%

=IF(C1=B6,C6=B7,C1=B8,D8*C2,0)



Trish

Can someone please help me out with this formula? In English I’m trying to say If the words “CANCEL”, “Cancel – Non-Pay”, or “Cancel-Pend Rel/NOC” are in column A, then count as 1, if not 0..

This is what I thought was right…Apparently not..

=IF(or(A:A=”CANCEL”,”Cancel – Non-Pay”,”Cancel-Pend Rel/NOC”,1,0))

What did I do wrong?



Kevin Evan

Any help on this would be appreciated. I am trying to calculate the commission based on the value of C1.
C1=$15
C2=$1,000,000
C5=$0
D5=.75%
B6=$.01
C6=$15
D6=2.00%
B7=$15.01
C7=$50
D7=2.50
B8=$50.01
D8=3.00%
=IF(C1=B6,C6=B7,C1=B8,D8*C2,0)



Kevin Evan

For some reason the formula I post changes when I post it. =IF(C1=B6,C6=B7,C1=B8,D8*C2,0)



Mero

if i have Amount and want to applicable formula for this amount as below mention

for – frist 25000 ded only 10%
– scand 15000 ded 15 %
– third 205000 ded 20 %
– above 245000 ded 25 %



Prashant

=IF(AND(K5>=4,L5>=4,M5>=2,N5>=2),”DVD”,0)

i want to know if k5=8,l5=8,m5=4,n5=4 then want to get result “DVD 2″

i mean get result multiplier with slab



kyle

Great Article
Thank you !



kamalesh

I want to redraw the grid lines in excel which are erased because of color which was applied. Help me in this



Ari

Any one able to help with the following:

=IF(AND(C91%),”",IF(AND(C9<=7,G9<15%),30%,IF(AND(C9=15%),40%,IF(AND(C9>7,G97,G9>=50%),50%)))))

I need also the cell to show the 30% when C9>7,G9>=15% BUT 7, G9



George

I need to combine a formula:
34 – 41 = Tier 1
26 – 33 = Tier 2
18 – 25 = Tier 3
11 – 17 = Tier 4
0 – 10 = Tier 5
Looks like a “or” formula.
When a value between 0-41 is entered, the “Tier #” will populate.
Does anyone have a formula for this?
Thanks,



shenagay berlin

i just don’t get the if statement



shenagay berlin

i still just don’t get the if statement.



guest

=IF(A1<11,"Tier 5",IF(A1<18,"Tier 4",IF(A1<26,"Tier 3",IF(A1<34,"Tier 2", "Tier 1"))))



Nina

very useful, thanks



Sagar Mudi

Helping



Chinna Charles

Hi i need help creating a formula i have three cells named as Marks1,Marks2,Marks3 and if the marks are above 60 in each cell the result should appear as “CLEARED” and if any of the cell has less marks 60 then the result chould appear as “NOT CLEARED”

I am unable to user IF & AND condition together please help and suggegst



fateh

hi please you help me for this which i use formula for one word write & auto fill in next cell detail for this person like example a1- name, b1- age, c1-qualification d1- address, e1- contact no. …. so please tell me



Cindy T.

Hi, Using SmartSheet and having trouble because it only accepts certain functions.
I am using the following calc successfully now I need to change this to an IF statement, can you help?
=INDEX(Sheet2!$B:$B,MATCH(E2:E400,Sheet2!$A:$A,0))



Amit

What is the mistake in this-
=IF(C8=B8, “True”, “False”, IF(ISBLANK(C8)=TRUE, “Blank”))



KPR

thanks for your cooperation I really appreciate it



Kathryn Laidlaw

I just wanted to say thank you for your article. I was struggling to put together a very formula which, in the end, included 11 nested IF formulae, 5 ORs and 4 ANDs. I was struggling for hours until I came across your advice. Thank you!



John

Hi

I’m trying to write a simple if formula. That basically returns three different results.

If K3 has no data in cell, return “0″ or leave blank
If K3 has data greater than 0, return “1″. As an example data will be $10
If K3 has data less than 0, return “-1″. As an example data will be -$10

=IF(K33>0,1,IF(K33<0,-1,0))
Works fine to return conditions 2 and 3, but will not work to return "0" or a blank cell.

Any help would be greatly appreciated.
John



Saleem

Who can Help me??

A1=A AND B1=ADULT,”500″
A1=A AND B1=CHILD,”375″
A1=A AND B1=INAFNT,”250″
A1=B AND B1=ADULT,”500″
A1=B AND B1=CHILD,”375″
A1=B AND B1=INAFNT,”250″
A1=C AND B1=ADULT,”400″
A1=C AND B1=CHILD,”300″
A1=C AND B1=INAFNT,”200″

Who can Help me??



Saleem

“INAFNT”
Typing Mistake “Infant”



Julius

Hi John,maybe this is what you are looking for

=IF(K3=0,”",IF(K3>0,”1″,IF(K3<0,"-1",0)))



Julius

Hi John,this is another short version of the formula

=IF(K3>0,”1″,IF(K3<0,"-1",""))



swapnil

I have one query for Nested If using AND operator. Pasting my formula as below:

=IF(AND(E12474=ZP3,G12474=ZR3,H12474=ZS3,I12474=ZT3),IF(L12474=ZU3,ZS19,IF(L12474=ZU4,ZS20,IF(L12474=ZU5,ZS21,IF(L12474=ZU6,ZS22,IF(L12474=ZU7,ZS23))))),IF(I12474=ZT4,IF(L12474=ZU3,ZP29,IF(L12474=ZU4,ZP30,IF(L12474=ZU5,ZP31,IF(L12474=ZU6,ZP32,IF(L12474=ZU7,ZP33))))),IF(H12474=ZS4,IF(L12474=ZU3,ZQ77,IF(L12474=ZU4,ZQ78,IF(L12474=ZU5,ZQ79,IF(L12474=ZU6,ZQ80,IF(L12474=ZU7,ZQ81))))),IF(OR(H12474=ZS5,H12474=ZS6,H12474=ZS12,H12474=ZS13,H12474=ZS14),IF(L2474=ZU3,ZS40,IF(L12474=ZU4,ZS41,IF(L12474=ZU5,ZS42,IF(L12474=ZU6,ZS43,IF(L12474=ZU7,ZS44)))))))))

I want to come out directly whenever my first condition will fall. first condition is “IF(AND(E12474=ZP3,G12474=ZR3,H12474=ZS3,I12474=ZT3)”. Please help in this regards.



PATIL BHAGWAT NATHU

I want correct formula for following:

Following cells contains:
A1-1201 and above
A2-976 to 1200
A3-751 to 975
A4-601 to 750
A5-600 and above

I want a result in cell c3 of following
1. if b2 is >= 1200 = “A+”
2. if b2 is >= 976 = “A”
3. if b2 is >= 751 = “B”
4. if b2 is >=601 = “C”
5. if b2 is >>600 = “D”
how I can use If and nested formula get above result. Please help.



Julius

@Patil

Don’t really understand what you want to say on the upper part of your question, but if you want the formula for this part :

1. if b2 is >= 1200 = “A+”
2. if b2 is >= 976 = “A”
3. if b2 is >= 751 = “B”
4. if b2 is >=601 = “C”
5. if b2 is >>600 = “D”

Formula :

=IF(B2>=1200,”A+”,IF(B2>=976,”A”,IF(B2>=751,”B”,IF(B2>=601,”C”,”D”))))



Susan

I want help PLEASE

I am a teacher. All the formulas is working as it should but in the last column you have to give a symbol.

The symbols is as follow

A for 80-100%
B for 70-79%
C for 60-69%
D for 50-59%
E for 40-49%
F for 30-39%
G for 0-29%

Averages is in column AB. Lets say line 1. Thus AB1

Please help me to set criterias.

Thanx



David

I am trying to create a statement where i can have many variables to flex data… I have included a switch to change the varibales. I want to say “if my switch equals 1 multiply the cell by 10, if my switch is on 2 multiply the cell by 20, if the switch is on 3 multiply my cell by 30 etc…there are 5 levels to be included..what is the best type of statement to use for this? Thanks



Kaneil

I have to do a nested IF statement in Excel and it goes like this……I need it pull information from 2 different cells and give 5 different answers. It needs to say all legal Level 1(which is in Cell L2) applicants and for each applicants state (which is in Cell C2) it must use the following rules “IF the applicant lives in Florida, denote with “FLX”, GA with “GAX”, SC with “SCX” and applicants in all other states with “REJECTED”…………Please help!!!!



mukund

Hello S/M,

Thanks a lot, it helped me big way!!!



Chris Hart

I am trying to write a formula to pay a percentage up to a certain amount and then increase the percentage once a rep exceeds that amount. Ex: Rep brings in $2,000.

Pay 50% for the first $600
Pay 60% for the next $600
Pay 70% for the next $600
Pay 80% for everything over $1,800.

A1 – $600
A2 – $1,200
A3 – $1,800
A4 – >$1,800 (??)
B1 – 50%
B2 – 60%
B3 – 70%
B4 – 80%



Eubert Vushoma

@ Chris
u don’t have to enter all the values in different cells as you proposed above. u may capture your information as column A- Rep names, Column B, Amount earned by each rep, C pay percent, D pay value.

in C enter the formula
=IF(B2<=600,50%,IF(AND(B2=601),60%,IF(AND(B2=1201),70%,IF(B2>=1800,80%,”"))))

this should work



Eubert Vushoma

=IF(B2<=600,50%,IF(AND(B2=601),60%,IF(AND(B2=1201),70%,IF(B2>=1800,80%,”"))))



Andrew

column A- Rep names, Column B, Amount earned by each rep, C pay value 1800, G Total Commission
Formula in C = =IF(B10<600,B10*50%,600*50%)
Formula in D = =IF(B11-600<=600,(B11-600)*60%,600*60%)

Formula in E = =IF(B6-1200<=600,(B6-1200)*70%,600*70%)

Formula in F ==IF(B8-1800<=600,(B8-1800)*80%,(B8-1800)*80%)

Formula in G = =SUM(C2:F2)

Beware of negative amounts when pasting the formula



Sports Direct Vouchers

If possible, as you obtain know-how, can you thoughts altering your website with more information?



Michael Kinney

IF(B8=”Double”,IF(G12<300,(G10*(G12)*3.14*1.05/144),(300*G10*3.14*1.05/144)))
how can I zero this out



Aries

Hi, I have some complex issue:

Value A, B C, and D

A = B or B = D, true

AND

A = C or C = D, true

Else false.

How do I craft this formula? I tried but not working:

if(AND(OR(A=B,B=D),OR(A=C,C=D)),”True”,”False”

Any help much appreciated.



Leslie

if A is in C2, B in C3, etc.. you could do:

=IF(IF(OR(C2=C4,C4=C5),1,0)+IF(OR(C2=C3,C3=C5),1,0)=2,”True”,”False”)



lary

How can you write the formula for 50 if 5



lary

50 for 2,120>5



lary

50 for 2, 120 for >5



Alice

need help on an excel formula.

If I want to formulate a formula to shows as below:

if cell C1 = A and less than or equal to 10am = YES
if cell C1 = A and greater than 10am = No
if cell C1 = B and less than or equal to 2pm = Yes
if Cell C1 = B and greater than 2pm = No

Cna i write this formula into a cell to show the correct answer that I want?



Adil

I Want to learn IF Function’s



Vinesh

if a1 is todays date and a6 is the due date i want a7 to come up with “paid” if paid on or before due date and “due” if passed due date – See more at: http://fiveminutelessons.com/learn-microsoft-excel/using-multiple-if-statements-excel#comment-331



SARA

What is the Font Color Formula in IF condition. without VB.
If A1 is greater than 5 then font color should be green other wise red.How to use this formula in wingdings.



redpaddle8617.xtgem.com

These hotels are perched around the edge of some harbour, London
bridges or the rambling and splendid River Thames,
through which the location of London is acknowledged for, being the ideal milieu against which these plush
hotels are sited. Rajasthan, known in the around world for its brutal and self-important race of people,
can be known throughout the globe for the highly considerate population.
From specially decorated resort rooms to planned trips for some with the
most romantic locales, the travel companies leave no stone unturned to
create your honeymoon a memorable one.



raju ghimire

could you please send me the formulae of IF, AND, OR of excel . I would be really appreciate for your cooperation.



raju ghimire, raju.ghimire@aepc.gov.np

Appreciate for Excel (IF, AND OR) formula.



Amberlee

I am trying to use conditional formatting and am having issues where there needs to be multipul rules.

I want is so that if column A is older then today and column B is equal to In Progress or Blank (i was using cells C2 and C3 to represent these) that the column A cell will turn pink.

Can you please assist me with a forumar as nothing i do seems to be working.



Sarah

help!
I need an IF/AND formula, for a sales floor.
they’re selling subscriptions. they have ladders to hit. if they sell 50+ multi year subscriptions in a month, they hit a different ladder than if they sell less than 50 multi-years, and however many single year subscriptions.
sarah.wittenauerATamgh.us please help!



Dawn

Can I do this?: If the value in cell I2 = either “this” or “that” and the value in cell H2 is <10, return a value of 10. (if not, no value returned.) I started with: =IF(OR(I2="C/S A", I2="AG C/S A",H2<10),10,0) but even if the cells don't contain the quoted titles, it returns 10 if the value is H2 is less than 10. I've tried putting "AND" before the H2 part, but it errors out every way I try. I appreciated any help!! Thank you.



Delton

I am currently trying to put together a formula that will calculate the time an aircraft is airborne. I currently write the takeoff time, the land time, and then find the hrs/mins the aircraft was up.
Example:
Takeoff ———————–Landing
01:00………………………………………7:51
The time must be broken down into minutes as shown below:
01-02min = .0
03-08min = .1
09-14min = .2
15-20min = .3
21-26min = .4
27-33min = .5
34-39min = .6
40-45min = .7
46-51min = .8
52-57min = .9
58-60min = 1.0

I know just by looking at it, it’s easy math in the head…6hrs 51min or 6.8<<<(I must use this format) but the formula will help me with the rest of the calculations I need to work out, thus making the whole process faster for me.

Can you help?
Thanks Delton



Brandy

Can someone help me with this formula? Here is what I want it to do….

If the value in column T=1 then multiple by the value in column T by .027 but if it equals 2 then multiple it by .037+.15



Tammy

Can someone help me with a formula for a if and or stament? this what I need….

If a value in column E=CRQ and the value in column K is Blank, “Potter” If a value in column E=CRQ and the value in column K=S or Q, “Jim”



Md. Shohel Rana

Can anybody help me to create a formula under the following criteria?

House Rent will be-of basic
1) up-to Tk.5000 Basic*55% but not less than Tk. 2500.
2) Tk. 5001-10800 Basic*50% but not less than Tk. 2800.
3) Tk. 10801-21600 Basic*45% but not less than Tk.5400.
4) Over Tk. 21600 Basic*40% but not less than Tk. 9700.



Pamela

I am trying diligently to create a formula that will help me figure out how much money I will need to spend by month. My spread sheet is multiple tabs and lines. I am trying to use the expiration dates of medications as the starting point. If the date is this then multiply the price by the quantity. So that I can quickly look at one page and know how to budget. Am I asking Excel to do too much? I’m a medic not a computer person, this confuses me.



Suresh

Can any one suggest a formula for below results. i want display if all cell marked as pass then Pass should display in the Final Result, if any one result is Fail then Final result should display Fail

Pass
Pass
Pass
Fail
Pass
Pass
Final result = Pass or Fail

Please help



Michelle Gee

Fabulous explanation! This is the first site I could find that broke it out in terms I could understand. Much appreciated for sharing!



Jose Antonio Garcia

Excellent explanation. After one hour fighting the formula, I decided to google it and found this excellent explanation. It solve my challengue in five minutes. Thank you very much.



Ken Miller

I am trying to create an Invoice in Excel 2013 that when a specific product is selected in the “Product Description” cell the unit price of that product will show up automatically in the “Unit Price” cell next to it. I have 50 different Product Discriptions and have them in a Drop Down List. I think I should be using a nested IF Statement but I can’t seem to get it to work.

Please help. Thank you.



bob

bob



bob

fack off



Tim

Very helpful.
Thank you.



7Years later

Dear Sir,

Many Thanks.

Regards,
7Years later



Ross Keen

Ken Miller:

An IF statement would be a very complex way of doing this.

VLOOKUP would be the option you require.



ALBOTRONIC

Suresh:

=IF(COUNTIF(I17:I22, “fail”)>0, “Fail”, “Pass”)

…where I17:I22 is the range of the Pass/Fail cells.



Ken

How would you create a formula that displays the number “1″ x number of times along a range over x number of times.

E.g if A1=7 and I wanted to display the number 1 of 7 consecutive cells from B1to H1.



mehul patel

extramely help full



Sultan Agha

Very good – real helped



Sultan Agha

very good – really helped



Rita

=IF(B6=”",”UN”,IF(C6=”",”Plan”,IF(AND(C6=”",B6=”"),”Free”,”Occ”)))

what is wrong in this formaula? please help



Deborah

I am seeking assistance with a nested IF formula. I have two condition qualifiers and four different combinations in order to deliver the result. First Cell C14 represents total sales and second Cell C15 represents total units. The three results are determine by 1) C14>=20000000 AND C15>=30; 2) C14>=12000000 AND C15>=25; 3)C14>=6500000 AND C15>=20; 4)C14>=2500000 AND C15>=15. Here’s the nest =IF I created:

=IF(AND(C14>=20000000, C15>=30),”Platinum”, IF(AND(C14>=12000000, C15>=25),”Gold”,IF(AND(C14>=6500000, C15>=20),”Silver”,IF(AND(>=2,500000, C15>=15),”Bronze”,”Does Not Qualify”

I received an error of:

When the first character is an (=) or (-) sign, Excel thinks it’s a formula. To get around this type an apostrophe.

This doesn’t work. Can you assist and provide me with the correct =IF statement? Many thanks.



pritam

i have make leave records in excel, now i want that when ever i put el or hpl in a cell (a5) then the date mentioned at (a4) should come to the another cell where i want that formula.



RAMY ELSHERBINY

=IF(B14>=0.7*B5,(B14*(0.8%)),IF(B14>=0.8*B5,(B14*(1%)),IF(B14>=0.9*B5,(B14*(1.3%)),IF(B14=B5,(B14*(1.5%)),B14*0))))

why the output it is came with first condtion only b14*8%



Thomas

Hello, I am looking for a formula:
I have a dataset in which a column shows a digit ranging between 1 and 7.
In another column I would like to apply a formula such that 1 would equal Sunday, 2 would equal Monday, etc.
I’m a bit stuck with the IF, AND formulas.
Could anyone help?
Tx



Jill

Why won’t this work? =IF(AND(B27>=1800000, E42>=60%, E41=100%), B27*0.5% (AND(B27>=1800000, E42>=60%, E41=125%), B27*1.0% (AND(B27>=1800000, E42>=60%, E41=150%), B27*1.5% (AND(B27>=1800000, E42>=60%, E41=200%), B27*2.5%, ” “)



Charisse

Good Morning!

Is there a way to do the following in Excel:

If …… AND ……. then

1

else

0

end

The syntax that I am using thus far for the first two conditions is as follows:

=IF(AND(AND(L17$S$1),NOT(L17=”")),1,0)

Is there a way to include OR in this statment.

The end result should be if the first condition (above) is met then a “1″ should appear in the cell otherwise a “0″ should appear in the cell.

Thanking You In Advance!



suvarna

i have values like :-
-230,-30,0, and 23,50,135
I want to put a formula like
If S 1 and < = 50 then Less than 51



keith

I need some help.
I want to use an if statement for the following:
Quantity (d1)
Pk Qty (E1)
Total (F1)
UOM (AA1)

Formala Help to be entered in F1:
If pk qty = 0, then enter the value for d1 in F1, If Pk quantity is>0, then return D1*E1 unless UOM =FT, then enter D1



Amjad Khan

I have a confusion in formula of MS Excel. I want fail and pass formula and the same cell when i enter A mean absent will show fail.



Amajd Khan

Dear,

I need help in excell formula.i want fail and pass formula and in the same formula when i entered A means absent in the same cell where fail pass formula is admitted then shows fail.



Prakash

=IF(AND(D1>=30000,D1=40000,D1=60000,D180000,”66.6%”,”no payment”))))



Kris Hembree

How do I continue a string of If(and( arguments only checking/changing 1 criteria to check for that cell

=if(AND(d2=”air import”,v2>2)’”yes”,’”no”

I want to only change the “Air Import” each time to check “Air Export”, “Ocean Import” etc…. and return then Yes or No based on the value of the =>#



hinh sec

Xem bo suu tap hinh sex khung nhat hien nay voi
hon hon 1 trieu hinh sex dep khong che hot nhat 2014.
Tham khao them voi cac loai hinh sex,hinh sec,hinh sec cuc
dep se lam ban thich thu



Ken

I’d like to use an If statement to return a “True” or ‘False” if any dates in column A or B are greater than or equal to 1/1/14. I’d like to also use the formula if there are dates in column C as well.

Order A B
1 4/6/2012 9/13/2012
2 6/19/2013 10/22/2014
3 9/12/2013 12/7/2013
4 5/2/2013 1/13/2014



Sabine

I’M SO 2008.
YOU’RE SO 2000 AND LATE.



Margaret

My problem has to do with using a multiple of the number 11. B7, B8, B9 & B10 can be $11, $22, $33 or $44. These are added in B11. B13 takes 6.25 from each $11 in cells B7-10. So if B7=22, B8=11, B9=44, & B10=11, the total in B13 should be $50. Since the amounts that are paid vary per week, I need a formula to determine the amount for B13.
I can get it for just $11 in each cell, but not the other amounts.
=IF(AND(B$7=11,B$8=11,B$9=11,B$10=11),C2*4)



Teresa

I am trying to write a and/if statement to read if b2=”manager” and d2=”b” and h2<or="740",then give me "under"

Is there some help someone could provide me with this?



Gus

I need assistance, please.
I’m comparing dates in two columns in Column C:
A2 is the Previous Data Date
B2 is the Current Data Date
Here are the scenarios:
If A2 and B2 are blank, then C2 remains blank
If A2 > B2, then C2 = B2
If A2 < B2, then C2 = A2



RAJENDRA JAIN

i have one question.i want help….

how to create formula in excel.. there are three “LOCAL” REMOTE” AND “NETWORK” AND RESPECTIVE VALUE IS “1″,”2″,”3″ .SUPPOSE A PERSON SELECT LOCAL THEN VALUE SHOULD COMES AUTOMATICALY IN EXCEL SHEET =1 AND SIMILARLY … FOR OTHER.

PLS GUIDE.



Jennifer Hart

I’m trying to create the following nested If formula. I can create the two halves separately but I cannot figure out how to combine them. I am creating a table that calculates percentages for shipped & unshipped items. The final calculation is for the average total percentage. Sometimes one of the 1st two is a zero. My calculations needs to do this:
IF percentage A is <=0 enter percentage B,
IF Percentage B is <=0 enter percentage A
If both A&B are greater than 0, multiply A*B and divide by 2
THIS is the 1st part -,(IF((OR(F13<=0,K130,K13>0)),((F13+K13)/2)))

How do I combine the two parts? I MUST have them in the same cell. I hope this makes sense.

Thanks!



Jennifer Hart

Actually that was the 2nd part.

The 1st part is like this:
(IF((OR(F13<=0,K13<=0)),K13,F13))



Kirit

i have type d or e or f… and auto generate value d =20, e=18 any or type si1 generate 10



google.com

Hi there! Would you mind if I share your blog with my myspace group?
There’s a lot of folks that I think would really enjoy your
content. Please let me know. Many thanks



bride

Hello there! This is my first comment here so I just wanted to give a quick shout out and tell you
I truly enjoy reading your articles. Can you suggest any
other blogs/websites/forums that deal with the same topics?
Many thanks!



Emma

Please can someone advise me why this/these formulae are not working?

=SUM(IF(AND(Data!B:B=”",Data!D:D=”03″),Data!P:P))

=IF(AND(Data!B:B=”",Data!D:D=”03″),sum(Data!P:P))



Dana

I have a head-scratcher here…

Sales $0 – $400K = 8% Commission
Sales $400K – 700K = 10% Commission
Sales $700K – $1M = 12% Commission
Sales $1M + = 15% Commission

I enter their total sales on a monthly basis – I’m having a hard time calculating computer 8% up to this total sales amount, and then the remaining dollars carry over to 10% commmission, etc.

Any help/guidance you can provide would be appreciated.



Goutam Paul

Sir,
I want to calculate ESI for my our staff. So I tried it to calculate thru Excel with the following formula
=IF(AND(F1>3000(IF(OR(F1>8000,V1>101),1,0)
Logic’s are as below
1. The Value if F1 varies from 2500 to 16500, The calculated value lies between 3500 to 15001
2. The value in V1 varies from 50 to 500, but the calculated value will be less hen 101
3. If condition 1 & 2 are true then the result will show “one” else “zero”

May any one can help me
With regards,
Goutam Paul



Nadine

Thank you!!



Lynette

This is really helpfull. Thankyou.



Shaun

I need help!!! I am doing up a spread sheet with darts scores and averages etc. We record the highest pegout for each player. Can anyone tell me what formula and possibly how to make it so my spread sheet will show the players name with the highest peg each week.



xan tay ao

I don’t even know how I ended up here, but I thought this post was good.
I do not know who you are but definitely you are going to
a famous blogger if you aren’t already ;) Cheers!



omon

Thanks a bunch, u av been helpful but I still need ur assistance on this.
I av 30 agents, to get bonus on each based on performance on a monthly bases, transaction count must be 25 and above and transaction value must be N50,000 and above. Now my question, aw do I know on which agent I get a bonus if some meet both conditions or one of the conditions.



SAMSHER

Thank you!!



Andre M

Thank you this has been really helpful. However, I have problem that seems so simple, but I can’t wrap my head around it. I have a large database, and i have two separate columns that unfortunately need to stay that way, which together are defining the time of an incident: Column A consists of a number “1″ to “12″, and Column B consists of cells with either “PM” or “AM”. I need a separate column that gives me a “1″ for anything that happened between 8 AM and 4 PM and a “0″ for all else. The problem obviously is the “12″ “PM” rows. That’s all I have so far:

=IF(AND(A1>=8, A1=1, A1<=4),IF(B1="PM",1,0),0)

and:

=IF(AND(A1=12),IF(B1="PM",1,0),0)

but I have no idea how to combine all of these in one statement at the same time. Can anybody help?



Andre M

Ignore the above typos with missing text, here again:

Thank you this has been really helpful. However, I have problem that seems so simple, but I can’t wrap my head around it. I have a large database, and i have two separate columns that unfortunately need to stay that way, which together are defining the time of an incident: Column A consists of a number “1″ to “12″, and Column B consists of cells with either “PM” or “AM”. I need a separate column that gives me a “1″ for anything that happened between 8 AM and 4 PM and a “0″ for all else. The problem obviously is the “12″ “PM” rows. That’s all I have so far:

=IF(AND(A1>=8, A1=1, A1<=4),IF(B1="PM",1,0),0)

and:

=IF(AND(A1=12),IF(B1="PM",1,0),0)

but I have no idea how to combine all of these in one statement at the same time. Can anybody help?



bullion vault

Actually when someone doesn’t know afterward its up to other people that they
will assist, so here it occurs.



Comments

Please Leave a Comment!





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