[...] 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. [...]

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!

Glad to be of help!

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.

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!

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

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

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 :(

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.

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

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?

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

Thanks, very helpful and well written

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

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

so difficult this stuff ha!

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

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

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!

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

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

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 :)

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

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

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.

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

Thanks for your help, Ricemutt! :)

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

Sorry,

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

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

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).

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.

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?

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 ..

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

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.

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!

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.

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

=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

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

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.

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

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

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…

@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.

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

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

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!

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.

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)

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.

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

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

thankssssssssss for all

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

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

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!

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

=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

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

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.

Thank you. This was so helpful!!

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?

It’s fantastic site to learn excel.

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.

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”,”")

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

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

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

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.

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

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

Thanks for your help.

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

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…

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”,”")

@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?

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

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!

@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.

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

@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.

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

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,

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

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???????

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?

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.

Keep up the good work!

thanks for this. it was really a great help!

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).

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.

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

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

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.

@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))

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

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!

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.

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

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.

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

=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”)

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”

nice try….

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

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″)

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

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

This is great description. Thank you

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.

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.

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

Thanks so much! very helpful and well written!

[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!

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

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.

Hi

Was very helpful

Thanks

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.

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.

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 )

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

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.

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))))

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

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

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

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!!!!

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?

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

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.

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!

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

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

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?

@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

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

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?

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)

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

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

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

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

Thanks for a quick refresher.

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?

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.

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?

Dear Friend,

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

thanx/mahendran

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

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?

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

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

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.

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

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

@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.

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)

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

Thanks Leslie, appreciate it.

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

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

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.

This is great! Thanks for the help!

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!

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”,” “)

i learn excel forumula
sum
if
sumif(
and or
etc

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.

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

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

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

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

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")

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

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.

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.

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))

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!!! :)

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?

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

@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.

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

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

Thank You very much for your help

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

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

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…

Thank you!

Hai ,

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

Thanks for understandin.
Akram

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

thanks for your time and efforts. very useful.

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?

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

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

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!

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 )

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

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!

hi
pls send me excel formuls step by step
use

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

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

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

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

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)))

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!

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.

Is there any easier way?

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.

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

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

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”)))))))

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

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.

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

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….!!!!

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

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!!

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

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?

this is so helpuful

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

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”,” “))))

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”))))

Hi prafulla

pls find the solution

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

hi, jay

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

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.

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

HI JAY

pls find the solution

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

HI Sukh,

Pls find the solution

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

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.

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!

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

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

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"))))))

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"))))))"

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"))))))

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

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.

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”)))))

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.

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

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

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

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

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.

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!!

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

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”

hi Yazan,

Pls find the solution

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

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"))))))

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"))))))

HI Khurram inam,

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

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”))))))

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.

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

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”)))))

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

RON,

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

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

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

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

thanks

Ron and Khurram inam

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

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

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

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!!!

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

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????????????????

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.

=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.

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?

=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

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

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

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.

how much “if” condition use in one formula?

@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.

@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.

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.

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

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

Thanks

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))))

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

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.

thanks this site is really helpful for learnning perpose…

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)))))))

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.

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

Jessica,

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

Should do the trick :-)

@Jessica: I think what you need is

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

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

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,”")

=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

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

=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

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

IT was just answer paper to my questions

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

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!!

@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….

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.

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

thanks for learn me if condition

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)

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″,)

whats wrong with this function:

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

@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

@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!

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

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

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!

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

I need to classify companies as follows -

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

Can I use the IF function?

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

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”

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.

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

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())

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

Thank you so !

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?

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.

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″.

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

OMG… AND()

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

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

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

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?

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

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?

=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

=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"

plz learn me exsel 2007

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.

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

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!

Well explained, Thanks.

article was well written. it did not answer what i needed. I’m looling to build an if statement that returns a color versus a number or word. the scheme is similar to the traffic light system. can you help me with that?

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.

Great information! I’ve been looking for something like this for a while now. Thanks!

tnx sir now my prblm is solve tnx a lot

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

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.

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 115% AND Y1 is greater than R1, then return “highpoint”

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”

SEMPEL EXAMPLE FOR (IF) FUNCATION

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.

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!

