Excel’s AND and OR formulas on their own aren’t very exciting, but mix them up with the IF Statement and you’ve got yourself a formula that’s much more powerful.
In this tutorial we’re going to take a look at the basics of the AND and OR functions and then put them to work with an IF Statement. If you aren’t familiar with IF Statements, click here to read that tutorial first.
AND Function
The AND function belongs to the logic family of formulas, along with IF, OR and a few others. It’s useful when you have multiple conditions that must be met.
In Excel language on its own the AND formula reads like this:
=AND(logical1,[logical2]….)
Now to translate into English:
=AND(is condition 1 true, AND condition 2 true (add more conditions if you want), if so do this, if not do that)
OR Function
The OR function is useful when you are happy if one, OR another condition is met.
In Excel language on its own the OR formula reads like this:
=OR(logical1,[logical2]….)
Now to translate into English:
=OR(is condition 1 true, OR condition 2 true (add more conditions if you want), if so do this, if neither are true do that)
See, I did say they weren’t very exciting, but let’s mix them up with IF and put AND and OR to work.
IF AND Formula
First let’s set the scene of our challenge for the IF, AND formula:
In our spreadsheet below we want to calculate a bonus to pay the children’s TV personalities listed. The rules, as devised by my 4 year old son, are:
1) If the TV personality is Popular AND
2) If they earn less than $100k per year they get a 10% bonus (my 4 year old will write them an IOU, he’s good for it though).
|
In cell D2 we will enter our IF AND formula as follows:
In English first
=IF(Spider Man is Popular, AND he earns <$100k), calculate his salary x 10%, if not put “Nil” in the cell)
Now in Excel’s language:
=IF(AND(B2=”Yes”,C2<100),C2x$H$1,”Nil”)
You’ll notice that the two conditions are typed in first, and then the outcomes are entered. You can have more than two conditions; in fact you can have up to 30 by simply separating each condition with a comma (see warning below about going overboard with this though).
IF OR Formula
Again let’s set the scene of our challenge for the IF, OR formula:
The revised rules, as devised by my 4 year old son, are:
1) If the TV personality is Popular OR
2) If they earn less than $100k per year they get a 10% bonus.
|
In cell D2 we will enter our IF OR formula as follows:
In English first
=IF(Spider Man is Popular, OR he earns <$100k), calculate his salary x 10%, if not put “Nil” in the cell)
Now in Excel’s language:
=IF(OR(B2=”Yes”,C2<100),C2x$H$1,”Nil”)
Notice how a subtle change from the AND function to the OR function has a significant impact on the bonus figure.
Just like the AND function, you can have up to 30 OR conditions nested in the one formula, again just separate each condition with a comma.
Try other operators
You can set your conditions to test for specific text, as I have done in this example with B2=”Yes”, just put the text you want to check between inverted comas “ ”.
Alternatively you can test for a number and because the AND and OR functions belong to the logic family, you can employ different tests other than the less than (<) operator used in the examples above.
Other operators you could use are:
- = Equal to
- > Greater Than
- <= Less than or equal to
- >= Greater than or equal to
- <> Less than or greater than
Warning: don’t go overboard with nesting IF, AND, and OR’s, as it will be painful to decipher if you or someone else ever needs to update the formula in months or years to come.
Note: these formulas work in all versions of Excel, however versions pre Excel 2007 are limited to 7 nested IF’s.
You can download the workbook used in this example to practice here.
For more on basic IF statements go here.
For more on Nested IF statements go here.
For more on Absolute References go here (they’re the formulas with the $ signs in the cell reference e.g. $H$1)
Don’t forget you can get more free Microsoft Office online training and video tutorials from My Online Training Hub. Just type your name and email address in the box at the top left and click the ‘Get Free Access’ button for instant access to our free online training.
Share the knowledge with your friends and colleagues on Twitter, facebook etc. using the shortcuts below.
Got a clever way you use IF AND OR? Let us know by leaving a comment.
... I would highly recommend My Online Training Hub for all your Microsoft Office needs .... Geniuses
{ 110 comments… read them below or add one }
thanks for the post
hi Robert,
Glad you liked it. Hope we are providing more useful info in our other blogs too.
Phil
this blows my mind. will have to go over it again when I can concentrate more!
In excel-07 I need to write formula if excel
If (cell A1)is less than 100 then multiply by2.47,If it is greater than 100 but less than 300 then multiply by 4.37, If it is greater than 300 but less than 500 then multiply by 6.25, If it is greater than 500 but less than 1000 then multiply by 7.25, If it is greater than 1000 then multiply by 7.50,
The above rate is for one month i.e.30 days
Now Question:-8000 value calculated in 7 months.
Hi Abhay,
Instead of a nested IF statement you’re better off using a VLOOKUP like this:
VLOOKUP Sorted List Explained
Not sure what you mean by “Now Question:-8000 value calculated in 7 months”.
Kind regards,
Mynda.
Your post clearly explains the examples and syntax …. All I needed was to know that the OR precedes the choices and Viola! My formula is working. Thanks a bunch!
Ginette
Hi Ginette, Glad to have helped
Kind regards,
Mynda.
nice ideas, thanks
Cheers, Todd
i like this website so much
we learn from youall what we need
I need to created a formula where the values in colums A, B, C, D and E are the same if one of these value are distinct the function has to tell fail, but on C column the value is automatic introduce by code lector as “_valueJ”; I need the function don’t take on count the “_” (space) and the J. And column D the is a “valueR”, so how could I do it?
Hi Viktor,
I’m having difficulty understanding/visualising your request. Can you please send me an example by logging a ticket on the help desk so I can see exactly what you mean.
Kind regards,
Mynda.
Question, if i have a scenario which:
I currently have a IF(OR) situation which allows a certain calculation to be done if the cells satisfy requirement A or B. However, if I need a different calculation to be done have another requirement that could be satisfied but that would need a whole different calculation to be done, how does one do that?
Cell B1 indicates “1 – person”
Cell B2 indicates “1 – person”
If (OR(cell B1 = “1 – person”, cell B2 = “1 – person”, B1x12, 0)
Now, if I have another choice for Cells B1 and BR (2 – person) which would result in a different calculation being done, is there a way to add that in to this formula? For example, if Cell B1 indicates ” 2 – person” then do B1x12+2.
Hi RV,
In Excel 2007+ you can nest up to 64 IF’s, so yes you can add more ‘choices’, however I don’t recommend you use all 64. More on Nested IF’s here:
Nested IF Functions
If you’re trying to nest more than 4 IF’s then I’d use a VLOOKUP function and put all your scenarios in a table.
I haven’t given you a solution to your question since I suspect you haven’t given me all of the information. e.g. if cell B1 contains text then you can’t multiply it by 12, you’ll get a #VALUE! error. So, I figure there’s more to it.
If you get stuck please send me your Excel file containing the example data so that I can give you a specific solution.
Kind regards,
Mynda.
Let Suppose Meter Reading 1000
Question Is If Meter Reading Is Less Than 200 Then Multiply By 2.54/- And If Meter Reading is Above 200 Then Multiply By 3.89/- And Both Total Is we Will Found
Like
Reading is 1000
Then Upto 200*3.54/- + Above 200*3.89/- = Answer
Please Solve My Query As Soon As
I am Waiting Your Help
Thanks
Hi Ashish,
I’m not sure I understand correctly so I apologise if this is not what you’re after:
=IF(A1200,A1*3.89))
Where A1 contains your meter reading.
and
=IF(A1200,A1*3.89))
Kind regards,
Mynda.
Trying to create a formula where if a sum is greater than 50 it should be sum divided by 2 but where less than 50 should be that sum ie.
=IF(J24>50,(J19+J20+J21+J22)/2),IF(J24<50,J19+J20+J21+J22))
Any ideas – help please!!
Hi Susan,
Your formula will be:
=IF(J24>50,(J19+J20+J21+J22)/2,J19+J20+J21+J22)
Kind regards,
Mynda.
Need a little support regarding IF Function:
What is the maximum number of times we can use “If” in a single function.
Ex :=IF(C64<=2000000,6,IF(C64<=3000000,7,IF(C64<=4000000,8,IF(C64<=5000000,9,IF(C64<=6000000,10,IF(C64<=8000000,11,IF(C64<=12000000,12,IF(C64<=15000000,13,IF(C64<=16000000,14,15)
When i type this above function ,,there was a Error msg saying "It uses more level of nesting than are allowed in the current file format"
So kindly help with this plz.
thanks
Hi Chathurika,
The maximum number of nested IF’s in Excel 2007 is 7 and Excel 2010 is 64.
It looks like your formula would be better handled with a VLOOKUP using a Sorted List.
Kind regards,
Mynda.
=+F(C64<=2000000,6,IF(C64<=3000000,7,IF(C64<=4000000,8,IF(C64<=5000000,9,IF(C64<=6000000,10,IF(C64<=8000000,11,IF(C64<=12000000,12,IF(C64<=15000000,13,IF(C64<=16000000,14,15)
Hi Danabalan,
What’s your question?
Kind regards,
Mynda.
Hi,
I have only just found your website and think it is great. I am trying to create a nested IF/AND statement but am struggling. I am trying to determine if people’s training requirements are up to date. Depending on their role, some need to be re-trained each year, and others every other year.
The actual spreadsheet is a lot bigger, but the basics are as follows:
Column A – Staff names
Column B – Whether they are “1 Year” or “2 Year”
Column C – The Date of their last training
Columd D – Is training up to date (this is where my formula is)
In Cell H1 – Today’s date
In Cell H2 – Today’s date less 1 year
In Cell H3 – Today’s date less 2 years
What I am trying to get is
- If Column B is 1 Year, and the training date from column C is less than a year ago, they are “OK”, otherwise “Training Needed”
I can get this far, but what I also need is if Column B is 2 year, and the training date from column C is less than 2 years ago, they are “OK”, otherwise “Training Needed”
I can get either statement to work, but not both together.
Help would be appreciated – thank you.
Hi Elly,
Here is your formula:
=IF(AND(B1=”1 Year”,C1>$H$2),”ok”,IF(AND(B1=”2 Years”,C1<$H$3),"ok","Training Needed"))
You need a nested IF statement.
Kind regards,
Mynda.
Thank you so much – have applied that to my real spreadsheet and it works a treat – fantastic. Am recommending the site to colleagues!
You’re welcome. I appreciate you spreading the word.
Kind regards,
Mynda.
Hi Mynda,
I just learned a lesson in inconsistent syntax within the family of IF formulas. (Are you shocked by a confusion of Excel syntax?)
I am playing with a series of “IF” formulas…IF(AND…IF(OR…If…IF(NOT formulas. Hopefully I can get my thought process across without sending the entire worksheet. Here goes:
The syntax for IF(AND..IF(OR…and IF( all seem to be consistent in the location of parenthesis ():
=IF(C25=TRUE,”Expenses are OK”,”Expenses are too high”)
English translation: If C25=TRUE, spit back “Expenses OK”, if not spit back “Expenses are too high”. The parenthesis go at the end, after the “if not” or FALSE condition. No problem.
So when building an ‘IF(NOT’ formula, I tried the same basic syntax:
=IF(NOT(C25=TRUE,”Expenses OK”,”Expenses are too high”))
..wrongo!
The only way it would work was by adding a closing parenthesis “)” right after TRUE in the logical question:
=IF(NOT(C25=TRUE),”Expenses OK”,”Expenses are too high”)
Of course the Excel error message was of little help, so it took a while to try that closing parenthesis after TRUE.
It just seemed inconsistent compared to the rest of the “IF” family.
Regards,
kayakbob
Hi Kayakbob
I can see why you’re confused. I used to do the same thing, but the way I tend to remember it now is to bear in mind that NOT is a separate function on it’s own and therefore needs closed parentheses before you can complete the rest of the IF function arguments.
It’s the same for OR and AND.
e.g.
=IF(OR(criteria 1, criteria 2),if either criteria are true do this, if both criteria are false do this)
=IF(AND(criteria 1, criteria 2),if both criteria are true do this, if either criteria are false do this)
Kind regards,
Mynda.
Respected Sir,
I want learn all excel formula so send me any guidence & formula
I hope you will send me formula
Tahnking you,
Hi Kapil,
You can find an index of Excel formulas here.
Kind regards,
Mynda.
Hi,
This blog is excellent. This tutorial is very very useful to me as I am in learning stage. Thanks for runing this blog.
But I unable to open the workbook after downloading the ZIP file as all the files are in .xml extension. Could you please help me in this regard.
Thanks & Regards,
Shankar.
Hi Shankar,
Thanks for your kind words.
The file you download isn’t a .zip, it’s a .xlsx file. Your browser is changing the file extension when you download it. What you need to do is download it again and type over .zip with .xlsx at the ‘File save as’ screen (or equivalent for your browser). You will then be able to open the file correctly.
Kind regards,
Mynda.
If I have my dates in row B2:AF2 and names in A3:A600; entries in B3:AF200 are all order numbers. from 1-150:
A B C D
1-1-2012 1-2-2012 1-3-2012
Leo Lim 121 52 52
Peter Justin 52 81
Ben Tumbler 121 121 121
Simon Dredd 52
Please help me if i can use countif function if i need to extract the count of specific work orders in a specific date or automatically at the latest date data were entered. Thanks of your help.
Hi Jed,
Thanks for your question. Can you please be more specific about what you want. For example, you say you want to “extract the count of specific work orders (what specifically about them? e.g. the name in column A or the order number), and in a specific date or automatically at the latest date data were entered”.
If you want to count the number of orders using specific criteria for each column (date) then you can use the COUNTIF Function (one criteria) or COUNTIFS Function (multiple criteria). If you then want to look up that count for a specific date or the latest date then you can use the INDEX & MATCH functions.
I hope that points you in the right direction. If you need some help please send me the workbook and specific instructions on what you want to do.
Kind regards,
Mynda.
I need to write a formula that goes
here are the variables
Salary $21,158.91, to a maximum of $200,000
Contribution percentage 3%
I need a formula that does the following
it will give each employee 100% of the first 2% of their salary and 50% of the next 25 of their salary, below id what I have so far but
=if(salary<=200000,and(Contribution%e1,and(f22<=2),200000*Contribution%,if(salary2),salary*(salary*2/100)+((salary*2/100)*5),if(salary>200000,and(Contribution%>2),(200000*2/100)+(200000*2/100)*5))
what am i doing wrong? Please help!
thanks,
carmen
Hi Carmen,
You’re doing everything wrong
I hope I’ve understood your criteria correctly:
=IF(AND(A1>=21158.91,A1<=200000),A1*0.02*0.03+A1*0.25*0.03*0.5,0)
Kind regards,
Mynda.
Hello Excel lords, I know a little about excel and usually can formulate anything I need, but this is getting me crazy so please help me.
I am a counselor at a school and I was trying to formulate a spreadsheet that calculate the total number of “credits” that an student had completed based on different courses so here is my problem and the example:
Most of courses had 2 parts, A and B, and each part worth “0.5” of credit or half of credit, but my problems comes when I realize that I have 6 different possibilities and I need that my formula give me 3 possible values.
Here are all the possibilities I can have based on 2 cells, Part A & B
Part A Part B
Approve Fail
70+ 69- = AVG >= 70 = 1
Approve Fail
70+ 69- = AVG = 70 = 1
Fail Approve
70+ 69- = AVG <= 69 = 0.5
Fail Fail
69- 69- = AVG = 70 = 1
I need to create a formula that can actually from these 6 different scenarios give me only of whatever of the 3 possible results, 0 or 0.5 or 1.
I am desperate and stuck here, I’m not sure if this can be completed in just one cell honestly I’m lost.
Kind regards and Im really hope someone can sort me this out
The six possible conditions…
Part A Part B
1) 70 or more / 69 or less = if average is greater or equal to 70 result should be 1 one
2) 70 or more / 69 or less = if average is less or equal to 69 result should be 0.5 point five
3) 69 or less / 70 or more = if average is greater or equal to 70 result should be 1 one
4) 69 or less / 70 or more = if average is less or equal to 69 result should be 0.5 point five
5) 69 or less / 69 or less = if average is less or equal to 69 result should be 0 zero
6) 70 or more / 70 or more if average is greater or equal to 70 result should be 1 one
3 Will lead to result = 1
2 will lead to result = 0.5
1 will lead to result = 0
Hi Carlos,
You can use this formula:
Where part A results are in cell A10 and part B results are in cell B10.
Note: in the first 4 scenarios the average of part A & B is 69.5 and results in zero points because the half point. You either need to round up or down the average calculation or change the < = / >= average qualifying amount to allow for decimals in the average.
Kind regards,
Mynda.
Thanks Mynda you really help me, I just adjust the formula but theres one condition that I cant make it work and thisis when you have 2 failing grades I create an spreadsheet to show you my problem where do i send it?
Thank you so much MILLION TIMES!!!!!!
Hi Carlos,
Thanks for your kind words. You can send me an Excel file via the help desk.
Kind regards,
Mynda.
Hi,
I am trying to do an if statement with both OR & AND with an array function and unable to figure it out. For example, I would like the syntax to read:
SUM(If (brand name) AND (year 1 or year 2 or year 3), data)
Can you please give me suggestions on how to combine and/or’s within an if statement?
Thank you!
Hi Alicia,
It’s not clear whether the year 1, 2 & 3 values you want summed are in separate columns (one for each year) or all in the same column with the year indicator in another column.
So, depending on how your data is laid out you can use the SUMPRODUCT function or this variation on a VLOOKUP function that sums multiple columns.
No array function is necessary.
Kind regards,
Mynda.
Hello Mynda and Philip,
I just learned about this site about a couple of weeks ago.
I’ve been trying to learn Excel formulas/functions for so long now but sometimes books can be intimidating.
I like the way you presented this tutorial.
My problem basically with Excel was the syntax. With your tutorial, I now know how to accurately present them.
I have encountered VLOOKUP reference and several other different FUNCTIONS but it was on a pre-formatted Excel worksheet and all I need to do was input the data.
I remember how frustrating it was to look at the formula bar and not comprehend the thing you’re looking at.
With your help, I am now able to understand and explain how formulas operate.
Thank you very much and more power.
Regards,
-Cheryl-
Hi Cheryl,
Thank you for your kind words. I’m pleased we’ve been able to help you get to grips with formulas
Kind regards,
Mynda.
I Want =ex: Excel Sheet A2=EMB TOP=”0.5″,SG TOP=”1.0,”GSB TOP =”1.5″
Just Type EMB TOP result =0.5 please send me formula & Example
Hi Praveen,
You need a nested IF function.
Kind regards,
Mynda.
Hi Mynda
i have data like this
Name Total amt Card Bal NET Amt Paid Not Paid
to pay
xx 50 40 10 YES
xxx 50 60 -10
so in this data -10 means no need to pay that should be seen in paid row so please advice me a formula for this i use IF for YES or NO
Hi Chandu,
I’m not 100% clear on your question but I suspect a formula like this will work:
=IF(D1<1,”NO”,”YES”)
Where D1 contains -10 you are testing for a value less than 1. If it is less than 1 then enter ‘no’ otherwise enter ‘yes’.
I hope that helps.
Kind regards,
Mynda.
Hi Mynda
Sorry For the confusing question i got formula for this data may be Nested If funtion is help for me.if not i will get back to you.
Thanks for your support.
need help with a formula. Excel keeps saying there are too many arguments.
=IF(A1>=60,”GREEN”, IF((A1)<60,"YELLOW", IF(A1<=0,"RED"))) This works fine its the last part that is giving me grief. IF A1<=0 LOOK IN B2 AND IF BLANK RETURN RED IF NOT BLANK RETURN GREEN.
When I add this it just ignores the logic. IF(AND(ISBLANK(B1)=FALSE,A1<=0),"BLUE","RED")
Please help.
Hi Mo,
Nested IF statements evaluate in order. That is, the first IF evaluates and then if that isn’t true it moves on to the next IF and so on. The problem with your formula is that it never gets to your last IF statement because one of the first 2 are always true. That is, cell A1 is either >=60 or it’s <60. Even a blank cell is <60.
You will have to re-think your logic.
Kind regards,
Mynda.
http://s2.postimage.org/8cqppx4nt/Capture.png
=IF(G4>H4,(300-(G4-H4)),H4-G4)
What I want to do is if H4 is empty I want to keep it to 0 instead of 300.. And more thing is there anyway I can set maximum value for specific cell? I want to keep it under 300. It should not be more than 300.
thanks a lot…
Hi Neel,
Kind regards,
Mynda.
Hey Mynda Treacy,
Thank you very much. I really appreciate your work….
ONe more thing Like I said I want to keep the under 300 in cell, but is there any way I can set the maximum to 300 in H4? Like it I put under 300 or 300 it should let me put that number, but If i put above 300 hundred it should say false! or something!`
Hi Neel,
I don’t know where the figures in H4 are coming from. You can certainly put a formula in H4 but it has to reference somewhere else in the workbook or another workbook.
Kind regards,
Mynda.
Another thing, sorry It feels like I’m spamming your blog lol. Is there anyway I can add those numbers(difference between (H4-G4)+(H5-G4) together in the place where the number 244 in cell. Something like that without making any separate cells for first each and individual difference then add.
Hi Neel,
I’m confused now….I thought you wanted the formula I provided for cell G8 i.e. where you have 244 in your image?
Kind regards,
Mynda.
Never Mind.. One more question for you. Is there any way we can lock the row or column in excel? When you login to facebook the topbar stays there even if you scroll down, the blue top bar. I wanna do the same thing with dates. I wanna put all the dates in row and it should stay there even if I scroll down.
I found out how to freeze the rows and columns.
hi,
I’m preparing a reporting tool in Excel, but cannot get a formule to work. What I need to record:
A B C D E F
M/F AGE Males 0 to<2 Males 2 to<6 Males 6 to<12 Males 12to<14
I need to be able to record under column C-F if the person is male and 0-2 years old (under Coumn C), male and 2-6 years old (under column D), etc.
Can you please assist me urgently with a formula? This will really make my life much better this early in the year.
Hi Liezl,
Do you have a list of names with their corresponding ages? I am assuming you want the see the names show up in the right Age group correct? I am more than happy to assist you however, I will need specific details such as the column where the names and ages are located. You can tell me here or You can just send us an email via the helpdesk and attach your file there, along with a detailed explanation of what you want to see in the template. I’m sure our solutions will make your life much better. Have a nice day!
Thanks!
Mike
Hi Mynda,
Its really awsome to be a member of your excel family. You are doing a great work. I salute you for your marvelous work.
I just wish to know that can we put more than 7 arguments in nested if statement in excel 7 or more? because it can not be done in excel 2003.
Regards,
Shazif
Thanks for your kind words, Shazif
Yes, you can nest up to 64 IF’s in Excel 2007 onward, but you really should consider a different approach if you find you need more than 7.
More on nesting IF functions here.
Kind regards,
Mynda.
Thanks a ton…
And again I need to salute you
=IF(OR((AND(B5=9,B5=10,B5=11,B5=12),And(C5=12)),Family!$D$263,AND (C5,12)))
I know this formula is incorrect. Can we have two AND tests?
How can I fix it?
Hi Peggy,
I really don’t get what and tests/criteria you’re trying to get here. Please send this to
HELP DESK and please also send some mock data and what results you want to achieve with this formula.
I tried to figure this out but it’s really hard because you put another and in the false value of the IF function. I was thinking maybe you need
a nested IF. If that’s the case then I really couldn’t give you a temporary solution.
Cheers.
Carlo
The IF(And…) situation worked well for me and was what I was looking for but now I’d like to go one step further. I read some of the other posts and Vlookup may be easier for me but I’m now wondering if you can throw an OR into the mix along with IF and AND.
I teach high school Geometry and we’re currently working on rotations. I’m using Excel as a problem generator to come up with examples along with their solutions. When graphing, if you rotate a point 90 to the right (clockwise or CW), that’s the same as if you rotate the same point 270 to the left (counterclockwise or CCW). The manipulation of the x and y coordinates then are the same for both situations.
Here’s what I’d like to know…can I write a formula so Excel determines if it’s a 90 CW OR a 270 CCW?
On my spreadsheet I have one cell that indicates 90, 180 and 270. I have another cell indicates the direction (CW or CCW).
I’m using If(And) for 90 CW. Can I also add in an OR to evaluate if it’s also 270 CCW?
Can you please send your response to my personal email? Feel free to post this on the blog as well. Thanks.
Just after I submitted my query I started playing around with the formula and sure enough, I got it figured out.
Here’s what I’m trying to do:
A 90 degree clockwise rotation and a 270 degree counterclockwise rotation are the same so 90 CW AND 270 CCW.
Also, 90 degrees counterclockwise and 270 clockwise are the same so 90 CCW AND 270 CW.
The third situation is a 180 degree rotation (direction doesn’t matter).
In English, If the rotation is 90 CW OR 270 CCW it’s the same AND if it’s 90 CCW OR 270 CW it’s different AND if it’s 180 it’s different still.
In one cell then, here’s the formula that works:
=IF(OR(AND(B2=”90″,E2=”CCW”),(AND(B2=”270″,E2=”CW”))),(CONCATENATE(“(“,-G2,”, “,F2,”)”,)),IF(OR(AND(B2=”90″,E2=”CW”),(AND(B2=”270″,E2=”CCW”))),(CONCATENATE(“(“,G2,”, “,-F2,”)”,)),CONCATENATE(“(“,-F2,”, “,-G2,”)”)))
I’m using the Randbetween(1,3) to randomly pick the rotation: 1 = 90, 2 = 180 and 3 = 270. I’m also using randbetween (1,2) to randomly pick the direction of the turn: 1 = CW, 2 = CCW.
Hi Kris,
That’s great. Honestly, I am not good at Geometry and I was about to ask you
to send it through our Help Desk.
I’m glad you got it.
Cheers.
CarloE
thank that help me some but i still can not get my formula to work.
Here is where i am:
IF “cell” > 5000 take the amount on the cell subtract the 5000 and the balance calculate 6%. IF “cell” < 5000 enter zero.
Please help thank you
Hi Maria,
Please use this formula:
Cheers.
CarloE
Kindly assist me with the below problem
Cell a1 to a(n) represents months 1 to 60.
Cell b1.. bn represents new regional offices.
Cell c1..cn represents cost of new regional office which is 30 K per regional office.
Value in cell b1.. bn changes on certain assumptions
based on the value in cell b1..bn, cost 30,000 is added in cell c1..cn
This cost needs to be amortized in 12 months i.e 30000/12 to obtain cost for one month.
In cell b2 same cost may or may not appear based on the value in cell a2 (if value changes in a2, then this cost is multiplied with the difference of value in cell (a2-a1), otherwise value of previous cell appears in cell b2.
Required.
values appearing in cells c2..cn need to be added with previous value, exception is when value of c2 reaches 12 months,should stop adding; and so on till 60 months. Whats left is our prepaid expense
Can you setup a formula for the problem
Hi Abbas,
Please send this through our HELP DESK.
We need to see this concretely to avoid wastage of time. So I need a file that has your sample/mock data
that explains this all.
CHeers.
CarloE
I need help if working out a formula for these problems;
Q1)
10’000 LBS or less, a charge of $2.50 per 1’000 LBS
10’000 to 350’000 LBS, a charge of $3.25 per 1’000 LBS
350’000 LBS or over, a charge of $2.20per 1’000 LBS plus $600.00
(If any of the above charges falls below $20.00, let it indicate $20.00)
Hi Sheldon,
Try this formula:
Try this data-results:
9000 - 22.5
10000 -32.5
351000 -1372.5
Cheers.
CarloE
Hello,
I hope you can assist me with a formula that I’ve been trying to figure out.
I want a late fee imposed based on if a payment was received after a due date and if no payment was made.
ie =IF(ISBLANK(H3),” “,IF(H3>=DATE(2013,3,1),50,0))
The correct amount is displayed based on a date. However, if the cell is blank I receive an #VALUE! error in the “grand total” cell.
Any suggestions?
Thanks
Also if the cell is blank it does not imput the $50.00 late fee (the cell is blank) and the “grand total” cell reports the #VALUE!
Hi Kevin,
I think you wanted a formula like this based on your narrative:
Logic of the formula:
If H3 is blank
penalize 50
Elseif not blank
Evaluate if H3 is greater than jan 1 13
penalize 50
Otherwise
no penalty
Cheers.
CarloE
Thank you!! You are awsome. I’ve been recking my brain on this formula for several weeks. thanks again,
Kevin
Hi Kevin,
On behalf of Mynda, You’re Welcome.
Cheers.
CarloE
We are using a template time sheet for our employees and are tweaking it to fit our needs. The time sheet calculates the hours worked when the employees type in their in and out times (up to 3 times per day). It uses this formula to do this: =ROUND(IF((OR(B12=”",C12=”")),0,IF((C12<B12),((C12-B12)*24)+24,(C12-B12)*24))+IF((OR(E12="",F12="")),0,IF((F12<E12),((F12-E12)*24)+24,(F12-E12)*24))+IF((OR(H12="",I12="")),0,IF((I12<H12),((I12-H12)*24)+24,(I12-H12)*24)),2). I would like to have it round the total hours to the nearest quarter. Ex. If we have a total of 7.92 hours in a day, it would need to be rounded to 8 hours; if the total was 6.18, it would need to round to 6.25 hours. Can you help?
Hi Suzanne,
You have got quite a complicated IFS right here. It would help, and I would gladly appreciate it, if you
can send your file via HELP DESK so we can be a little concrete about it.
Cheers.
CarloE
Dear Mynda,
would you be so kind to help us with IF formula. We have the following issue when the result in column is #N/A can we create this kind of IF formula =IF(AB2795″#N/A”;”Booked”;”Sent to AP Scan”) if yes please send us some hints because it does not work.
Many thanks a lot in advance, Andrea
Hi Andrea,
Try this:
Cheers,
CarloE
i am kindly seeking help from u guys concerning the IF function..this is the question:
I AM an intern at Supermarket. My boss has tasked me with creating a spreadsheet that will be
used by the cashiers to carry out calculations on purchases by the customer. The spreadsheet will have a table for the list of products as well as the table for the items being bought.
so was given the following clues:
he point of sale will be on a new worksheet called last_four_digits_of_id_Point of Sale E.g If your id is
201203555 the worksheet should be 3555_Point of Sale. There will be two tables in this sheet, one for the
cashier i.e Chashier Table; and one for list of items i.e Product List. Your ID (IS 201205507), surname and Faculty should be
written in C3, D3 and E3 respectively.
Cashier table: should occupy cell range B2:H29. It should have a bold outline. The logo should be in cell C5
and should be merged with other cells all the way to G7. It should be Calibri font of size 24. The label
“TOTAL” should be in D9:E10. The total should be calculated in F9:G10. Both should be in Calibri font of
size 24 and bold.
Within the cashier table the Sale Products table should span C15:G26. The “CASH TENDERED” label should
be in C29:E30. It should be Calibri font of size 20. The cash tendered field should be in F29:G30. It should
be Calibri font of size 20 and bold. The cashier will type the code of the product and using proper functions
the spreadsheet will then calculate the following:
In C15 the cashier will enter the code of the product being bought by the customer. Quantity in
D16 will automatically set to 1 plus the last digit of your id. Use IF function to calculate this. The
cashier can change it.
In E16 use VLOOKUP function to retrieve the name of product from the Product List table
In F16 use VLOOKUP function to retrieve the item price from the product list
Similarly enter the product code and the quantity in the table and other values are retrieved from
the product list table and the table is completed.
In G9:G10 (merged cells) Calculate the subtotal for the item
Calculate the total so far
The cash tendered amount should be calculated using the MOD function to be the last four digits of
the ID.
If the amount tendered is less than the total, the change field should show “NOT ENOUGH CASH”
otherwise it should show the proper change.
Ensure that all values are valid.
Ensure that all currency values are formatted to P
i dont realy understand this task…i have made the table as i was asked but now i dont understand if the code should be entered manualy….so tried calculating the quantity using IF fuction but i get 8… this is hw i tried to do it….:=IF(AND(K12<K13,K13<K14),(7),0)…so please tell me were i went wrong
Hi Knodge,
I suppose you have sent this via Help Desk right?
Please don’t reply here anymore.
Cheers,
CarloE
I tried using the excel if and function but am having trouble. I want to create an aging sheet. If an item is above 30 day and less than 61, then I want the amount past due to populate in that column, etc. Here’s the formula that I used =IF(AND(G2>30,G2<61),P2=J2,”Nil”). It's coming back with an invalid name error. Can you help me?
Thanks, Debbie
Hi Debbie,
I’m not sure what the data looks like that you’re working with but your formula doesn’t look right anyway. In English your formula reads:
If G2>30 and G2<61 then P2=J2 otherwise Nil.
I think the P2=J2 should either form part of the AND statement or be amended. You might be best to send us your file via the help desk so that we can see what you’re working with.
Kind regards,
Mynda.
Need help on how to use the following with the if command.
The investment plan calls for the company to match an employee’s investment, dollar for dollar, up to 3%. Thus, if an employee invests 6% of his or her annual salary, then the company matches the first 3%. If an employee invests only 2% of his or her annual salary, then the company matches the entire 3%.
so how would i do an if command to figure out the company investment percentage on all employees.
Hi Jeanine,
I’m confused about this one. Granting there’s no error in your instructions,
this is how your formula should look like.
A1 – investment
B1 – salary
but if you mean ‘company matches the entire 2%’ in the last sentence of your second paragraph then
this is how this should look like:
Cheers,
CarloE
My column A contains values between 0 and 180. In another column (say column B), I want the values be named as follows: if the value is between 0 and 30 – name it as “+ve low”; values between 31 and 60 as “+ve moderate”; between 61 and 120 as “high”; between 121 and 150 as “-ve moderate” and between 151 and 180 and “-ve low”. Do I use IF, AND and OR function? What will the function (formula) look like?
Heaps thanks for your help.
Hi Jeet,
I’d use a VLOOKUP on a sorted list for this.
Kind regards,
Mynda.
Thanks xl Guru
Good afternoon, I hope you are well
I’m trying to get the following values with just 1 formula
Priority_1 and URGENT with more than 5 days in process put “Yes” in Issues, Priority_1 and URGENT less than 5 days put “No” in Issues.
Priority_2 and Priority_3 with more than 10 days in process put “Yes” in Issues,Priority_2 and Priority_3 less than 10 days put “No” in Issues.
Priority_4 with more than 20 days in process put “Yes” in Issues, Priority_4 with less than 20 days put “No” in Issues.
A B C
1 Issues Days in process Priority
2 Yes 114.637882 PRIORITY_4
3 Yes 55.762083 PRIORITY_4
4 Yes 55.749282 PRIORITY_4
5 Yes 55.743484 PRIORITY_4
6 Yes 55.739954 PRIORITY_4
7 Yes 55.738646 PRIORITY_4
8 Yes 55.737535 PRIORITY_4
9 Yes 55.734699 PRIORITY_4
10 Yes 55.681088 PRIORITY_4
11 Yes 50.648194 PRIORITY_4
12 Yes 50.64669 PRIORITY_4
13 Yes 50.645289 PRIORITY_4
14 Yes 50.644028 PRIORITY_4
15 Yes 28.645718 URGENT
16 No 16.81272 PRIORITY_4
33 Yes 14.80125 PRIORITY_1
35 Yes 13.675579 PRIORITY_3
36 Yes 13.672824 PRIORITY_3
52 Yes 9.683206 PRIORITY_1
53 Yes 9.682685 PRIORITY_1
54 Yes 9.681991 PRIORITY_1
55 Yes 9.681701 PRIORITY_1
56 Yes 8.76544 PRIORITY_1
57 No 7.739803 PRIORITY_2
58 Yes 7.660613 PRIORITY_1
59 No 6.776563 PRIORITY_2
60 No 6.775602 PRIORITY_2
61 Yes 6.739711 PRIORITY_1
62 No 2.98912 PRIORITY_2
63 No 2.984641 PRIORITY_2
64 No 2.981806 PRIORITY_2
65 No 2.697269 PRIORITY_1
66 No 2.69662 PRIORITY_1
Hi Felipe,
=IF(AND(OR(C2=”Priority_1″,C2=”Urgent”),B2>5),”Yes”,IF(AND(OR(C2=”Priority_2″,C2=”Priority_3″),B2>10),”Yes”,IF(AND(OR(C2=”Priority_2″,C2=”Priority_3″),B2<10),”No”,IF(AND(C2=”Priority_4″,B2>20),”Yes”,”No”))))
Kind regards,
Mynda.
I have multiple logic statements with the same outcome. Can I combine them into one formulae?
This is my table:
Reference Method Result Test Method Result Agreement Category
Resistant Resistant Agreement
Resistant Intermediate Minor error
Resistant Susceptible Very major error
Intermediate Resistant Minor error
Intermediate Intermediate Agreement
Intermediate Susceptible Minor error
Susceptible Resistant Major error
Susceptible Intermediate Minor error
Susceptible Susceptible Agreement
I looking at the minor error. So I wrote the follow formulae:
=IF(AND(M2=”S”,H2=”I”),(OR =IF(AND(M2=”I”,H2=”S”),(OR =IF(AND(M2=”R”,H2=”I”),(OR =IF(AND(M2=”I”,H2=”R”),”MINOR ERROR”,”")))))))
Thanks
Hi Dave,
I can’t really make sense of your formula without seeing the Excel file. If you’d like to send it to me via the help desk I can take a look.
Kind regards,
Mynda,
Hi Mynda
i’ve written individual IF statement, but i want to consolidate them to one formulae: for example:
=IF(AND(M4=”I”,H4=”S”),”MINOR ERROR”,”") ie if new method is “intermediate” and reference method is “sensitive”, if true then it a “minor error”, false, then blank.
This formula works, but i have write 4 ‘minor errors’, 1 “agreement error” and 1 “very major error” and 1 “major error” formulae, which would mean I have 1 columns to evaluate 2 columns. If could find a synthax to group the “minor error” together that would be great.
Thanks.
Dave
Ive sent the file.
Hi Mynda
I found a work around by creating a list of outcomes and using the formulae
IF(AND(A1>0,B1=”y”),E1,”"))
I can expand the formula. It worked.
Thanks for your help.
Dave
Great. Well done, Dave
Thanks for letting me know.
Code Qty Price Code Qty Price
A B C | Brochure A B C
1 ACH22 30 | ACH22 5 7.00
ACH22 10 9.00
ACH22 30 15.00
Can you help me with this problem:
if (A1=Brochure!Code AND B2=Brochure!Qty)
then C = Brochure!Price
Hi, trying to indicate duplicate numbers between different rows and used the following: =if(or(f3=f4,f3=f2),1,0)
Formula created in newly inserted column G (to the right of F) on row 3…. not working. Advice please?
Hi Neels,
I’m not sure without seeing your data. You can send it to me via the help desk and I’ll take a look.
Kind regards,
Mynda.
Hi Marty,
I’m not sure I understand your question. Perhaps the data isn’t displaying correctly. It might be clearer if you send me an example file so I can see exactly what you want and where.
Kind regards,
Mynda.
Can you help me with this problem:
=IF (A3=Brochure!Code$A$3 AND B3=Brochure!Qty$B$3)
then C3 = Brochure!Price
****** Ticket Was Created ******
Sheet1
CODE QTY PRICE
ACH22 30
————————-
Brochure Price List
CODE QTY PRICE
ACH22 10 7.00
ACH22 10 9.00
ACH22 30 15.00
———————–
Hi Marty,
You can use this formula:
Where A7 contains ACH22 and B7 contains the quantity you’re matching.
Entered as an array formula with CTRL+SHIFT+ENTER.
I wrote a blog post about how to do this with VLOOKUP last week.
Kind regards,
Mynda.