I am doing a schedule for my staff.
we are open till `10pm, if an employee is working 5pm-10pm i wish it to show “CL”
working 5pm to before 10pm, to show “PHASE”
I will share the worksheets if that helps.

Hi, If statement example was terrific!, would it be possible for you to let me know the logic of having paranthesis and how can i know when and where should i used to get the desired reslults

You made some good points there. I did a search on the topic and found most people will agree with your blog. Thanks

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 trying to have my formula project maintenance schedule projected dates of mining equiptment for the next six months. i want it look at two dates. if they match it is to then look in another column for what work is scheduled. but when the two dates match the logic test says “false”. why is it the two matching dates do not reconize each other

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.

thanks

thanks for helping me

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

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?

thanks & it’s realy very helpful

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.

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?

I am trying to make a formula if each cell from b12 to b42 is <=9:00 and if k8=0 true=2000.00, false=0.00. How do i do this. Please help

Forgot to add one condition, formula should be if each cell from b12 to b42 is <=9:00 and if number of 0:00 from b12 to b42 is <= 4 and if k8=0 then true=2000.00, false=0.00. How do i do this. Please help

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.

Excel nerds meeting=)

=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! :)

Good article…I will use some of these interesting principles myself…more great info please…

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"

Cannot appreciate this walk through enough. I have used it countless times. Thanks a million for taking the time to write this out!

This is a very helpful post, i hope this really helps me to complete my project.

hi , new here , i would like to know if anyone can help me with this .
i’m trying to put this in excel but i can’t get the condition right : like for example if 9+1=10 return 0 and if 9+2=11 return 1 can anyone help me with this thx

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

Use this .. U need to close all parentheses at the end cant close it with in statement

IF(A1=10,0,IF(A1=11,1,IF(A1=12,2)))

Soooo easy, once I read your explanation of how Excel uses the syntax.

I will be checking back if I have any problems with any other formulas.

Thank You!

may you use some simple examples like classroom activities

Thanks for the Help!

ty :)

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.

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!

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.

Great Job , keep it up.

Thanks, Thanks, Thanks a million thanks again

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!!

thank you
it will be useful because i can do my SA1 test

I was very pleased to find this site. I wanted to thank you for this great read!! This is a very informative post, it helps me more

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…

I MAKE BANK RECONCILIATION STATEMENT HOW TO CALCULATE THIS AMOUNT

A B
500 DR
200 CR
300 DR
500 CR
—– ——
I WANT CAL. DR AMOUNT AND CREDIT AMOUNT

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

This is such a great resource that you are providing and you give it away for free. I love seeing websites that understand the value of providing a quality resource for free

pa help po sa exam namin plss!! this is the input
(Iput number of Employees:2:5:10

and let the user Input the following depends on the number of employees;
Employees ID:
Employees name:
Employees Level:

if level is
level rate
1 320.50
2 410.30
3 500.00
)
“Employees rate:”Display depend’s from the Input level???????

Me read http://www.experiglot.com/2006/12/11/how-to-use-nested-if-statements-in-excel-with-and-or-not everyday, good information and practice for English. Thank You as is pleasure read your site.

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 :)

this is stupid i don f***ing get this @ all!!!

Thank you very much! very usefull statement.

Extremely useful website. Thanks!

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!!!!

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.

Fantastic post!! Thanks!!

Clear concise & v helpful. I have applied this to make a supremely complex (& much valued) occupancy analysis which ensures my continued employment because only I know how it works!

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.

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.

=IF(AND(F5=”PRESS ONLY”,G5=”EXPRESS”),5000),IF(AND(F5=”PRESS ONLY”,G5=”reguler”),4000)

What’s wrong with that? please help!

how can i calculate figures whchi is in fromulated colume.

I was very pleased to find this site. I wanted to thank you for this great read!! This is a very informative post, it helps me more.

Thank you for the auspicious writeup. It if truth be told was once a leisure account it. Glance complex to far delivered agreeable from you! However, how can we be in contact?

Calcultaiotn of Gratuity
ifEmployed for less than 1 year – no gratuity
Employed between 1-3 years – 7 days for each year of employment (1/3 of the limited contract amount)
Employed between 3-5 years – 14 days for each year of employment (2/3 of the limited contract amount)
Employed longer than 5 years – 21 days for each year up to 5 years, and 30 days for each year after 5 years (same as for limited contract holders). Maximum limit of 2 years worth of salary

Can you please help me in this.

I never knew how to work on this in a thorough manner , until I saw this.

Hii i want to formula for below problem.

If marks is 40-60 text should be “PASS” if it is more than 60 & less than 75 First Class , If more than 75 “Disti”.

Pls answer!..

Thanks & Regards
Santhosh Shetty

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.

perfect, u r wundrfull!

I like your way of writing.

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?

I am trying to complete a formula in which if cell I5= “SAV” or “CD or “IRA”, then date (B5)+120 days, if not “N/A” Can anyone help me with this? I have figured out how to calculate the date, but not sure how to do it if my condition is based on “OR”!

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.

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..

Great info.

Hey There. I found your blog using msn. This is an extremely well written article. I’ll make sure to bookmark it and come back to read more of your useful info. Skup Samochodow Thanks for the post. I will definitely return. Feel free to yourself

you are highly requested to give me a simple of dcount, dsum

THANKS

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

What kind of boss is this? If you sell more than 200K, you don’t get a bonus…
That is mean!

Hi,
Thanks for creating a site where a beginner like me can learn and get work done. I have a data showing Country name and monthly sales for 4 quarter. I am trying to create a formula where it checks the data and if the country name match with A1 and month match with B1 then return the sum of all values.(Please see below the table I am trying to create) i.e
Name January February March Apr USA #VALUE!
Canada
Germany
France
UK
China

Any Help would be greatly appreciated.

Thanks. Syed Rafique

Thanks a lot for the nested if !!

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???

By the way one of the biggest problems i am having is getting around the rule of 8

I just admire this site.Long live this site.

Great !!!!

unfortunately, i need Example for making a tons of homework about if function. can anyone post other example?

Hello:
trying to use charge formula as such:
if # between 0-300, charge $10
if # between 300.01 to 1000, charge 5% of the value
if # over 1000.01, charge 4 % of the value

titled “Status” and place an asterisk (*) in its cells if the balance owed is more than $10000 or a double asterisks (**) if the balance owed is more than $30000.

What’s the fomular please help me?

It is actually fascinating, You’re a very talented blogger. I’ve joined your rss feed and glimpse forward to in search of more of your wonderful post. Additionally, I possess shared your site in my social networks!

I am trying to figure out which formula would work best.
Here is the scenario:
If a review of records was done before a Credential Date, then a review of records needs to be done again.
In addition:
If a site visit is done before Credential Date then another site visit has to be done.

Help!

thats Great

tell me some more formulas about look up

My company began using IF statements when we started developing commission calculators. We actually found that using VLOOKUP was a bit less messy.

We now offer these templates for free download at http://www.oneclickcommissions.com/exp.html

Thanks for thorough guide, bookmarking now!

here is my question. I have two separate tabs with data. I want to compare the location against the other tab location and match it against the name list on both files. But I want to pull the total area from one tab that has the same location and name list

HI

I M SO CONFUSE IN THE CASE OF THESE FORMULAS I HAVE TWO FORMULA BUT I CAN’T TO JOIN IT

MY FORMULA IS
A B C D E
RAJU 6 5 RAM 6

IF(D1=”RAM”,C1*5,IF(A1=”RAJU”,C1*10))

AND THE ANSWER IS 25+50=75

I KNOW THAT THIS IS WRONG FORMUL THIS FRML ONLY TAKE ONE CONDITION
SO CAN U TELL ME WHICH FORMUL I USE IT

can u please teach me how to use if statement condition in excel contains if A column data starts with “b” the result will be “d” column data, otherwise that have to take “c” column data. or otherwise please send some excel sheets contains different if statement conditions to—- ganeshsd19@gmail.com

I need to do an if then statement one workksheet 2 different tabs.
if B3 (of tab 1) is “1″ then B3 (of tab 2) is “1″ only if G3 H3 & I3 all contain the same name ….if B3 is blank in tab 1 leave B3 blank in tab 2
B3 is the same in both tabs but there are about 2000 lines of b3 and i need it to work for all the lines of data G3 H3 I3 all contain differen data (names) in the two tabs
Thanks TT

Thanks a million times this was seriously very very helpful and if possible could u help me …..if you have similar eg in mind i would be able to practice such formulas thankyou once again

hi i wana prepare the general ledger in ms excel sheet or access.
What is the original formula of creating DR. Cr, with balance.
Can you help any one

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

“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”

if(C1 = “Iwantthis”, “setthisvalue”, C1)

this is really helpful.. thanks so much!..

Hi, we have just restarted some new excel tutorials, please feel free to send us questions by email as well even including spreadsheets with examples. We will try to reply to most through our new posts! Email would be admin /AT m35.ca or you can use the “contact” page (link at the top of all pages). Thanks!

please help me to solve this query.
if tot40 but 50 but 60 but 7 but 8 ,result =Excellent.
How can use a formula in excel to solve the above query?

In vlookup function, if vlookup value not found in vlookup table so result shoes #N/A.
It is possible if vlookup not found value in vlookup area it is shows blank.

Please hemp me

A B C D E F
1 B Eng Math GS SS Phy
2 98 31 58 85 65 14

=IF(and(A2,B2,C2,D2,E2,F2)<=33,"F","P")

Dose not work properly,
Please Help Me…

I was able to nest 5 if(and(),b1*b2, “400″*B3)…..Thanks a million.

Description  Cpn. Value Qty. Price  “Final Price
w/coupon” % Saved 

Milk – Soy $0.25 1 $3.99 $3.49 13%

Hot Dogs $1.00 4 $2.50 $8.00 80%

=D2*C2-B2-IF(B2>=”.50″,B2*2)-B2

=IF(B3<".50",B3*2)/D3

I’m trying to make a coupon grocery list. My favorite grocery store doubles .50 and under. am I able to have one statement for both fields?

gr8 it helped me alot thank you

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))))

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

Of course it will return 0 if not “Enrolled” nor “InProcess” regardless of value.

@Cathy-
Disregard the previous post if wanting to utilize OR() function; which I guess is the whole purpose of this page :)

You’ll have to type the below formula. Copy and paste from this site may not work as it may alter the double quotes.

=IF(OR(TRIM(A1)=”Enrolled”,TRIM(A1)=”InProcess”),10,IF(OR(TRIM(A1)=”Rejected”,TRIM(A1)=”DoNotProcess”),0,”"))

This formula will return a blank if neither condition is satisfied.
Hope it helps.

it was good explanation but example was too short and small so.

it is really useful. Thanks alot
keep doing.

THANKS FOR YOUR COMMENTS I LEARNED SOMETHING I DON’T KNOW BEFORE REGARDING EXCEL THANKS A LOT.

thank you so much, now i can complete my formula exactly as i need.. hooray ^_^

Thanks for ur assistance

I have forgot how to use the if function with formating. So i have a list of values and i want them to appear green if they are over a certain limit and red if they are under it. How do you do that?
Thanks

@CATHERINE
If using Excel 2007:
Navigate to ‘Home’ tab.
Under ‘Styles’ section click ‘Conditional Formatting’. Click ‘New Rule’. Select ‘Use a formula to determine which cells to format’. In the text box provided under ‘Format values where this formula is true’ type a conditional formula, e.g., ‘=IF($A1>10,1,0)’. Then click the ‘Format’ button to set the font or fill you wish to use if the formula returns TRUE. After applied, you can right-click drag the outside selection of the cell down to apply on a broader range.

Hope this helps.

I am trying to get just a Y or N when applying this formula =IF(OR(P2>=0.5,P2=10%,Q2<=-10%),"Y","N")) However, when the value does not meet the 1st criteria, it is returning a False. It seems to work when the value does meet both criteria because I am getting a Y when I am supposed to.

Sorry, the formula is not showing correctly. It should have been =IF(OR(P2>=0.5,P2=10%,Q2<=-10%),"Y","N"))

Still not showing correctly. It should be
=IF(OR(P2>=0.5,P2=10%,Q2<=-10%),"Y","N"))

I’m trying to write an IF statement to help figure time and apply Shift Diff when applicable. If they work Friday after 5PM and until Monday at 8AM they get SD. How can I write that?

I have a slightly different problem. I have a row in which I created data validation drop down menus to select a product code. I would like to create an “IF” formula in the adjoining cell to select from a list of product descriptions which match those product codes. For instance, if product “123-45″ is a “Whisby Flammer”, I would like to select product “123-45″ from the drop down and then have the adjoining column auto-fill as “Whisby Flamer” based on an “IF” formula referencing a separate list based on the criteria of “123-45″.

this formula is not work plz suggest me
=+IF(AND(AE12=0,AP12=0,BC12=0),BH12,IF(AND(AE12>0,AP12=0,BC12=0),(BH12-Z12),IF(AND(AE12>0,AP12>0,BC12=0),(BH12-AK12-Z12),IF(AND(AE12=0,AP12>0,BC12=0),(BH12-AK12),IF(AND(AE12>0,AP12>0,BC12>0),(BH12-AV12-AK12-Z12),IF(AND(AE12=0,AP12=0,BC12>0),(BH12-AV12),IF(AND(AE12>0,AP12=0,BC12>0),(BH12-AV12-Z12))))))))

I would like to know if it is possible to write a formula to determine “pass” or “fail” given the following criteria:
The scores are given as follows in excel :
example : (subjects are named A to G)
The subjects are typed in left to right
in one row, not in columns.
A B C D E F G
44 31 28 38 48 65 55

criteria :Subject A must be >= 40, from B to G there must two subjects >=40 and three >=30 – for a student to pass.

Hi Abubaker, I will send you a spreadsheet to do this (today or tomorrow) and post the result in a few weeks on the website. Thanks for the question!

How to write command for this problem. In cell D1 write matter of A1 if in A2 there is some value but if there is some value in B2 then in D1 write which is in B1 and if there is some value in C2 then in D1 write which is in C1

I tried with the following excel formula to assign the auto code for 75 districts, this says error “more than 64 nested condition !” how to do that ??? Please help

If(A1= “Achham “, “69 “,If(A1= “Arghakhanchi “, “51 “, If(A1= “Baglung “, “45 “, If(A1= “Baitadi “, “74 “, If(A1= “Bajhang “, “68 “, If(A1= “Bajura “, “67 “, If(A1= “Banke “, “57 “, If(A1= “Bara “, “33 “, If(A1= “Bardiya “, “58 “, If(A1= “Bhaktapur “, “26 “, If(A1= “Bhojpur “, “10 “, If(A1= “Chitwan “, “35 “, If(A1= “Dadeldhura “, “73 “, If(A1= “Dailekh “, “60 “, If(A1= “Dang “, “56 “, If(A1= “Darchula “, “75 “, If(A1= “Dhading “, “30 “, If(A1= “Dhankuta “, “7 “, If(A1= “Dhanusha “, “17 “, If(A1= “Dolakha “, “22 “, If(A1= “Dolpa “, “62 “, If(A1= “Doti “, “70 “, If(A1= “Gorkha “, “36 “, If(A1= “Gulmi “, “46 “, If(A1= “Humla “, “66 “, If(A1= “Ilam “, “3 “, If(A1= “Jajarkot “, “61 “, If(A1= “Jhapa “, “4 “, If(A1= “Jumla “, “63 “, If(A1= “Kailali “, “71 “, If(A1= “Kalikot “, “64 “, If(A1= “Kanchanpur “, “72 “, If(A1= “Kapilbastu “, “50 “, If(A1= “Kaski “, “40 “, If(A1= “Kathmandu “, “27 “, If(A1= “Kavre “, “24 “, If(A1= “Khotang “, “13 “, If(A1= “Lalitpur “, “25 “, If(A1= “Lamjung “, “37 “, If(A1= “Mahottari “, “18 “, If(A1= “Makwanpur “, “31 “, If(A1= “Manang “, “41 “, If(A1= “Morang “, “5 “, If(A1= “Mugu “, “65 “, If(A1= “Mustang “, “42 “, If(A1= “Myagdi “, “43 “, If(A1= “Nawalparasi “, “48 “, If(A1= “Nuwakot “, “28 “, If(A1= “Okhaldhunga “, “12 “, If(A1= “Palpa “, “47 “, If(A1= “Panchthar “, “2 “, If(A1= “Parbat “, “44 “, If(A1= “Parsa “, “34 “, If(A1= “Pyuthan “, “52 “, If(A1= “Ramechap “, “21 “, If(A1= “Rasuwa “, “29 “, If(A1= “Rautahat “, “32 “, If(A1= “Rolpa “, “53 “, If(A1= “Rukum “, “54 “, If(A1= “Rupandehi “, “49 “, If(A1= “Salyan “, “55 “, If(A1= “Sankhuwasava “, “9 “, If(A1= “Saptari “, “15 “, If(A1= “Sarlahi “, “19 “, If(A1= “Sindhuli “, “20 “, If(A1= “Sindhupalchowk “, “23 “, If(A1= “Siraha “, “16 “, If(A1= “Solukhumbu “, “11 “, If(A1= “Sunsari “, “6 “, If(A1= “Surkhet “, “59 “, If(A1= “Syangja “, “39 “, If(A1= “Tanahu “, “38 “,If(A1= “Taplejung “, “1 “, If(A1= “Terhathum “, “8 “,If(A1= “Udayapur “, “14 “, “00″)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

Soooo easy, once I read your explanation of how Excel uses the syntax.

Many thanks

I’m trying to figure out how to make a spreadsheet for my commissions…where i just enter the weekly profit and my weekly markup…and using the table below, have it automatically figure out the commission. the hard part is that we get 3 stages of commission…paid a certain % on first 5k of profit per week, then a bigger % on the next 5k of profit per week…then anything over 10k in profit is paid the highest %. But, it also makes a difference what column of markup you fall under. any suggestions?

< 20 % 20.1 – 25.9 26 – 29 29.1 – 31.9 32 +
0 – 5000 5 6 7 8 9
5000-10000 6 7 8 9 10
10000 + 7 8 9 10 11

sorry…that table did not come out the way it was supposed to…i can email you what it should look like in excel

I need a multi level nested formula, to cover the following possbilities of cells A1 and B1.
If cell A1 100% of “X” and B1 is between 100 and 90% of “X” cell C1 shows 1.
If cell A1 is 100% of “X” and B1 is between 89 and 80% of “X” cell C1 shows 2.
If cell A1 is 100% of “X” and B1 is between 79 and 65% of “X” cell C1 shows 3.
If cell A1 is 100% of “X” and B1 is equal to or less than 64% of “X” C1 shows 4.

I am trying to get a value for the number of days a case is open as of today or if its closed the number of days it was open. If there are dates in both date fields its easy, the problem is when the date fields havent been filled in yet.

Here is what i need

If the report complete date is blank and report start date is blank then days open should be zero. if start date has a value but complete date is blank then i want to take todays date – start date, if complete date is not blank then i want to take complete date – start date.

I think i almost have it, every scenario worked until i put both dates in. Then i got a “false” error. Here’s the formula i have so far.

=IF(ISBLANK(AZ123),IF(ISBLANK(AT123),0,IF(ISBLANK(AZ123),TODAY()-AT123,AZ123-AT123)))

Any ideas?

This articlewas a outstanding read. I Learnt alot from it. Many thanks for sharing.

this site is very help full thanks for greate teaching

HOW CAN I CREATE A FUNCTION THAT WILL COMPARE IF A MULTIPLE SUB-FOLDERS EXIST IN A CERTAIN FOLDER? PLS HELP
I am out of my time limits

this site is very help full

hii.. i am nagesh & want to know is

i have 15 emplyees..

their NT working hours is 0830 to 1730 hrs.
if they work beyond this time it will be OT & full sunday & holidays also OT.
so i need simple & automatic NT/OT calculation formulas employee wise.
Also we are giving daily allowances to them’
if they work minimum for 6 hrs daily either NT/OT they are eligible for single allowance of Rs. 200/- & if they worked more than 12 hrs daily the applicable for double allowances.
Employee Name working hours NT hrs OT hrs allowances (single/double)
Biju 1000 to 2100 hrs ?? ?? ???
Sijo 0930 – 2230 hrs ?? ?? ???

Pls help me out formulas in question mark..
Thanx in advance…
Hv a great evening..!!

Thanks U

Hi,

This is great post .

Wonderfull, I have been looking for this since quite a long time.

=IF(a2=>2 then a3=”completed”)

=IF(a2=>3 then a3=power(700,2)

=if g1>12 then g2=+1 ‘hence g2 should be 0 in cell

if g1=(“5″,g2 then=5*2)

Thank you so much for this wonderful explanation!! I have been to many sites looking for an example that was close to what I needed to accomplish. Your example hit the nail on head!! Thanks again!!

Hello, I need add a formula for the following example,,, in plain English. IF H7 equals X, then I7 must be either a 1 or 2. Can you help.. for further enhancement it could say (if possible) ..If H7 equals X, then I7 must be either 1 or 2 AND J 7 must be blank.
Thanks!!

IF formula needed: Column E is the sum of Columns C & D (IF) < 4hours minus .50 add Column E, (IF) Column H shows codes NL do NOT subtract .50 from Column E
Total hours cell – sum of Column E and Column F Help!?

I need help! I need to create a formula in excel. I have a column of cells that are all equal to one of the following values: Eastern, Central, Mountain, Pacific.
I need the next column of cells to equal 0 if the preceding column says Eastern, equal -1 if the preceding column says Central, equal -2 if the preceding column says Mountain, and equal -3 if the preceding column says Pacific.
I have tried a variety of =and(if… =or(if(…
but it always returns a VALUE! statement.

This is fine but it will not handle dates

I am creating a formula for my backorder inventory, pls help me to formulate this one
=IF(AS80,”WITHBACKORDERS”,IF(AS5=0,”EXACT DELIVERY”))), the problem is it will not come up for my expectation…pls help.tnx

Sorry for the error i have to repost my formula =IF(AS80,”WITHBACKORDERS”,IF(AS5=0,”EXACT DELIVERY”))) tnx.

oh not again =IF(AS80,WITH BACKORDERS,IF(AS8=0,EXACT DELIVERY))) sory for the error..this my formula, pls kindly correct it.tnx

I would like to help my daughters school simplify a name to number matching issue they are having with a program they run monthly. I need a statement that says: if student name is James then account number(s) 1,2,3… should be given credit

Anyone know who to do it?!

Hi,
I have come accross your site which is really useful. I am really having a problem with something I want to do. I want to know how I can move a row of information from one sheet to another when that information reaches either a probability % or changed from Prospect to Pileline, then from pipeline to closed. Any help would be great.
Many Thanks
Julie

Wow!…It’s a great forum in Excel…Hoping this forum will continue up to the upcoming version of Excel i.e. excel 2010,2012

Hey Thanks a lot sir it was really helpful, i was struck in between my but your tutorial helped me a lot thanks :)

this formula is not work, please check

=IF(B1<01-04-2011,"April-11",IF(B1<01-05-2011,"May-11",IF(B1<01-06-2011,"June-11",IF(B1<01-07-2011,"July-11",IF(B1<01-08-2011,"Aug-11",IF(B1<01-09-2011,"Sep-11",IF(B1<01-10-2011,"OCt-11",IF(B1<01-11-2011,"Nov-12",IF(B1<01-12-2011,"Dec-11",IF(B1<01-01-2012,"Jan-12",IF(B1<01-02-2012,"Feb-12",IF(B1<01-03-2012,"Mar-12”, "Month Check"))))))))))))

Please help me ..

The numbering system for dates considers 1/1/1900 to equal 1. So “1-4-2011″ or April 1, 2011 would actually be the number 40603. Using Excel, you can format the dates to get the proper numeric equivalents. Good luck.

How does classify the employees designations with A, B, C, D, E, etc. according to their designation in Excel with examples

Need some because I am new to IF function.. Please help me put in anIF funtion..thanks

Logical_test if BI2 -BK2 = -1, or BI2 -BK2 = 0, my true value should be 0, if false should be 1

A very nice and very informative help-sharing site.

DIANA,

The formula should be:

=IF(OR(BI2-BK2=-1, BI2-BK2=0),0,1).

ok here is my issue
I need a formula that will give me 4 diff. values depending on three different factors
so factor one 1100 must be met to get value .05 other wise 0
factor two is if factor one was met in prior week and in current week value is .1 other wise 0
factor three is if factor one and factor two have been met then value .15 otherwise 0 I can get it to work in the first 3 cells but if 1 factor is not met it is suppost to drop to 0 then go back up and it is not

you know what are the codes we could use in making a program about false-method using excel?

I need a formula about excel for date please help me for this function i know the today and now formula with out they two formulas

Hello, could someone help me…to fix this formula…i’m recievend error with #Name!
i’m having this formula now…what needs to be added to have value from other sheet ? thank you for prompt answer, A.

=IF(NOT(BLANK(RawData!P3,RawData!Q3,RawData!R3)),TRUE, “C”)

@Anna – What are you trying to do with the Blank function?

@experiglot -> in sheet called RawData in columns P, Q, R row 3 i got 3 different dates ….and the next sheet where i would like to get letter “C as completed” and with NOT BLANK i thought it would be possible….

i forgott to mentioned that the date which i would like to see as C – completed in sheet called milestone status, date is visible in Rawdata sheet R3

@experiglot -> i got it :)
=IF(ISNUMBER((RawData!P3, RawData!Q3, RawData!R3)),TRUE, “Completed”)

but now i do not know how to assign BLUE color to completed column in second sheet milestone status…??? could u help me please with this ?

hello,
how to correct this formula into one field ? thank you in advance.

=IF(ISBLANK(RawData!J3),RawData!K3,RawData!L3)=IF(ISBLANK(RawData!K3),RawData!L3,RawData!J3)=IF(ISBLANK(RawData!L3),RawData!K3,RawData!J3)=IF(ISNUMBER((RawData!J3,RawData!K3,RawData!L3)),TRUE,(“completed”))

=IF(AND($M$3>=$D114,$M$3<=$E114),($F114/$O114*($M$3-$D114+1)),F114)

WHAT IS WRONG IN THIS AS IT PICKUP THE WHOLE % IF D114 IS GREATER THAN M3

Need help with this formula below..Any help is appreciated

+IF(C6=15101520,”20+ yrs”))))))

+IF(C6=15101520,”20+ yrs”))))))

For Lexxi: Not sure if you wanted string outcome…try this

=IF(C6=15101520,”20+years”,”")

Best example and simple demostartion, thanks a million!!

I’m trying to get a “W” or “L” to appear in a cell based on certain conditions but I can’t find the right formula to meet all conditions. Here is what I’m trying to do.

if A1<A2 and B5B2 and B5>B6 I want a “W” in cell A3. But when A1>A2 and B5<B6 or A1B6 I want a “L” to appear in A3. How can I set that up? Thank you in advance.

If A1<A2 and B5A2 and B5>B6 “W” but if A1>A2 and B5<B6, then "L". thanks

That is supposed to be A1<A2 and B5<B6.

Need help with an IF statement. =IF(Sheet3!H19<1,=Sheet3!D18). If Sheet3!H19 is less then 1 dispaly the text that is in Sheet3!D18.

Thanks

Never mind I got it. Thanks
=IF(Sheet3!H19<1,"",Sheet3!F18)

Found this tip today while searching for a solution using IF and OR.

Thank you! Worked great.

Need some help please. Not doing something correct. I have a a range of qualifiers to determine a calculation. Assume that the reference lookup is in cell E6. If the value of E6 is 280 then E6*3.50. Thanks for any help.

Nice article! Helped me a lot.

thank you so much!!!

I have a related question. I have two columns. One is a data label and the other is data. The data labels are categories and some of them are repeated. I would like a simple automated way to have the data from each category added together so that the result is a a new set of columns with no repeated data labels. I could easily do this with a Linux shell script or simple C/C++ program, but for this specific case, it would be better to do in Excel. Thanks.

@Brent – Not sure I understand the issue, but would a pivot table be a possible solution?

Congratulations, you’re more intelligible than my idiot professor. Thanks for actually teaching me something.

Very helpful site! Thank you.

pl explain how to read a particular cell based on an IF condition. The data is available within the excel sheet. It should be similar to reading a data from arrayed variable in C language.

Could someone advise why i keep getting true statements anytime b1 is > 250,000 (and not the calculation i’ve stated in the formula)
Basically what i’m after is:
if b1 less than 250,001 then b1*0
else
if b1 more than 250,000 AND less than 500,001 then b1 * 0.02
else if b1 more than 500000 then b1 * 0.04.
any help would be appreciated!

=IF(B1250000,B1500000, B1*0.04)))

full formula for query mentioned above…
=IF(B1250000,B1500000, B1*0.04)))

thx in advance!

‘=IF(B1250000,B1500000, B1*0.04)))

pls guide me i need a formula on how to alter results when it’s 10 and above.
whenever it reaches 10 the answer will be =0
when 11=1
12=2
13=3;
and so on.
20=0
21=1
22=2
example:
1+9=0
3+7=0

all is just simple addition and subtraction.

and when it is
0-1=9

but if it is below 10 on the addition,
results would be as normal
thank you

Donkey, what you need is a function called MOD. Lemme give you a few MOD examples:-
the function takes the form, MOD(number, divisor)
A. MOD(10,10) = 0
B. MOD(11,10) = 1
C. MOD(16,10) = 6
MOD(22,10) = 2
…. and so on and so on

Hope this helps…

I just discovered using Joomla for dynamic website design but feel the limitation on use of templates is a major minus. I think it should be possible to use my own template in the design. Anyone help?.

It’s really helpful for my research, thank you very much

I need some help

I Need to match Column A with Column B to get the Results in Column C.

Column A is a letter code (I.E. BNS for Beans) Column B is a Number code (10 for 10#.) Column C. is my Quantity (how many 10# packages of beans I have.)

An impressive share, I simply given this onto a colleague who was doing a little bit evaluation on this. And he the truth is bought me breakfast as a result of I discovered it for him.. smile. So let me reword that: Thnx for the deal with! But yeah Thnkx for spending the time to discuss this, I feel strongly about it and love reading more on this topic. If doable, as you change into experience, would you mind updating your blog with extra details? It’s extremely helpful for me. Large thumb up for this blog put up!

Overall I found this site to be very informative and it is truly appreciated that there is no fee or signup required.

this has been great help and i’ve created a few formulas, but having trouble with this: if a2>35 and b2 is not equal to >3 years, then yes/no
please help! I’ve been at this for over 2 hours and obviously missing something.
Thanks

Can you please help me with writing a formula(s) that would find several different words and tag them in Excel 2007? For example, in the following lines, I would like to write a formula that would find “First Aid” or “CPR” or “AED” and assign #1 (or any kind of tag). I also would like it to find “swimming” or “Aquatics” and assign #2. The formula needs to also assign #3 to others that do not have any of those words. There is a $ values attached to each line, and I want to sum based on category. I have several hundreds of lines, so it’s not practical to go through them manually. I’ve been brawsing the web for a few hours but I can’t find how to do it. Please help.

* Adult First Aid/CPR
* Standard CPR/AED, Pediatric CPR and first Aid
* Standard First Aid
* Swimming and Skill Proficiency
* Child Aquatics

I am using Excel 2003 and draw data from an outside source into various cells on the spreadsheet. I then summarize in column H, eg. =if(G1=0, 0, if(G1=5, 1, ” “)). G1=sum(B1:F1).
Now, I would like to focus on when there is a “0″ or “1″ in column H, as the data is constantly changing so most of the time there is a “blank”, but when a “0″ or “1″ is displayed, I’d then like to retain the “0″ or “1″ in column N until other data that I have in columns J:L total either a 0, 1, 2 or 3, then take a display a different number. For example, if column H shows a “0″, I’d like that “0″ displayed in column “N” until column “M” (which is a sum of J:L)=2, then I’d like it to display a blank and await a new in column “H”.

If column H shows a “1″, I’d like that “1″ displayed in column “N” until column “M”=1 or 0, at which time I’d like it to display a blank and await a new number in column “H”.

So, to clarify, because the numbers in column “H” could change numerous times, I’d like column “N” to hold the number displayed in column “H” until other parameters are met, basically to smooth out the noise of numerous and short lived changes to try and catch a reversal, then display the new result.

Could you assist with a code for column N to provide this?

I hope this is understandable and I’d be most appreciative if you could offer your expertise to solve this dilemma for me.

Thanks
another cell depending on when the sum of the three additional cells shows a “0″, or “1″, and then display a blank if it shows a “2″ or “3″, unless the calculated value

Mike,

I am having some trouble with nesting IFs ANDs & ORs. I am working on a tracker that has dates in 3 columns (let’s say F (Due),G(Review),H(Submitted)). From this, I need to have the tracker tell me if the product was submitted ontime or overdue plus tell me when I open the document if the product is In Process (due is more than 7 days from Today) or Due Soon (due in less than 7 days). Also, I need the column to not show anything if there isn’t a product listed in the row. So for, I have been able to enter the formula to get the In Process or Due Soon to show, but haven’t been able to get the formula to show Ontime or Overdue when information is entered into columns G or H. The formula I have so far is
IF(or((AND(F6″”,G6=”",H6=”")),((IF(F6>(TODAY()+7),”In Process”,IF(F6<=(TODAY()+7),"Due Soon"))))),(And(f6″”,g6″”,h6″”), ((if(h6<=f6,"Ontime","Overdue"))))

Can you help me get this working?

Thanks in advance.

it was very bad ive seen toads explain it better

its very very very terible

hurga blurgah HERP DERP DICK CHEESE

i agree bob hurga blurga indeed

NEED A BURGER NOOOOOOOOOOWWWWWWW!!!!!!!!!!!!!

Please I need your help to solve below mentioned formula,

=IF(A2=”January”,”8″,IF(A2=”February”,”8″,IF(A2=”March”,”9″,IF(A2=”April”,”9″,IF(A2=”May”,”9″,IF(A2=”June”,”9″,IF(A2=”July”,”7″,IF(A2=”August”,”7″))))))))

The problem is I cannot add September 8, October 8, November 10, December 9,

After any Eight Months showing error,

Please I need your Quick support if possible,

Thanks in Advance,
Shakkir

Make a hidden table like this

Column E Column F
JANUARY 8
FEBRUARY 8
MARCH 9
APRIL 9
MAY 9
JUNE 9
JULY 7
AUGUST 7
SEPTEMBER 8
OCTOBER 8
NOVEMBER 10
DECEMBER 9

and apply this formula in B2,
=VLOOKUP(A2,E1:F12,2,FALSE)

@ Shakkir Kottayil

Another way to do this…

=LOOKUP(A2,{“April”;”August”;”December”;”February”;”January”;”July”;”June”;”March”;”May”;”November”;”October”;”September”},{9;7;9;8;8;7;9;9;9;10;8;8})

I keep getting errors in Excel stating that the following formula has errors in it:

IF(AND(Q4=”Functional”,P4>=150000,”Y”,IF(AND(Q4=”Program Mgmt”,P4>=185000,”Y”,”N”))))

Any thoughts?

@ Kamran,

Many Thanks….. for your Support….

Its worked first one, but second one not worked, it is better than first, can you please confirm it again,

Thanks,
Shakkir

BLEH

Its great…thanks for your help…

Im trying to use an If statement for a cell for when you type in a number, the cell next to it will give you the result. For example:

If you insert 1, 2, 5, or 7 in cell A2, then B2 would display Mike. If you insert 2, 4, 6 or 8 in cell A2, then B2 would display John.

I have a total of 24 numbers, but the answer would only be Mike or John. Please help me! Thanks

evista buy online

I have a row (H78:AD78) that sums 23 columns of data that is changing daily. I would like to highlight the highest number in green the next highest in yellow, the third highest in amber, and the rest in red. There can be duplicate numbers.

Please help!

THANK YOU!!!!

This was a huge help to me! Much better than Excel’s help. I appreciate it.

really helpful

Thank you for breaking down the information in an easy to understand way.

The web site very helpful to student and working people m learning if formula in this site

hi friends,

how do i write formula for following:-
if Value of C35 but 10 but 20 but <30 then set D3='D';

i want a formula for
value 90=40%
am wrking but bcz of sm error its saying FALSE. hlp plz..

want a formula for
value
90=40%
am wrking but bcz of sm error its saying FALSE. hlp plz..

I need help with writing an “IF” formula in excell. Background: I run a local golf league with 40 to 70 golfers playing each week. Instead of using strokes and keeping up with the handicaps, I use a point system. Each week I have to manually calculate each man’s score, plus or minus, from his required points. Example: For myself I currently am required to make 45 points. If I make within plus or minus 2 of the 45 points there is no change to my next weeks requirement. However, if I make minus 3 or more points, my score will drop 1 point(to 44). If on the other hand, I score 3 or 4 points above 45, my new point requirement increases by 1 point, if I score 5 or 6 points above, my requirement increases by 2 points, if I score 7 or more points, my requirement increases to 3 points. I currently use and excell spreadsheet, listing the players in cells A3 to A73, their points requirement in B3 to B73. I enter their that days point total in C3 to C73 and then manually do the math and enter their new point requirement in D3 to D73. I can continue to do the math…but in this day and age I would like to work “smarter not harder” Please take pity on and old man and help. Thanks. You will not imagine how much sharing your knowledge with me will be appreciated.

I am trying to create an IF statement with date parameters:

IF Cell x is > 12/2/2009 but less than 12/31/2011 return 0-3 years

IF Cell x is > 12/2/2006 but less than 12/2/2009 return 3-5 years

IF Cell x is > 12/2/2001 but less than 12/2/2006 return 5-10 years

IF Cell x is 10 years

Thanks