Excel IF AND OR functions 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)
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)
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.
Download the Workbook
Enter your email address below to download the sample workbook.
Excel IF AND OR Practice Questions
IF AND Formula Practice
In the embedded Excel workbook below insert a formula (in the grey cells in column E), that returns the text 'Yes', when a product SKU should be reordered, based on the following criteria:
- If Stock on hand is less than 20,000 AND
- Demand level is 'High'
If the above conditions are met, return 'Yes', otherwise, return 'No'.
Tips for working with the embedded workbook:
- Use arrow keys to move around the worksheet when you can't click on the cells with your mouse
- Use shortcut keys CTRL+C to copy and CTRL+V to paste
- Don't forget to absolute cell references where applicable
- Do not enter anything in column F
- Double click to edit a cell
- Refresh the page to reset the embedded workbook
IF OR Formula Practice
In the embedded Excel workbook below insert a formula (in the grey cells in column E) that calculates the bonus due for each salesperson. A $500 bonus is paid if a salesperson meets either target in cells C24 and C25, otherwise they earn $0 bonus.
Want More Excel Formulas
Why not visit our list of Excel formulas. You'll find a huge range all explained in plain English, plus PivotTables and other Excel tools and tricks. Enjoy 🙂
Alex says
Hi I have a problem that basically says that if B7=0 and B8>20000 then it’s a ‘yes’. If it’s false then I have to insert a nested if function (using AND) to determine if B7>0 and B8>200000 (when which case it’s a ‘no’ if false)
Basically it’s asking if the goal of 20000 was met if there were no fundraisers for the months of Jan, Feb, and April. But if there were fundraisers in the months of March and May then the goal of 200000 had to be met.
I’m just very lost and would love some help!
Catalin Bombea says
Hi Alex,
Try:
=IF(AND(B7=0,B8>20000),”Yes”,IF(AND(B7>0,B8>200000),”>200000″,”No Condition met”))
Cheers,
Catalin
Wil says
COLUMN A is a list of names, ROW 1 is a list of dates, intersecting cells have a value such as X: Can I write a formula to search by row 1 for the coordinating information from column A if the X value is present? For example, If A2 contains “Billy Bob”, A3 contains “Phil”, A4 contains “Charles” and B1 contains “April 3”, and B2, B3, B4 (the intersecting coordinates) has “X”, I want to be able to search by the B1 value with the result of all corresponding A values. So search: April 3, Result: Billy Bob, Phil, Charles. ANY HELP IS GREATLY APPRECIATED!
Mynda Treacy says
Hi Wil,
I’d use a PivotTable. Put the names in the row labels and drag the date you want the list for into the values area. This will count the Xs against each name, then you can filter out any zero results.
If you get stuck please post your question in our Excel forum where you can upload a sample Excel file that we can put a solution into.
Mynda
Wil says
THANK YOU SO MUCH! I will try that!
Rachel says
Hi,
I am doing a leave tracker to record all the employees in a year.
There are 4 categories of staff with different leave entitled as well years of services.
Group M
2years is 18days, >5years is 22days
Group E, Group A
2years is 16days, >5years is 20days
Group C
2years is 14days, >5years is 18days
I need the excel will automatically define the correct leave entitled with group data
Is there any good formula to work up?
Seek for help, thank you.
Catalin Bombea says
Hi Rachel,
Try this formula:
=INDEX(CHOOSE(INDEX({1,2,3},MATCH(C1,{0,2,5},1)),{0,0,0,0},{14,16,16,18},{18,18,20,22}),MATCH(D1,{“C”,”E”,”A”,”M”},0))
In cell C1, it should be a number representing the number of years, you can use a formula like this: =DATEDIF(A1,B1,”y”)
In D1, you should have the group name: C, E, A or M.
Cheers,
Catalin
jo says
I need to have 5 emails sent from a worksheet with more than 50 attachments. The workbook would have 77 worksheet with this requirement.
And I want to share this with other people so that they can use it without any need to edit the code to personalize for them.
Mynda Treacy says
What’s your question, Jo? Perhaps this tutorial will help: Create a PDF from Excel and email it with Outlook.
SPR says
Hi,
Anyone can help me on this.
Current formula i am having now is below.
=ROUND(IF(AND(B6=”E”,C6=”BTT”),SUM(G6,I6)*5%,SUM(G6,I6)*3%),0)
Now i have a new condition. ie
A B C D E F G (E*F) H I(E*H) J
O 2 6000 3 18000 5 30000 480
O 1 3000 1 3000 2 6000 110
in “J” i need SUM of (1% OF “G” OR 50*”D” WHICHEVER IS HIGHER)+(1% OF “I” OR 50*D WHICHEVER IS HIGHER)
I need this criteria to be included in the above formula.
Catalin Bombea says
Hi SPR,
Can you please uplload a sample file with your sample data and a manual result for that sample data? Use our forum to upload. (create a new topic after sign-up)
Catalin
Vinay Chavan says
Merit List Decide the Eligibility of Student for the admission
Direct admission: if Total Marks is >600 & Sum of Maths Marks & science Mark is >=180
1st Merit List: If Total Marks is >=500 & Maths Marks >=55 & Science Mark >=55
2nd Merit List: If Total Marks is >=400 & Maths Marks >=55 & Science Mark >=55
3rd Merit List: If Total Marks is >=300 & Maths Marks >=50 & Science Mark >=45
Else not Eligible
Kindly solve the problems
Catalin Bombea says
Hi Vinay,
Try this formula:
in column A: total marks, column B: Math marks, column C: Science marks.
ahsan says
sum, count, average or any other formula not working on this below mention data. Furmulas worked when we rearrange cell with the help of F2 function key or double click on cell. Have any other option to rearange complete sheet or worksheet imediately?
Mynda Treacy says
Hi Ashan,
I’m not sure what you mean, but maybe you have calculation set to manual? Check the Formula tab > Calculation options.
Mynda
maro says
IF both A1+B1 7 but 9 but 20, return $200
I can not write it in Excel
Mynda Treacy says
Hi Maro,
I’m not sure what you mean. Are you saying if A1+B1= 7, and are less than 9, then 20….???
Perhaps you can post your question and a sample Excel file on our forum we’ll be able to see what you’re trying to do.
Mynda
Uzma Razzaq says
Some assistance needed please..
i need a formula for excel sheet. As i have record of fee of school students.
Condition: If more than one child of a parent studying in same school they will be granted fee compensation as
100% fee for first child
75% fee for Second child
50 % for all others & so on
if one child left the school, 100% will replaced by 75% &
75% will be replaced by 50%
what formula can i use for automatic replacement, pleas help me in this regard
Mynda Treacy says
Hi Uzma,
It’s difficult to say without seeing the layout of your Excel file. e.g. do you have rows containing the names of each student and a corresponding column that has the count of family members for that student? If so it would be relatively easy, but if you don’t have that count then you need to calculate that first. Let’s assume you have the count of family members in column B beside each student listed in column A:
If you still have questions please post them in our Excel forum where you can also share the Excel sample file.
Mynda
Devon Chipman says
Hello,
I have a formula that is giving me a headache. I am trying to do this:
Example:If cell B2=1 then cell C2 equals .043 or if cell B2=2 then cell C2 equals .087.
I am trying to type a number in B2 (1,2,3,4) and then have C2 put in the number that it is tied to or equal to.
Catalin Bombea says
Hi Devon,
Try this formula in cell C2 please:
=IF(B2=1,0.043, IF(B2=2,0.087,”Other value in B2″))
Cheers,
Catalin
selvaraj says
Landing Date & Time Delivery Taken Date & Time Del-Lan Result
3-4-18 22:00 3-6-18 12:01 38:01:00 Greater 24 Hours
3-5-18 10:30 3-6-18 12:01 25:31:00 Greater 24 Hours
3-7-18 4:14 3-7-18 6:35 2:21:00 LESS4
3-16-18 9:00 3-16-18 14:25 5:25:00 4 TO 8 HOURS
3-15-18 22:45 3-16-18 7:15 8:30:00 8 TO 12 HOURS
i did formula like this, but answer is not getting for greater 24 hours,
=IF(AI38<TIME(4,0,0),"LESS4",IF(AI38<TIME(8,0,0)," 4 TO 8 HOURS",IF(AI38<TIME(12,0,0)," 8 TO 12 HOURS",IF(AI38<TIME(24,0,0)," 12 TO 24 HOURS"))))
pl help me to share formula
Catalin Bombea says
Hi selvaraj,
Date and time problems are very sensitive, there are many factors that can affect the calculation.
I think it’s best for you to upload a sample file with your sample data, this way we can analyze the formats you have and provide a functional solution.
Please use our forum to create a new topic and upload the file.
See you on forum.
Catalin
HABEEB RAHMAN says
i have one doubt
i have one product 3600 PCS
last purchase date is 1st Jan 2018 -2300 pcs
last before 13th November 2017 -2300 pcs
whats is formula or program to calculate this ageing in excel
Catalin Bombea says
Hi Habeeb,
Not sure what you want to achieve. Are you trying to calculate the difference between nov 2017 and jan 2018? This should be a simple deduction between those 2 cells.
Can you upload a ample file to our forum and clarify what you’re after?
Thanks
Catalin
Steve says
Hello,
i am trying to do this formula
=IF(B3=1,AND(C3=5,15,PRODUCT(B3:C3)))
what i want to do is if cell B3 is equal to 1 and cell C3 is equal to 5, then i want it to return 15, Else i want Product of cell B3 and C3
Is this possible, if so What am i doing wrong?
Catalin Bombea says
Try this:
=IF(AND(B3=1,C3=15),15,B3*C3)
Steve says
That is Awesome, but i have 1 more condition to add in:
=IF(AND(B3=1,C3=5),15,OR(IF(B3=2,C3=5),15,B3*C3))
same thing, but i need to get the same result 15, or product if B3=2
am i doing too many steps?
if B3 is either 1 or 2 and C3 is 5, i need the result to be 15, or the product of the 2 cells
Catalin Bombea says
Then it should be:
=IF(AND(OR(B3=1,B3=2),C3=5),15,B3*C3)
There are many ways to write this, here is another version:
=IF(OR(B3*C3=5,B3*C3=10),15,B3*C3)
In this case, it does not matter which cell has the 1 or 2 and which has the 5, so you should know better which one fits to your needs.
Steve says
You are Awesome!!!!, have a great day
Catalin Bombea says
You too 🙂
Cheers,
Catalin
Josh says
Hi,
I’m trying to make a spreadsheet that can automatically assign a time slot to candidates that are successful in a test. For instance, Pass Test A then receive time slot. However, the difficulty comes in doing this for multiple candidates. For instance when the next person is entered as Pass Test A they would receive a timeslot 10 mins after the previous candidate. Is this something that can be automated?
Catalin Bombea says
Hi Josh,
There is always a way, sometimes not exactly as your expectations, there may be design changes if something is not possible.
Can you upload on our forum a sample file so we can see your structure? (open a new topic after sign-in). A theoretical answer might not be what you need, we can try to find a personalized solution for you. Don’t forget to add as many detail as possible to clarify things, a fast response usually means that you did a good job explaining what you need 🙂
Regards,
Catalin
Pat says
I need assistance. In the below chart if cells in column “A” equal 4656 AND the cell in column “G” equals 50, then I need to get the sum of only the cells in column F that correspond. I need it to look at the whole column of each.
A B C D E F G
1 8623 RHODES AARON 8623012287 3/1/2018 25.54 50
2 8623 HERRIN AARON 8623012288 3/1/2018 84.86 50
3 8623 JACKSON DYLAN 8623012286 3/1/2018 10.34 50
4 8623 RHODES DYLAN 8623012287 3/1/2018 47.88 60
5 8623 RHODES FRANK 8623012287 3/1/2018 31.92 80
6 4653 HEAD GLENDA 4653009783 3/2/2018 115.34 50
7 4653 HEAD GLENDA 4653009783 3/2/2018 154.81 60
8 4656 JOHNS GLENDA 4656017111 3/2/2018 388.59 50
9 4656 HART JADE 4656017109 3/2/2018 57.29 50
10 4656 JOHNS JERRY 4656017111 3/2/2018 477.12 80
11 8623 SIMMONS LATISHA 8623012293 3/2/2018 61.15 50
12 8623 MITCHELL LATONYA 8623012281 3/2/2018 28.8 50
13 8623 HARPER LATONYA 8623012292 3/2/2018 9.83 50
14 8623 SIMMONS PENELOPE 8623012293 3/2/2018 65.52 60
Catalin Bombea says
Hi Pat,
You can try this formula:
=SUMPRODUCT((A1:A14=4656)*(G1:G14=50)*(F1:F14))
Johnson says
I am putting together an inventory spreadsheet. I need 1 case to equal 24 units. (I’m inputting cases of beer). What formula do I use?
Mynda Treacy says
Hi Johnson,
I suspect there are some details missing from your description, because at its most basic the formula is:
=1*24
If that isn’t what you were after please post your question and sample Excel file on our Excel Forum where we can better understand what you need.
Mynda
DJ says
what is the formula to calculate to see if 3% is lesser than 30%
Catalin Bombea says
Hi DJ,
Not sure what you mean, 3% will always be smaller than 30%. Can you give us more details?
Catalin
Kipp Play says
=IF(C5>C3,”local”,IF(AND(C5>=5534,C5C3=local. Then I need two more answers, if C5>E3 = okay, but if C5<=E2 =More.
Catalin Bombea says
Hi Kipp,
Try this:
=IF(C5>C3,”local”, IF(AND(C5>=5534,C5<>C3),”local”,IF(C5>E3,”okay”, IF(C5<=E2,"more","other")))) Nested ifs require a specific order of conditions, when the first true condition is met, the rest will not be evaluated.
Kipp Play says
=IF(C5>C3,”local”,IF(AND(C5>=5534,C5C3, then place local in the filed. And if C5 is >=E3, then place Okay in the field, but if C5 is <= E2 then place Needs more in the filed.
I just can't seem to get the formula to work the way that I need it.
mohasin says
in o26 value is 5000 and next cell o27 value is fix which is 10000 now i know that if cell o26 value is less than 10000 came cell o26 value but cell o26 value is more than 10000 than came only fix value 100000 plz tell formula
Catalin Bombea says
Hi ,
Please use our forum to upload a sample file, your request is not clear, hopefully a sample file will clarify the request. Create a new topic after you sign-in to upload the file.
Regards,
Catalin
Subash says
Hi Mynda,
Thanks for all the pains you and your team take to respond to queries of people visiting your site.
Regarding IF AND function, I am having a problem, kindly assist.
I have Nikhil in A1
in D1 I have put this formula
=IF(AND(A1=”Nikhil”,B1=0),”good”,IF(AND(A1=”Nikhil”,C1=4),” very good”))
even though I do not enter 0 in B1 I am still getting the result “good” in D1 whilst as I should get “very good” as I have put 4 in C1
I know excel treats 0 as some value, which I cannot remember.
Kindly assist as I have a list of 2600 rows to work with. -:)
Best regards,
Subash
Catalin Bombea says
Hi Subash,
If B1 is empty, excel will consider it 0, so you need another check:
=IF(AND(A1=”Nikhil”,B1=0, Len(B1)>0),”good”,IF(AND(A1=”Nikhil”,C1=4),” very good”))
Or, put first the other condition, there is a specific behavior with nested IF statements: if a condition is met, the rest of IF statements will not be evaluated.
=IF(AND(A1=”Nikhil”,C1=4),” very good”),IF(AND(A1=”Nikhil”,B1=0),”good”,”other case”))
Subash says
Thanks Mynda,
You guys are the greatest.
When I added the LEN function it worked. But it doesn’t work when I shifted the conditions as you suggested.
Thanks and best regards,
Subash
Catalin Bombea says
You’re welcome, glad to hear you managed to make it work.
Catalin
Judy says
I need help in getting a formula to help with the following. In my spreadsheet I have Hours earned for going to monthly training in cells D2 through O2. I also have the number 1 for each member if they attended the monthly meeting. I want to add the numbers in Cells D2 thought O2 ONLY if there is the number 1 in cell D5 through O5. An so on for the 75 members in the group. I want to put the answer in cells Q for each member.
I tried =SUMIF(D2,O2,D5,O5,1)
and I tried SUMIF(D2,O2,D5,O5,”1″)
Catalin Bombea says
Hi Judy,
Can you please upload a sample file on our forum? (create a new topic)
It will be easier to provide a functional formula for your specific data structure.Thanks
Catalin
Carel van der Merwe says
Good day,
I need your assistance please. I have two columns J & K which my information are in. “07/06/2017 05:06 PM” & “11/01/2017 02:08 PM” you can see the information in each cell contains the date as well as the time.. What i want to achieve is a true or false statement based on the time in each cell to calculate whether the difference between the two times are less than four hours of more than four hours difference. Meaning if less than 4 hours difference = true, more than 4 hours difference = false.
I hope and trust that my explanation makes sense. THANK YOU IN ADVANCE!
Catalin Bombea says
Hi Carel,
If you want to ignore the dates and work only with times, you can try this:
=ABS(A1-INT(A1)-(B1-INT(B1)))>4/24
A time value will always be less than 1, as a day is an integer, one hour will be 1/24.
A1-INT(A1) will extract only the time fraction, ignoring the days, so it’s just a matter of deducting the time fractions and compare them with 4/24 (4 hours, converted to decimal system).
Catalin
Carel van der Merwe says
Hi Catalin,
Thank you very much for you very much for your prompt response. I would like to take the dates into consideration as well seeing the i need to calculate the 4 hour difference =true/false? Is that possible?
So i basically need the calculation to be done on a calendar basis including time running on a 24 hour time format (am & pm) (“,)
Hiya did you get my response?
Catalin Bombea says
Hi Carel,
Then simply deduct those 2 cells with dates:
=B1-A1>4/24
Carel van der Merwe says
You’re a star! Thank you!
Catalin Bombea says
You’re welcome 🙂
Sachin Vahile says
IF THE GIVEN VALUE IN A CELL IS MORE THAN 3500 THEN DEDUCT 12 OR DEDUCT 6 FOR ANY VALUE
Catalin Bombea says
Hi Sachin,
Try this:
=A1-IF(A1>3500,12,6)
Khan Fakhar says
Dear i need help with the following formula , as i am making an estimation sheet for a paint company. for example a paint bucket is sufficient for 80 meter, if we take a job of 50 meter we have to use the same bucket and there will be wastage of 30 meter paint so we have to charge it to client. what i want is
if the value in the cell =if(i3 is greater then 0.1 but less then or equal to 1 then the value in cell E3 =1 ,but the i3 is greater then then 1 then its actual value to shown in E3 )
hope you can help me with this as i have be trying to figure it out since 3 days but i couldn’t make it.
regards
Catalin Bombea says
Hi Khan,
Try this formula:
=IF(AND(I3>0.1,I3< =1),1,IF(I3>1,I3,I3))
Sahed says
how to use in excel formula I’ll get 2,000 taka for every 3,000 taka
Sahed says
i ll using this formula 12 column how can i use ?
Catalin Bombea says
Depends on what you need to do. What do you have in those 12 columns? AND conditions, or IF conditions?
Please prepare a sample file with a manual result and detailed explanations, we can help only if the problem is clear. Use our forum to upload a file, create a new topic after signing-up.
Catalin
Catalin Bombea says
Hi Sahed,
Try:
=ROUNDDOWN(A1/3000,0)*2000
Parveen Dhariwal says
I want to put formula in excel like we have some students out of which some only understand english but not able to write and speak.
some understand and write but not able to speak.
some are able to read, write and speak all three.
then we have to categorized if someone is able to speak write and understand then he comes under excellent category.
If he understand and write comes under good category.
if he understand only then comes under average category.
if he is not able to speak write or understand then comes under poor category.
Kindly help in categorizing data as per requirement.
Catalin Bombea says
Do you have 3 categories or 4? You mentioned Read, Write, Speak and Understand.
Each student should have 3 columns (or 4, if you have all those 4 attributes mentioned above), with values of 1 and 0. If a simple sum from those 3 columns returns the result 3, it will be “Excellent”, 2 will be “Good”, 1=”Average”, 0=”Poor”.
You can try a simple formula to return those categories:
=Index({“Poor”,”Average”,”Good”,”Excellent”},Match(Sum(B2:D2),{0,1,2,3},0))
This approach will return Good if the student has 1 ability, no matter which one, can be Read, or Write or Speak.
For more restrictive classification, you can use nested IF’s:
=If(And(B2=1,C2=0,D2=0),”Average”,If(And(B2=1,C2=1,D2=0),”Good”,”Other Case”)). You can add more cases until you cover all situations.
I assumed that in column A is the student name, column B is Read, column C is Write, Column D is Speak.
Sure says
Hi i want a Coding that Cell J3 should be displayed as True if I3 < I4 to I9 I tried with this coding =If(I3<(I4:I9),"TRUE","") No rsults comes help me to solve this…
Mynda Treacy says
Hi Sure,
I’ll assume that I3 must be less than all of the values in the range I4:I9. If so, you can use this formula:
Jean says
Hi, i having situation as below. can the formula in 1 cell with below 2 IF situation.
in fty date later than produce date, can cell show late how many days, no late than show ok
if in fty date later than produce date <5 days and the production line start with K, can the cell show late how many days – ok
Mynda Treacy says
Hi Jean,
Sorry, it’s a bit difficult to follow your example. Can you please post your question on our Excel forum and upload an example Excel file so we can see your question in context.
Thanks,
Mynda
Melinda Koite says
HI! I think I need to use the IF function but please tell me if I’m wrong.
I’m making a spreadsheet for storage units. And I have columns with the months. I want to be able to put an x under the column month and it equal the amount of the storage unit rent in another column.
Mynda Treacy says
Hi Melinda,
Possibly, but it’s difficult to say without seeing an example Excel file. Are you able to post your question on our Excel Forum where you can upload an example file so we can help you with a specific solution?
Mynda
lamees says
If the amount is > 750, give a 10% discount.
Mynda Treacy says
Hi Lamees,
Try this, where A1 contains your ‘amount’:
In English it reads; IF the amount in A1 is greater than 750, then take 10% off A1, otherwise the amount in A1.
Mynda
G.Harbs says
I need some help on a statement. I recently made several changes to the way we track our estimating and I need to now track every project we bid as a count. Every week, we could bid on over 30 projects ranging from $20,000.00 to $2,000,000.00. I need to figure out a simple formula to track our bids as a count. In other words, I need to know how many bids we are submitting that are $0-100K, 100k-250k, 250k-500k, 500k-1M, $1M-$2M, and >$2M. Thank you for the help!
Mynda Treacy says
Hi Gus,
I’d use a PivotTable for this. If you need pointers please post your question and some sample data in an Excel file on our forum and I’ll show you what I mean.
Mynda
Paul Smith says
Excellent!! Very helpful.
Allen says
All conditions are met in And If statement which should give True response, but it gives the False response.
‘=IF(AND($D66=”STPC”,$D66=”D-STPC”,I66>=10000),$I66/10000*3,5)
The STPC represents product type in column D, column I represents qty produced. If more than 10,000 are produced, since both logical conditions have been satisfied, should choose true, but it defaults everytime to answer 5.
Example: Row 71 = product type of STPC, and has a qty of 187000 in Column I which should give an answer of 18.70 * 3 = 56.10. But it defaults to 5.
My other And If statements work in other columns, but it is not working in column AN.
Mynda Treacy says
Hi Allen,
There will be some anomaly in your data that’s causing the FALSE result. I’d have to see it to tell you why. Can you share the your question and file on our Excel Forum?
Mynda
samy says
Thank you so much for the above explanation, however i have one scenario where i need help.
Excel Column A1. Excel Column B1
Price Highest price/ lowest price
1 NO
2
3
4
5 YES.
what i need is to populate yes & NO automatically wherever the price is high or low.
please help me with the formula on this.
Mynda Treacy says
Hi Samy,
Assuming your values are in cells A2:A6, you can use this formula:
Mynda
Gk says
Hello
If a person to get based on allocation,his billing type and he worked in full month so what function and what can I put the formula in excel…
Alloc:- allocation (based on )
B:- Billable
Nb:- non billable
Eg.
Name Alloc B/ NB start date enddate
ABC 100% B. 1/1/2017, 31/1/17
Plz help me
Awaiting ur reply
Thank you.
Mynda Treacy says
Hi Gk,
Can you please post your question and a sample Excel file on our forum as it’s a bit difficult to understand from your description alone.
Thanks,
Mynda
RAJESH JOSHI says
CELL VALUE IN PERCENTAGE A1 IS 50.00 SET CRITERIA IS BELOW MENTION
75-“3.00”
PLEASE URGENT REPLY …..THANKING YOU
RAJESH JOSHI says
A1-50.00
A1 75 CELL VALUE IS “3.00”
Catalin Bombea says
Hi Rajesh,
Assuming that A1 is a percentage, you should be able to use a very simple formula: =IF(A1=0.75,3,”This will be displayed for any values other than 0.75 in A1″)
Catalin
Jim Baker says
I NEED TO WRITE A FORMULA THAT IS SIMPLE BUT FOR SOME REASON I CANT GET IT RIGHT. scenario. cell C1 has total hours worked. I want cell A1 to tell me that if C1 is greater than or equal to 40 than put 40 in A1. I also want A1 to tell me if C1 is less than 40 than put the number in C1 in cell A1
Mynda Treacy says
Hi Jim,
Please post your question on our Excel Forum and include your example Excel workbook. Time calculations can be tricky so we need to see what format you’re entering hours etc. in order to help you.
Mynda
Sharlene Harding says
Hi, Could I get help to fix this formula please
=IF(K4,”Y”)COUNTIF(B4:G4,”>0″)
Basically if a column has a “Y” count the amount of columns if the value is more than 0
Mynda Treacy says
Hi Sharlene,
It’s a bit more complicated than an IF or even COUNTIF. You want to count data across multiple columns (B:G), as opposed to a single column, which is what COUNTIF is designed to do. You can use SUMPRODUCT like so:
Mynda
Paddy says
Hi, I have a problem while using IF function.
See.. Am trying to use if function Column B & C with a formula that if column B contains CLOSED than C should be CURRENT date and the formula is ” =IF(B2=”CLOSED”,TODAY(),””) “.
It is working correctly, however, while opening the excel sheet in the next day, it is reflecting the current day and it is not reflecting the date which I actually enter CLOSED in column B on the particular date.
Can someone please help me to fix, what exactly am I doing wrong?
Thanks,
Paddy
Catalin Bombea says
Hi Paddy,
Each time excel recalculates, TODAY function will return today’s date.
You have to use a simple macro that will react to column B values, when you type CLOSED, the code will put today’s date in column C.
Should be like this:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
If Not Intersect(Target, Range("B:B")) Is Nothing Then
Application.EnableEvents = False
For Each Cell In Target
If Cell = "CLOSED" Then Cell.Offset(0, 1) = Date
Next
Application.EnableEvents = True
End If
End Sub
The code should be placed in that worksheet vb module.
Ashok says
i want to check whether a number is in range a1:e1. suppose if that number is in cell c1, i want to find the largest number in range c1:c10. wats the formula in excel
Mynda Treacy says
Hi Ashok,
Please post your question and example Excel file on our Excel Forum, as I’m not sure what you mean by your explanation here.
Mynda
MD.JAHANGIR ALAM says
Hi. How I Can Round In Bonus after Using IF(AND) Formula
Mynda Treacy says
Try wrapping your formula in the ROUND function:
Will round the result to 2 decimal places, assuming IF returns a number. It’s difficult to be precise without knowing the details of your formula.
Mynda
ishmail dumbuya says
Please i need a formula for this spreadsheet.
This is the summary of the formula i need.
discount of $37.9/MT for every pound below 49lbs to 47lbs. And discount of $75.8/MT below 47lbs to 46lbs and rejection for outturn below 46lbs.
Discount of $5/MT for every nut above 210 to 215, and above 215 to be rejected.
Discount of $20/MT for every 1% above 10% to 12% above 12% to be rejected. please i need this formula assistance. Thank you.
Mynda Treacy says
Hi Ishmail,
Please post your question and a sample Excel file on our Excel forum so we can see your question in context of your data.
Thanks,
Mynda
jay_excel says
Hi,
I need a formula to sum the values based on their respective dates and once the due date appears(monthly), it should start over again from that date until another monthly cycle.
Thanks
Mynda Treacy says
Hi Jay,
It sounds like you need SUMIFS
Mynda
Jay says
Hi,
Please advise a conditional formatting custom formula to highlight last Thursdays of every month in selected cells.
Catalin Bombea says
Hi Jay,
Depends on what you have in those date cells.
In case you have dates, not text, you can try this conditional formatting formula, assuming that data starts from row 2 in column A:
=AND($A2>=EOMONTH($A2,0)-7,WEEKDAY($A2)=5)
Jay says
Hi Catalin,
This has been helpful, undoubtedly. I tweaked the function a bit as follows, considering date column is AK and my weekdays start from Monday.
=AND($AK2>=EOMONTH($AK2,0)-7,WEEKDAY($AK2)=4)
Although this is perfect in almost all months, there is some error in few months.
The months ending on Wednesdays’ such as Jan 2018, Feb 2018, Oct 2018, July 2019, Sep 2020, March 2021, etc. shows the first Thursday of the next month i.e. 1st Feb 2018, 1st March 2018, 1 Nov 2018, 1st Aug 2019, 1st Oct 2020, 1st April 2021, etc. respectively, as highlighted.
L.E.:
The months, which are ending with Thursday’s, the formula is considering to highlight the last two Thursday’s of those months. Examples of such months are Aug 2017, November 2017, May 2018, January 2019, February 2019, etc.
Kindly help.
Kindly advise. Thanks..
Catalin Bombea says
Hi,
Simply change the formula from:
=AND($AK2>=EOMONTH($AK2,0)-7,WEEKDAY($AK2)=4)
to:
=AND($AK2>=EOMONTH($AK2,0)-6,WEEKDAY($AK2)=4)
jay_excel says
It worked.. Thank u so much.. Cheers 🙂
Jay says
I need to create a formula where the text of the target cell, changes from “ACHIEVED” to “PENDING” on a basis of due date reoccurrence, every month. The sum reference cell values are obviously taken from different cells.
Here is what I got so far. My current status is “ACHIEVED”, but need this to start over again as “PENDING” on 15th of every month, irrespective of any other influencing factors.
=IF(X3>=S3,”ACHIEVED”,”PENDING”)
Please Advise
Catalin Bombea says
Hi Jay,
We have to see some examples, try uploading a sample file on our forum. (create a new topic)
Catalin
LeNor says
Hello. I need a formula that adds up currency in one column if the criteria in another column is met. I have tried a few and can’t get it to work with a range.
A3:A5 (has either a 1 or 2) and C3:C5 (has currency) I need a formula that says if range A3:A5 is 1 then add C3:C5 that coordinates with 1.
I hope that makes sense.
Catalin Bombea says
Hi,
Can you prepare a sample file with an example of how it should be? At this moment, it’s not very clear. Use our forum to create a new topic and upload the file.
Catalin
Robert says
What is the IF function for C2>E2=G2
Mynda Treacy says
Hi Robert,
Mynda
naveen says
Hai,
If 0 to 1000 value is 23
please advise some formula
Catalin Bombea says
Hi Naveen,
use this one:
Emma says
Hi Guys,
If I am using the formula: IF(AND(B2=”TRUE”,C2>1),”A”,”B”) where column B’s outputs (True or False) have been calculated with the OR formula (i.e. not hard-typed); I get the wrong output for my IF formula.
Any ideas how you get the right output when relying on information that has got formulas on it?
Thanks
Catalin Bombea says
Hi Emma,
Try to use B2=TRUE, without wrapping TRUE between double quotes.
Catalin
Emma says
Brilliant!
Thanks 🙂
Amit says
hi admin,
Pls help to make a formula for, if in a cell (J6) is less then 1 then in cell(J7) populate 100%, If in cell (J6) is in between 1 to 5 then cell (J7) populate 50% and If in a cell(J6) is greater then 5 then populate 0%.
Catalin Bombea says
Hi Amit,
Try this formula:
=IF(J6<1,100%,IF(J6<5,50%,0%))
You will have to decide if the limits should be included, use <= instead of <.
JJ says
Hi, if I type in cell A1 a number between 0 and 20 and I want it to give in b2 =1 or 21 to 40 = 2, what formula do I use?
Catalin Bombea says
Hi JJ,
try:
=IF(A1<=20,1,IF(A1<=40,2,3)) There can be many solutions, this is just one of them. Catalin
Dale H. says
What is the three for?
Catalin Bombea says
Hi Dale,
is for any other case, not covered by the first 2 evaluations.
Dale H. says
In colum E are # 0-15.Im trying to create an equations that says if E is less than 1=40,if greater than 1 but less than 6 =80, if greater than 6 but less than 12 =120, but if greater than 12 =160
This is what created”IF(E3<1,40,IF(16,80,IF(612,120,160)))” anything less than 1 it gives me 40 which is correct but everything greater than 1 its giving me 80 which is semi true.
Catalin Bombea says
Instead of nested IF’s, use a more flexible formula:
=INDEX({40,80,120,160},MATCH(E1,{0,1,6,12},1))Catalin
Namdeo says
I am trying since long to create a formula in excel sheet using IF & AND function. My challenge is, I want Excel to update remarks automatically basis dates updates in different columns in my inventory sheet. However, I am not able to proceed.
Catalin Bombea says
Hi Namdeo,
You will have to upload a sample file with details on our forum, from your description is not clear what you want.
Catalin
Symon Jere says
How do I represent the following PAYE info. in an excel:
First 20000 is free (0%), the next 5000 its 15% and the excess is 30%.
E.g
Salary: 40, 000
First 20,000 free
Remaining 20,000
5,000 is 15% = 750
Remaining (excess) 15,000
15,000 is 30% = 4500
Catalin Bombea says
Hi Symon,
Try this one:
Natasha says
Hi,
I am trying to calculate depreciated values of assets based on number of years I have owned them.
the first problem is I am unable to do it all in one cell. moreover, when I split it up, almost all cells were returning values although i have limited my logic to one year. see below:
Price No of yrs depreciated value > 5 yrs 4-5 yrs 3-4 yrs 2-3 yrs 1-2 years
28,500 4.05 8,550.00 FALSE 8,550.00 14,250.00 19,950.00 25,650.00
the correct column for this example should be 4-5 year and this is the formula: =IF(5>H4>=4,G4*0.3,0)
I have created similar formulas for he rest of the columns and as you can see all are returning values although i have stated a value if false.
=IF(4>H4>=3,G4*0.5,0)
=IF(3>H4>=2,G4*0.7,0)
=IF(2>H4>=1,G4*0.9,0)
Catalin Bombea says
Hi Natasha,
Use the AND function to combine multiple logical tests:
=IF(AND(4>H4,H4>=3),G4*0.5,0)
Ludi says
Hope someone can help.
In column C, I have text.
In column D, I have account numbers. Their format is for example: 505.1605.130
Another account number will be 505.2450.100
Now the 16 in the first account number example indicates that it is an asset account.
Not if the account in column D is an asset account, I want the cell $H$8 displayed in column H and the row that the account is in.
BUT, in the same formula, I want to look up in column C (which is a text column) for the word Depn between multiple words, and also return $H$8 to the row in column H.
I’ve started with the following formula, but it still need a lot of work.
=IF(AND(LEFT($D$12,5)=1,LEFT($D$12,6)=6),$H$8,””)
Can it be done?
Catalin Bombea says
Hi Ludi,
Use MID to find the account:=IF(AND(MID(D8,5,2),ISNUMBER(SEARCH(“depn”,D8)))=”16″,H8,””)
Catalin
rj says
i want formula for (if A1999 than A1*6/100)
Catalin Bombea says
Try:
=IF(A1999,A1*6/100,0)
You will have to specify the condition that should be met: A1999>0, or A1999=”Yes”
Kevin Frazier says
I am trying to track sick time, I want it to be that they earn 1 hour for every week worked, max of 80. once they use some it should subtract that and keep tracking example
week 80 they are at 80
week 81 they are at 80 still
week 82 they use 8 hours so they are now at 72
week 83 they get 1 hour so they are at 73
can you do this?
Mynda Treacy says
Hi Kevin,
Sure, but you’re best to upload a sample file to our Excel Forum so we can see your question in context of your data layout and give you a solution.
Mynda
Ashley Rader says
Say I am doing Times in columns A and B and I want to do an IF statement that Returns a Green highlight If the time in column A is earlier than the time in Column B and Returns a Red highlight IF the time in column A is Later than the time in Column B, how do I do this?
Mynda Treacy says
Hi Ashley,
You need to use Conditional Formatting for this. I cover that here: https://www.myonlinetraininghub.com/excel-conditional-formatting-with-formulas
If you get stuck please post your question and Excel file on our Excel forum where we can give you a specific answer.
Mynda
Shanu Singh says
Hi,
I have a problem if somebody can help me it would be very appreciable. I had wrote many formula but did not get results.
Have data like:
Age Actual Value Exempted Value
50 30000
50 14500
50 25000
60 30000
60 19100
60 35000
We want to get results:
Age Actual Value Exempted Value
50 30000 25000
50 14500 14500
50 25000 25000
60 30000 30000
60 19100 19100
60 35000 30000
Note: There are upper cap for 60 year old 30000.
Thanks
Shanu
Philip Treacy says
Hi,
You haven’t provided any logic/rules how to calculate that exempted value so I can’t help.
If you can provide this and a sample workbook, and post a question on the forum, someone might be able to help.
Regards
Phil
Shanu Singh says
Sure Thanks
Ahsan says
This is Ahsan. I have created a new logic which is mentioned below.
=IF(A20.75,A2*20%+A2))
by this logic any one can set any new target with below mentioned management criteria such as (if existing employee achieve 0 to 0.75 then his new target will be 0.75 but if existing achievement is more then 0.75 then 20% achievement will be add with existing achievement). Hope this logic will help with other.
Correction comments:
=IF(A20.75,A2*20%+A2))
Mynda Treacy says
Hi Ashan,
I think you mean
=IF(A2<=0.75,0.75,A2*1.2) Mynda
Sara says
Hello!
I am trying to create a slightly complex IF/THAN. If H2 = Late and G2 >= 30 then yes, otherwise no; If H2 = Early and G2 >= 15 then yes, otherwise no.
Column G is supplied via an ABS formula, and Column H are manual entries.
Thanks!
Mynda Treacy says
Hi Sara,
Try this:
Mynda
Stace says
Hi there, I am trying to do something similar but want to include multiple if’s in 1 cell to put different results if 2 different cells met certain criteria. How would I combine…I can not seem to get it to work
If H2= 1 and I2=”15oz” then =”EA” , If H2= 2 and I2=”27ft” then =”CT” ,If H2= 1 and I2=”13cm” then =”CA”
Appreciate any help
Thanks!
Catalin Bombea says
Hi Stace,
Try:
Catalin
Praveen says
If Column A is greater then Column B then Column C should given me “Yes” orelse “No”, if nothing contains in Column A and B then it should given me a blank cell
Mynda Treacy says
Marty Chanbmil says
Hi,
I am trying to have excel look up a cell in the same workbook – but per identity.
In this case cell 21 in each separate identity (represents a date/time entry via a macro.
This is reflected identically in the Summary Sheet in column A per identity =MickyMouse!A21.
When the new date is action via the macro in MickyMouse work sheet inside this same working book, the Summary sheet no longer reflects the current entry but rather changes the cell entry:
=MickyMouse!A22. HO do I automate this to be a true reflection, or a MACRO to check the column and make sure all end in !21.
Cheers Marty
Mynda Treacy says
Hi Marty,
Please post your question and Excel file on our Excel forum so we can see your question in context.
Thanks,
Mynda
Steven says
I am trying to create an expression and any help would be GREATLY appreciated! I am creating a worksheet with a little automation and would like to create an expression that outputs this example; If JANE DOE (name in column 1) will be arriving within the next 30 days (arrival date in column 2 listed as (date formatted) 21 January 2018) show “30 days or less” in column 3
Catalin Bombea says
Try this:
=IF(Today()-B1< =30, "30 days or less", ">30 days")Catalin
Steven says
Thanks for the reply. Its a good start but it doesn’t argue the date in column b, it just says “30 days or less” regardless. I tested it by changing the date in column b to 31 December 2018 and it returned with “30 days or less”
Catalin Bombea says
Hi Steven,
For future dates, higher than today’s date, use B1-TODAY() in the logical test argument. If the date is smaller than today’s date, use TODAY()-B1. If you want this to work in both ways, use ABS(TODAY()-B1)
Cheers,
Catalin
Steven says
Catalin,
Thanks so much that worked. If its not too much to ask, and im only asking because I cant seem to figure it out and you are already familiar with this problem.
I would like to output in the same argument “31-90 days”, “91-180 days”, “More than 181 days”
Thanks again for your time!
Catalin Bombea says
Hi Steven,
Use an Index/Match formula with constants, like:
=INDEX({"Less than 30 days","31-90 days","91-180 days","More than 181 days"},MATCH(B1-TODAY(),{0,30,90,180},1))chell says
Do you know how to create an excel formula for this: if b1 is x subtract 50% to a2, or if b1 is y subtract 100% to a2, the answer should appear on cell c2?
Mynda Treacy says
Hi Chell,
I’m not sure I understand your question. This part is confusing: “if B1 is x subtract 50% to A2”. What do you mean by “x subtract”? Can you please provide an example using numbers so we can follow the logic and provide you with formula.
Thanks,
Mynda
Ram Mohan says
If a worker worked for 15days, but he will be paid for 10days, due to poor performer. Similar for 50workers. What formula to be used in excel
Catalin Bombea says
Hi Ram,
Please upload a sample file with your data and a better description of the problem on our forum (create a new topic).
Catalin
Alan says
Trying to figure out a rolling time period of 75 days where the previous day and the data associated with it falls off.
Catalin Bombea says
Hi Alan,
Try uploading to our forum (create a new topic) a file with sample data and an example of the desired result, it will be easier to understand your situation.
Catalin
Alan says
Thank you Catalin, I will try that.
Alan says
Trying to get a formula for dates older than 75 days. If B4>75 days then B4,C4,D4 and E4 will be blank. If B4 is< 75 days then there is no change to any cells. B4 has a date value in it. Any help is appreciated.
Catalin Bombea says
Hi Alan,
It’s not possible for a cell to depend on itself and hold different values, depending on it’s own value. Only with vba this can be achieved, not with normal cell formulas.
Catalin
Alan says
thank you
Madisen says
I’ve been struggling to figure out how to type in this IF function for excel. I could really use some help! Thank you!!
In cell G2, create a formula using the IF function and structured references to create a calculated column that determines the value of Weeks Vacation based on the following criteria:
a. If the years employed are less than 3, the Weeks Vacation should be 2
b. If the years employed are 3 or more, the Weeks Vacation should be 3
I keep getting invalid credentials or the outcomes being all FALSE, TRUE and 0, all the cells being 0, or the first 3 cells being 0 and the last 3 coming out as NULL instead of the correct numbers… The correct answers when the formula is entered correctly is:
G2:3
G3:2
G4:3
G5:2
G6:3
G7:3
Thanks again!
Catalin Bombea says
Hi Madisen,
If you have an employment date column, the formula should be simple:
=IF(YEAR(Today())-YEAR(EmploymentDate)<3,2,3) (where EmploymentDate refers to that starting date column)
If you already have the employment years calculated, replace YEAR(Today())-YEAR(EmploymentDate) with your cell reference where you have this value.
Catalin
Philippa Stanworth says
I want to put formula in to say if column c says “6W” and column u is greater than 99, add the number of rows with 6W and >99 and divide them by 29 and multiply by 100.
Help!
Catalin Bombea says
Hi Philippa,
Try this:
=SUMPRODUCT((C:C=”6W”)*(U:U>99))/29*100
Paul says
I need to see if the value in A2 is 10% or higher than the value in B2…should I be using an IF function for this…I can’t figure it out
Mynda Treacy says
Hi Paul,
You can do this with a simple logic formula like so:
If it’s bigger then it will return TRUE, otherwise it will return FALSE
You didn’t say what you wanted to return if A2 was 10% greater than B2. If you want to return something other than TRUE or FALSE (that will be returned by the formula above) then you’ll need IF.
Mynda
Paul says
Thank you Mynda, I think this is a case where I was overthinking..
Kristi Gunerius says
I have a partial answer to my question. I am trying to get an answer based on a date…
=IF(TODAY()>=EDATE($C$2,12*10,300,0) AND =IF(TODAY()>=EDATE($C$2,12*15),500,0)
I am trying to get this in one equation, but I can’t seem to get the two statements joined. They both are working on their own just fine. Basically I am asking that if today’s date is 10 or 15 years from the hire date, then the staff member receives either a 300 or 500 stipend.
Mynda Treacy says
Hi Kristi,
It looks like your EDATE function isn’t correct. It only has two arguments; Start Date and Months.
Try this:
=IF(TODAY()>=EDATE($C$2,12*15),500,IF(TODAY()>=EDATE($C$2,12*10),300,0)
Mynda
Jahayra Gomez says
can some one help me with a formula.
I want to create a formula tat states the following:
if a3 has a date of xx/xx/xx or grater then “40” but if a3 has a date of less than xx/xx/xx then it should be “0”
Mynda Treacy says
Hi Jahayra,
Try:
Mynda
Virendra Sidhu says
I am looking for a formula where if 268 (Column B) was scored out of 284 (Column A), which means it is 88% achieved (Column C), I need a formula that states if Column C is below 60% it is 0 marks, if below 80% then half of the defined mark which is 10 marks of defined 20 marks and above 80% to 100% then actual % of column C of 20 marks and if above 105% then double the marks of above 100% + 100% marks for e.g if 107% then 7% * 2 times for double which comes to 14%, so now 14% of 20 marks which is 2.8 marks + 20 marks = 22.80 marks
Catalin Bombea says
Hi Virendra,
Try this formula:
=CHOOSE(INDEX({1,2,3,4,5},MATCH(C1,{0,0.6,0.8,1,1.05},1)), 0, 20*0.5, 20*C1, 20*C1, 20+20*2*(C1-1))
Your description is very confusing, and there is a range not covered: what should happen if the percentage is between 100 and 105% ? You described only 80-10 and above 105%. The formula for this case, which is value 4 argument of the CHOOSE function, must be changed according to this case needs.
Catalin
Prashant pawar says
i want formula count late mark condition is if employee cross 100 min his deduction 0.5 late make per day if he late.
Catalin Bombea says
Hi Prashant,
You will have to upload a sample file to our forum, and provide more information, at this moment it’s not very clear what you want to do, a sample file with your data structure will help clarify the request.
Thanks for understanding
Catalin
Edouard says
I have this formula in the cell X2:
=If(AND(W2>=$X$10;W2<=$Y$10;C2″”;D2″”;E2″”;F2″”;G2″”;H2″”;I2″”;J2″”;K2″”;L2″”;M2″”;N2″”;O2″”;P2″”;Q2″”;R2″”;S2″”;T2″”;U2″”);”NAF”;”DEL”)
If these conditions are meet then return NAF otherwise DEL and it works.
But for me the formula is long, I tried to replace from C2 to U2 with C2:U2″” but is returning #Value!
Is there anyone who can simplifier this formula for me? Please I need your assistance.
Edouard says
C2 to U2 not blank, the application removed the sign less than and great than after cell numbers.
Catalin Bombea says
Hi Edouard,
Try this one:
=IF(AND(W2>=$X$10,W2< =$Y$10,SUMPRODUCT((C2:U2<>"")*1)=COLUMNS(C2:U2)),"NAF","DEL")You can replace COLUMNS(C2:U2) with 19, if the number of cells in that range is not going to change.
You will also have to change the comma separator to your semicolon separator in formula.
Catalin
Jafar Chhapra says
You want to add the contents of cells A3 and B3, then add 15%, and display the total in cell C3. What do you type in cell C3 to make this happen?
Mynda Treacy says
Hi Jafar,
Kind regards,
Mynda
Alex Wilkins says
trying to add 10 years to a date if another cell = Y and 6 years if cell = N
would also like to change the color to recognize the N
a b c d e f
Y 03/01/2010 03/01/2020
N 03/01/2010 03/01/2016
Catalin Bombea says
Hi Alex,
Try:
=DATE(YEAR(B1)+IF(A1=”Y”,10,If(A1=”N”,6,0)), MONTH(B1), DAY(B1))
Catalin
Mynda Treacy says
Hi Alex,
I see Catalin has answered your formula question. You can use Conditional Formatting to automatically format the colour.
Mynda
saurabh Verma says
Calculate the division for a student acc. to following conditions:-
1- If Total marks >=500, then division is 1st.
2- If Total marks is b/w 400 to 500, then 2nd.
3- If Total marks is b/w 300 to 400, then 3rd.
4- If Total marks is <300, then fail.
How can i do this plzz rpy me…..
Catalin Bombea says
Hi,
Try this one:
=INDEX({0,3,2,1},MATCH(C15,{0,300,400,500},1))
Catalin
Elbeth says
Hi,
Will you be so kind to assist me.
I am struggling to get a working formula for what i want to do.
The 2 cells referred to was pulled through with VLOOKUP or calculated with a nested IF formula,
=IF(E21=Saturday,F24*1.5,IF(E21=Sunday,F24*2))
E21: VLOOKUP Formula from another sheet which will indicate whether that day was a Monday, Tues,etc.
F24: IF nested formula to give a price
I want the total to automatically calculate the price x1.5 or x2 if it E21 is Saturday or Sunday, and normal pricing should remain if during weekdays.
However I have received a #NAME? error
I am still learning via all these helpful posts, will you please assist
Catalin Bombea says
Hi Elbeth,
put the text between double quotes:
=IF(E21=”Saturday”,F24*1.5,IF(E21=”Sunday”,F24*2)), only defined names can be used without double quotes.
Elbeth says
Thank you for your assistance,
I have tried this and yet again I receive the NAME error.
Is it possible that the Saturday and Sunday I refer to in the formula was converted from date directly with format cells dddd function?
Catalin Bombea says
Are those double quotes copy pasted from the previous reply? If so, you will have to type them from your keyboard, those double quotes copied from a web page might be different than the type of double quotes expected by excel.
Also, you can create a new topic on our forum, to upload your file, it will be easier to understand what happens there.
Catalin
Jeanine says
Hi,
I am busy writing a performance review document for my company. In column A will be the job description example “Sales”. Column B you rate the employee as either 1 or 2 = Poor, 3 = Average or 4 or 5 = Excellent.
I want the description (Poor, Average or Excellent) to come up automatically in column C when you type 1-5 in column B.
Please assist with the formula to use
Mynda Treacy says
Hi Jeanine,
Try this:
Mynda
Jeanine says
Hi Mynda,
that did work, thank you.
However, now i want to know, if the sell is blank, it give the if(B1<3, "Poor") rating. however, i want to change it to should the cell be blank, there shouldn't be any rating.
Please assist
Thanks
Jeanine
Mynda Treacy says
Hi Jeanine,
Kind regards,
Mynda
priya says
A4=A2-A1, If(A4>30,”YES”,”No”). Does it work or not?
Catalin Bombea says
Hi Priya,
If you have this in a single cell: A4=A2-A1, If(A4>30,”YES”,”No”), it will not work.
If you have in cell A4 the formula: =A2-A1, then in any other cell the formula: =If(A4>30,”YES”,”No”), it will work.
Catalin
Vignesh says
10
15
20
hi friends any one help me to get correct the formula.
In my excel sheet in row i want to apply this formula.
if the cell contains 10 the formula cell needs to show as 1
if the cell contains 11 to 15 formula cell needs to show as 2
if the cell contains 16 to 20 formula cell needs to show as 3
Catalin Bombea says
Hi,
The formula should be:
=IF(A1=10,1,IF(AND(A1>=11,A1< =15),2,IF(AND(A1>=16,A1<=20),3,0))) It will return 0 for any other alues than the ones you indicated Catalin
Michelle says
I wonder if you could help me. I am trying to set up a spreadsheet of products we sell, however some items have GST (Australian tax at 10%) some do not. My C6 column asks if the product has GST and I have a Y or N answer in this field. Further along my sheet in J6 I have a sale price column. Then K6 is GST (=SUM(J6*1.1-J6))
L6 is sale price plus GST (=SUM(J6+K6))
How could I write in Excel language… If C6 equals N to leave the K6 as Zero, but if C6 = Y to show the equation I already have? Maybe there is a much easier way and I am complicating things too much!
I have Googled myself crazy trying to work this out! Hope anyone could help!!
Mynda Treacy says
Hi Michelle,
Try this for your GST calculation:
=IF(C6=”N”, 0, J6*.1)
If that’s not it please post your question and sample file to our Excel Forum.
Mynda
Marcie Heinz says
What is the function written out for this problem?
In cell B10, enter a formula using the IF and AND functions to indicate whether the revenue goal has been met that month:
Enter the logical test using the AND function to determine if the Fundraisers amount in cell B7equals 0 and the Total in cell B8 is greater than 20000.
If the logical test is true, display Yes (using “Yes” for the value_if_true argument).
If the logical test is false, insert a nested IF function.
Enter the logical test of the nested IF function using the AND function to determine if the Fundraisers amount in cell B7 is greater than 0 and the Total in cell B8 is greater than 200000.
If the logical test for the nested IF function is true, display Yes (using “Yes” for the value_if_true argument).
If the logical test is false, display No (using “No” for the value_if_false argument).
Catalin Bombea says
Hi Marcie,
Please upload a sample file on our forum (open a new topic), with your data structure and a sample result, it’s not clear what you need to do.
Catalin
Alec Gerlach says
In cell H7, insert a nested function that awards the employee a 10% bonus if they were hired before 7/31/2012, a 6% bonus if they were hired on or before 7/31/2016, and 4% to employees hired after 7/31/2016.
-can someone help me set up this function? if so, can they kinda explain how they got their answer too? im struggling with this!!!!!!!
Catalin Bombea says
Hi Alec,
An Index-Match formula should work:
=INDEX({0.10,0.6,0.4},MATCH(A1,{0,41121,42582},1))
Te formula can be inserted in any cell you need, just replace the A1 reference with the cell that holds the date to evaluate.
Those 2 arrays with constants (in curly brackets) contains your indications: 0.10 will be returned if the date is between 0 and 41121, 0.6 will be returne if the date is between 41121 and 42582, 0.4 for dates higher than 42562.
Make sure you have dates in correct format, not text strings. (a date is a number representing the number of days from 01/1/1900, 41121 is 7/31/2012)
The key is the last argument of the Match function; 1 is the Match type: Less Than.
Alec Gerlach says
Thans!!!!!
Dheeraj says
Very good
Renee says
Need Help with a formula….
If J2 is > K2 up to 100, the answer is 2 but
If J2 is > K of 101 or more, the answer is 1 or
if J2 is < K, the answer is 3
Mynda Treacy says
Hi Renee,
Try this:
arjun rawat says
Name M1 M2 Project Online Total Percent Status
Arjun 44 46 60 202 352 70.4
Sital 27 38 73 182 320 64
Chandan 40 47 80 275 442 88.4
Mahesh 44 37 73 241 395 79
Madan 33 27 70 235 365 73
Pawan 43 47 91 225 406 81.2
Dinesh 36 37 60 163 296 59.2
Yogendra 26 34 51 218 329 65.8
Aman 41 47 75 244 407 81.4
Nabin 40 48 72 176 336 67.2 ,
i am typing this formula=IF(AND(C3>=25,D3>=25,E3>=60,F3>=150),”PASS”,”FAIL”) for status but it says formula contains error. Could you please locate my error?
Catalin Bombea says
You have to upload a sample file on our forum(create a new topic), the formula is looking good. We have to see the file to understand what happens there.
Catalin
Diane Awbrey says
This didn’t work.
=IF(ISBLANK(M2),””,IF((TODAY()-I2)>45,”Contact Requester”,””))
It showed Contact Requestor even though M2 had a closed date and when I deleted the closed date it didn’t perform IF((TODAY()-I2)>45,”Contact Requester”,””)) part of the formula.
Mynda Treacy says
Hi Diane,
I think I’m confused 🙂 Can you please post your question and sample file on our Excel Forum. That is the best place for support as we can actually see your file and what you’re working with.
Thanks,
Mynda
Diane Awbrey says
In reference to my earlier comment….
This almost does what I need
=IF(AND(NOT(ISBLANK(M2))),””,IF((TODAY()-I2)>45,”Contact Requester”,””)), but if the formula is in A1 (such as a template) because I2 is blank A1 Displays “Contact Requester” until I put in an issue date, whereas I want it to be blank.
Mynda Treacy says
Hi Diane,
Try this:
Mynda
Diane Awbrey says
Hi I am having trouble with my formula., If M2 is blank I want to perform the following **calculation below. This part works great, what I am having trouble with is the cell M2 part of it. If M2 is not blank I don’t need to determine is today is more than 45 days (I2 contains an issue date), however if M2 is blank I need to know if it is overdue (M2 contains closed date)
** =IF((TODAY()-I2)>45,”Contact Requester”,””) works great
How do I write the formula to accomplish if I2 is over 45 days and M2 is blank the cell will populate with Contact Requestor (this part works), but if M2 has a date I want A1 (cell where formula is) to stay blank.
Amanda says
I can’t get my formula correct. What I need to do is say “if G3 is greater than today, then write RED in column D3 which is the RAG status column.
Thanks for your help.
Amanda
Mynda Treacy says
Hi Amanda,
Have you tried this in cell D3:
If that doesn’t work please upload an example to our Excel Forum so we can help you.
Mynda
Shivam says
if grossprofit <=15000,then taxes = gross profit * 20 %, rest all cases Gross profit * 30%
Catalin Bombea says
Try:
=IF(GrossProfit<=15000,GrossProfit*0.2, GrossProfit*0.3) You already written the formula, in a very close format 🙂 Replace GrossProfit with a cell reference.
Samer Saadeddin says
Hello Guys,
I have a formula that requires 6 out of 12 conditions to be met randomly. if I try to use the IF with AND and OR, I need to write more conditions than the Excel can provide.
the questions is, how can I tell excel that if 6 conditions are met, then the return value would be “X”?
Samer Saadeddin says
Correction:
How can I tell Excel to return s specific value if 6 out of the 12 conditions are met randomly?
Mynda Treacy says
Hi Samer,
Please post your question on our Excel Forum with a sample Excel file so we can see your question in context, as I don’t understand the scenario you describe.
Thanks,
Mynda
Ybo Tawano says
=IF(B28=””,””,IF(OR(B28=B29,B28<B29),"MET","UNMET"))
if B28 and B29 are equal, it's supposed to say "MET" but it doesn't. say B28 and B29 are both 10%, it says "UNMET" instead. care to comment? thanks
Catalin Bombea says
Check B28 and B29, there may be more decimals than displayed. Increase the number of decimals displayed, this way you will be able to see if the results are different, or round the values: ROUND(B28,4)=ROUND(B29,4)
Leslie says
I need help with this formula.
If column A is “corn” then it’s d5/2.6, but if column A is “soybeans” then it’s d5/.93
also this one:
if column A is “cotton” and column B is “Monsanto” then it’s d5/5.55, but if column A is “cotton” and column B is “Phytogen” then it’s d5/5.11
Any help would be appreciated 🙂
Catalin Bombea says
Hi Leslie,
Try this one: =IF(A5=”corn”,D5/0.93,IF(AND(A5=”cotton”,B5=”Monsanto”),D5/5.55,IF(AND(A5=”cotton”,B5=”Phytogen”),D5/5.11,0)))
It will return 0 if no condition is met.
Catalin
Jay says
Hi There
Can one do an auto allocation?
If A1 is > than 0 then C1 will always be 4
Thanks
Catalin Bombea says
What should be there if A1 is not >0?
Put this in C1:
=IF(A1>0,4,A1)
If you wanted to modify cell C1 from a formula located in another cell, this is not possible, a formula will display a result only in the cell where it’s located. Only through a specific macro you can modify any cell you want.
Kevin says
I want =IF(J4>L4,I4,K4) but if J3=L3 I want it to display TIE
Catalin Bombea says
Try:
=IF(J3=L3,”TIE”,IF(J4>L4,I4,K4))
tena says
q=write+a+query+taht++changes+the+salary+to+10000for+all+employees+with++salary+of+less+than+9000
Mynda Treacy says
Hi Tena,
In a separate column insert this formula:
Where 'salary' is the cell containing your salary. You can then replace the values in the existing salary column with the values returned by the formula if you wish.
Mynda
Donna says
our employees get vacation time as follows but I cannot seem to come up with the correct formula, either it’s an error or it shows blank – I’ve tried multiple ways, please help
less than 1 year = 0 days
@ 1 year but less than 3 years = 5 days
@ 3 years but less than 10 = 10 days
10 years or more 15 days
Mynda Treacy says
Hi Donna,
Try this formula where A2 contains the number of years they’ve been employed:
If that doesn't work then it's best if you can post your question with a sample Excel file on our Excel Forum so we can see what format your years are in.
Mynda
Gagan says
Dear Team,
I want you know if our status is closed , pending and under process and we want no. of closing days if only status is closed only i.e. no of closing days = closed date- booking date.
Please tell me know I can do this ASAP.
Many Thanks!!
Mynda Treacy says
Hi Gagan,
Please post your question on our Excel Forum and include a sample Excel file so we can see the format of your data and help you.
Mynda
carla says
hi I have an issue. I need my daily total that I have auto summed that if the daily total is less than 94$ fill in 94$ if not less than 94$ then the auto sum of the daily total.
Catalin Bombea says
Hi Carla,
Please upload a sample file on our Forum, I don’t understand what you mean by autosum.
The IF condition is simple:
If(DailyTotal<=94, 94, DailyTotal) Catalin
Bhavya says
hi I have a problem, simple though, but isn’t getting the required results..
the values in column A are as followings:
A1= 699, A2= 499, A3, 399, A4= 899, A5=799
Now in Column B I want to show the calculation as: If A1>500, then it should calculate 5% of the value in A1; if A1<500, then it should display 0.
Please help.
Thanks in advance!
Catalin Bombea says
Try this:
=IF(A1>=500,A1*0.05, 0)
Catalin
Manoj says
I need to calculate PF for persons working in our company in excel sheet. And the condition is if the cell A is basic pay and value in cell A15000 the it should calculate for the amount of Rs.15000
Rate of % is 12 for calculating PF
Help me to calculate using if statement and thanks in advance
Mynda Treacy says
Hi Manoj,
Please post this question on our excel forum with an example Excel file. There are a few points missing from your description that can be answered with an example file.
Thanks,
Mynda
Kevin says
Hello,
Im trying to get some pricing from 2 vendors to come up with a sell price but i would like to have the formula use the smaller cost between the two.
So
Cell A1 = $100
Cell B1 = $75
Cell C1 = .7
I would like B1 to divide by C1 but if the roles were reversed i would like A1 to divide by C1.
Please help everything ive been trying has not worked,
Thanks
Catalin Bombea says
Hi Kevin,
Try: =MIN(A1, B1)/C1
Catalin
Callum says
Hi,
Great for the tip
I have an excel problem which i think is similar to an If statement but basically i have 3 columns
a b c
1 10 15
2 13 16
If the value in b1 is greater then the value in a1, in c1 I want to return the value of a1, so in this instance I want 10 to appear in c1 – is this possible?
Thank you in advance for any help
Mynda Treacy says
Hi Callum,
Your formula in cell C1 would look like this:
Note: you didn’t say what would happen if B1 wasn’t greater than A1, so I put 0 for the false argument of the formula. You can change it accordingly.
Mynda
John says
I’m sure mine is easy to do and i’m over thinking it but i am trying to do an income tax spreadsheet with multiple tax brackets. If total income is one cell, i want the first tax bracket to have a function that will show total income upto a cap and if the income is higher than the cap, the remaining amount will be placed below in the next cell which is tax bracket 2. But the tricky part where i get hung up is i want a 2nd and 3rd bracket that both have different caps but will gove totals off the income.
It’s confusing to type out and vicalize. Hopefully it makes sense to you.
Thank you in advance for any help.
Catalin Bombea says
Hi John,
Can you please create a new topic on our Forum, and upload a sample file with your data setup? It will be easier to understand your situation to provide a personalized answer.
Thanks
Catalin
Ana says
Hi! I’m a newbie when it comes to Excel so I’m sorry if my question seems silly, but here it goes:
I have a column with 80 values and I need to split that column into two (of 40 each). However, and need this 2 new columns to have similar means to one another. Is there any way to do this? Again, I’m sorry to bother you, but it would be great if you could help me.
Thank you.
Mynda Treacy says
Hi Ana,
Thanks for your quesiton. I’m not sure what you mean by “I need this 2 new columns to have similar means to one another”.
Perhaps you can post your question with a sample Excel file and an example of the end result you’re after on our Excel Forum where we can help you further.
Thanks,
Mynda
Pam Bethune says
I need a complex formula that I cannot figure out. I have a spreadsheet with many clients and many sites.
Column J is the audit type. There are 4 conditions in column J: C, S1, S2, or R.
Column O is the Audit end date.
When Column J = R, I need to know that a date in column O for the same client (column P) and the same site (column AB) is less than or great to the date in column O when column J=C.
L.E.:
Sorry,
When Column J = R, I need to know that a date in column O for the same client (column P) and the same site (column AB) is less than the date in column O when column J=C.
I really need to think more clearly. When J=R, the date in column O is allowed to be 3 months early and zero months late for the same audit end date listed when J=C for the same client/site.
Column J is the audit type. There are 4 conditions in column J: C, S1, S2, or R.
Column W is the standard. I only care if W=TS 16949 or IATF 16949.
Column O is the Audit end date.
Column P is the client name and column AB is the site.
When Column J = R, I need to know that the date in column O is less than the date in column O plus 1094 days when column J=C for the same client (column P) and the same site (column AB).
Let’s assume I am working with row 120. If J120=R and W120=TS 16949 or IATF 16949, (This could be *16949 of course)
find J=C for the same client (P) and same site (AB). Let’s call the result row 40.
Compare the dates in Column O. If O120 > O40 + 1094, turn the date red.
IF(J120=”R”
And IF(W120=”*16949″
Catalin Bombea says
Hi Pam,
A sample file is more valuable than a 1000 words, you already know this. You wrote those 1000 words, but we still need the sample file, to see the data structure and provide a solution tested on your file. Please open a new topic on our Forum, we will gladly help you.
Catalin
Richele says
I am trying to create a conditional format where if day x is a statutory holiday it will format the next available work day.
eg) Jacob Peanut works 4 days a week Tues – Friday. If his Monday day off is a statutory holiday the next available weekday needs to format. (I say this as December 26th is a holiday as well as a Monday, the next day, December 27th is day in Lue for December 25th so Wednesday December 28th would need to be his day off.
Thanks,
Catalin Bombea says
Hi Richele,
Can you upload a sample file on our Forum? It will be much easier to understand your situation and to provide a personalized answer.
Thanks for understanding.
Catalin
Meyanui says
Hello, I am back. I need a formula that would help me insert photos in excel spreadsheet and later on merge to MS office word.
Thank you in advance
Philip Treacy says
Hi Meyanui,
We’ll need a lot more information before we could produce any kind of answer, e.g. where are these photos stored? A sample workbook always helps otherwise we are just guessing.
Please open a question on the forum and supply a workbook and a detailed description of what it is you need.
regards
Phil
Meyanui says
Alright, thanks for the concern. The issue is that I do prepare students’ assessment sheet and at the end produce a report card. I have been trying a situation where I can insert their photos located in “Drive C” and to later on merge in Microsoft Word.
Secondly, how can I maintain same format when I merge, eg if a value has red color, when merged in Word, it keeps the same red color.
Thanks for your response in advance
Philip Treacy says
Hi Meyanui,
Please create a post in the forum and supply a sample workbook and a full explanation of the problem and the result you want.
As an example of why I am asking for this, and so you understand where I am coming from, you state that you want to ‘maintain same format’, what formats are you trying to move into Word? Maybe some of the format or Excel workbook design isn’t transferable to Word. If I don’t have a sample workbook I am guessing at what you want and the process of finding a solution for you can take longer than it needs to.
I need to understand why you need to merge the Excel workbook into Word in the first place. Is this merge required? Why? Can you achieve the same thing in Excel?
Regards
Phil
Meyanui says
Hi I love your well explained formulas. They are very helpful but i need help in this section: I’ll need a formula where ( If a particular cell is equal to a particular value, then distribute particular values to particular cells). That is to say if A2=10 then B2 should get “5”, C2 gets “4” and D2 gets “1”. Thanks for your response in advance
Mynda Treacy says
Hi Meyanui,
You’d have to put your IF function in each cell you want a value returned e.g. in B2:
=IF(A2=10, 5, 0)
in C2
=IF(A2=10, 4, 0)
in D2
=IF(A2=10, 1, 0)
Kind regards,
Mynda
Meyanui says
Thank you very much. It was helpful
Meyanui says
Hi there, i have come with yet another issue that’s a bit tricky for me to work out. Given the case i presented:
“I’ll need a formula where ( If a particular cell is equal to a particular value, then distribute particular values to particular cells). That is to say if A2=10 then B2 should get “5”, C2 gets “4” and D2 gets “1”.
Now, if the values of cells B2, C2, D2 are fixed, that is 5,4,1 respectively and A2 =8. I need a formula that would distribute as follows: B2=5, C2=3 (knowing fully well that C2 is supposed to be 4 but for the fact that A2 is less than the expected value) and when a value say 2 is added later on to A2 to make it 10, then it automatically adds to C2 to make it 4 before overflowing to the cell (D2).
I hope someone understands me enough
Thank you.
Catalin Bombea says
Try this in B1:
=CEILING(A1/2,5)
In C1:
=RoundDown(A1/2,0)-1
And in D1:
=A1-B1-C1
Meyanui says
Thank you Catali. Unfortunately, it didn’t do the exact magic
Catalin Bombea says
Can you describe why “it didn’t do the exact magic”?
According to your description, if A1=10, the 3 cells should be 5,4,1. If A1=8, we should have 5,3,0, and that’s what the formulas are returning.
Provide more details, and examples with different values in A1.
Cheers,
Catalin
Meyanui says
hi Catali, thanks very much once more. The issue is that I got many cells to apply those formulas. They range from B2-K2 and I want values to be distributed accordingly in this order: 3500, 500, 31000, 10000, 15000, 1000, 1000, 25000, 1000, 8000 RESPECTIVELY
A2 is the reference cell. Total value needed 96000. There are instances where a customer offers less than 96000, say, 56000. I’d like it distributes thus: B2=3500, C2=500, D2=31000, E2=10000, F2=11000(though it’s supposed to be 15000, but for the fact that it’s less). If an amount is added to A2, say 7000, (meaning 56000+7000 ie A2=63000) then F2 will change to 15000 before overflowing to the other cells. That is G2=1000, H2=1000. When A2=96000, then all the cells are then filled up.
The formulas of CEILING & ROUNDDOWN were a bit hard to fit in all. That’s the reason I said, didn’t perform the exact magic.
Once more thank you as I await your response
Catalin Bombea says
Please upload a sample file with your data and a manual result of how it should be. Create a new topic on our Forum. It will be a lot easier to understand your situation, no words can beat a sample file 🙂
parul malik says
it was helpful
alexei says
Hi,
I am not an excel novice by any means. 🙂 I am attempting to create a formula that I have yet to get to work. I have a spreadsheet that track dates. For example, in Column L2 it will either have a date or it will be blank. There are 3 other columns that depend on the date in that first column. What I need is:
Column S2 to be blank or + 3 of date input into L2
Column R2 to be blank or +3 of date now in S2
Column T2 to be blank or +3 do date now in R2
Hopefully that makes sense. 🙂
Thank you!
Mynda Treacy says
Hi Alexei,
In S2 you can use this formula:
In English it reads; if L2 is blank, then enter blank, otherwise L2+3.
Modify for columns R and T.
Let me know if you have any questions.
Mynda
Alexei says
Mynda, THANK YOU SO MUCH!!!!!
Cristina says
Hi,
can you help me with this formula, I have a date in B1, I have to make true if the date is the same or is between + or – 7 days, otherwise is false.
Thanks
Mynda Treacy says
Hi Cristina,
I’m missing some information; “if the data is the same or is between + or – 7 days” from when? From today, from a date in another cell?
Kind regards,
Mynda
Janifeertauseef Maqbool says
Can you please help me to get the formula:
if cell value is greater than 30000 then multiply with 0.5 otherwise multiply with 0.3
Mynda Treacy says
You can use a formula like this:
Kind regards,
Mynda
Kevin Strong says
I am having trouble getting a formula that will start with 75000 in Cell C15 if Cell C5 is 2500 or less and increase cell C15 by 10000 when cell C5 increase at increments of 2500. If Cell C5 is 5000 then C15 will be 85000. If cell C5 is 4999 Cell C15 is 75000. If C5 is 7500 then C15 is 95000. So the C15 is 75000 at 2500 or less and only increase by 10000 for every 2500.
Here is what I came up with. =IF (C5C5))
Catalin Bombea says
Hi Kevin,
Can you please upload a sample file on our forum? It will be easier to understand your situation.
Thanks for understanding.
Catalin
gerald says
Hi,
please help me to make a formula. example, If the value of A1 <= 100 it appears "1", if A1<=500 it appears "2", if A1100,1,IF(A1>200,2,IF(A1>300,3,IF(A1>400,4,IF(A1>500,5,0))))) only 1 appears on my equation.
Catalin Bombea says
Hi Gerald,
Try this one:
=INDEX({1,2,3,4,5},MATCH(A1,{0,101,201,301,401},1))
It should do what you want.
Cheers,
Catalin
Angie says
I want the cell to increase by $1.00 for every 6 pieces. So if I have 13 pieces it should be $3. I created this formula but it’s not correct, please help.
=IF(B32<=6,"$1.00") OR(B32,7==13,"$2.00""∞")
Catalin Bombea says
Hi Angie,
You can try the Floor function, or its sister Ceiling function (this is the one you want). The first rownds down to the nearest 6 units step, the second is rounding up to the next 6 units step:
=FLOOR(A1,6)/6
=CEILING(A1,6)/6
Catalin
Natasha says
HI There, I am wanting to use and IF/And function to change a cell text colour, is this possible?
this is what I am trying to do….
=if(and(C2=”Yes”,L2=”Please Select”) Change font colour of L2 to “Red Text” is this possible to do this within conditional formatting as a custom formula? as I am trying to do it and it is not working, text will not change colour in L2.
I need to alert myself if C2 says “Yes” but L2 says “Please Select” I need to select an option in cell L2. I am wanting to change the colour of the text as I already have a conditional format on the cell colour based on another rule.
Catalin Bombea says
Hi Natasha,
You already wrote the correct formula for a conditional formatting rule:
=and(C2=”Yes”,L2=”Please Select”)It should work without any IF statements.
Catalin
Natasha says
Thanks heaps Catalin!!!
Anne says
Hi Thanks for all your help and this is my last question and I was trying to working on this problem for 4 days now and I am having problem. please don’t think that I did not try and I really tried to do this right but I keep getting the wrong answer. In cell J8 (Balance), enter the formula that adds the values in the Tuition and Material Costs cells and then subtracts them from the sum of the Paid and Discount cells. You should see $350 in cell J5. A formula I used for this was =SUM(F9+G9)-(H9-I9) and I keep getting $2,350.00 but i don’t know how I can get $350 in J8? please help me thank you.
Catalin Bombea says
Hi Anne,
The operation you made here: (H9-I9) does not correspond to your description: “adds the values in the Tuition and Material Costs”. It’s as simple as this: if the instructions are saying to add the values, then use the “+” sign, not the “-” sign: (H9+I9)
It should be:
=SUM(F9+G9)-(H9+I9) or, without paranthesis:
=SUM(F9+G9)-H9-I9 (remember that if the paranthesis is removed, the minus sign before the paranthesis will change the sign of each term from that paranthesis.)
Let me know if this was the problem.
Cheers,
Catalin
Anne says
HI I have another question. how do you solve
In Cell I4, use the AVERAGEIF function to calculate the average balance owing by the students in the English Program. because I have tried this formula with =averageif and it is givimg me error. please help me thank you.
Catalin Bombea says
You have to read carefully the argument description, otherwise it will not work. First argument is the criteria range, you should select here the range with “English” column, the last argument is the average range. The last argument is optional (note that it’a wrapped in square brackets), if it’s not used, the first range will also be used for average calculation.
You can read the argument description in the functions wizard (after you type =AVERAGEIF press the fx shortcut next to formula bar).
It should look like this:
=AVERAGEIF(B1:B64,”English”,D44:D64)
Cheers,
Catalin
Anne says
HI I am having problem with this problem and if someone can help me with this problem, it would be great and I been trying to do it and just wanted to tell you that I am not letting you guys do the work for me I just need help.
1. In cell I5, enter an “AND” IF function that enters “F5*.10” if the value in the Program range is English AND the value in the Level range is 1. If neither of these criteria are met, nothing is entered. The purpose of this IF function is to offer a 10% discount to students who are taking Level 1 English. You should see FALSE in cell I5 and $200 in cell I7.
Catalin Bombea says
Hi Anne,
Try this:
=IF(AND(A5=”English”,B5=1),F5*0.1,””)
In A5 and B5 should be those Program and Level values you mentioned, adjust the references as needed.
Cheers,
Catalin
Tom May says
Could you help with this formula?
If A3 contains blank then blank if A3 contains “Y” then A1 times B1 is greater than .01 but less than 144 then =.5 if A1 times B1 is greater than 144 but less than 536 then = 1 if A1 times B1 is greater than 536 but less than 864 then = 1.5 if A1 times B1 is greater than 864 but less than 1440 then = 1
Thanks
Tom
Catalin Bombea says
Hi Tom,
Try this formula:
=IF(LEN(A3)=0,"",IF(A3="Y",1,0)*INDEX({0,0.5,1,1.5,1,0},MATCH(A1*A2,{0,0.01,144,536,864,1440},1)))
Your description is not complete, the limits are not clearly specified: for example, “if A1 times B1 is greater than 144 but less than 536 then = 1 if A1 times B1 is greater than 536 but less than 864 then = 1.5” doesn’t say something about the exact value of 536: in the first part we have less than 536 (the mathematical sign for this is “<536"), in the second part we have greater than 536 (the mathematical sign for this is ">536″). As you can see, 536 is not found in any groups. It should be one of these:
“less than or equal to 536” (“< =536") , or "greater than or equal to 536" (">=536″)
If you want to include the limits in the nexxt group, you have to adjust the limits in the formula:
=IF(LEN(A3)=0,"",IF(A3="Y",1,0)*INDEX({0,0.5,1,1.5,1,0},MATCH(A1*A2,{0,0.11,145,537,865,1441},1)))Cheers,
Catalin
Tom May says
Thank you, that is exactly what I needed!
Catalin Bombea says
Which one? The first or the second version?
James Miller says
i need a cell to increase by 40 every 180 days. can you help. i know with a start date i can track days on a job. but i cant figure this one out
L.E.:
I need some help, i need a cell on my first page to increase by 40 every 180 day. and i have know idea how to do that
Catalin Bombea says
Hi James,
You can simply deduct from today’s date the start date, it will give you the number of days between those 2 dates. If you divide the result by 180, you will get the multiplier.
It should look like this, in A1 there should be the start date:
=40*ROUNDDOWN((TODAY()-A1)/180,0)
I also used rounddown, because the result of TODAY()-A1)/180 may be fractional, like 1.65, 1.2, in these cases the result will be rounded to the lowest integer.
Cheers,
Catalin
Venkataramanan V says
Hi
Kindly help my data in one cell is 25
Now I need to check this Value for 3 Different Conditions
1) Less than or equal to 20
2) greater than 20 but less than 50
3) greater than 50 but less than 80
4) greater than 80
Thanks
Catalin Bombea says
Hi, You can try this:
=INDEX({"< =20","20-50","50-80",">80"},MATCH(C2,{0,21,51,81},1))Cheers,
Catalin
Matt says
Can someone help? I need a formula to calculate for full days holiday and half days holiday when specific codes are entered into the spread sheet. So if H is entered it would count 1 day but it H-AM or H-PM is entered then it would only count 0.5 of a day in the total monthly holiday taken. I cant seem to work this.
Catalin Bombea says
Hi Matt,
Can you please prepare an example file? When working with time, the format you are using is important, it’s hard to give an example that will work on most formats, the solution depends on your structure. You can open a new ticket on our Help Desk.
Cheers,
Catalin
Chante says
Can someone please help me? I have a column that is either emergency services or general construction. I have a separate claim where if D2=Emergency Services subtract U2 from L2. If D2= General Construction subtract U2 from AA2.
Mynda Treacy says
=IF(D2=”Emergency Services”, L2-U2, IF(D2=”General Construction”, AA2-U2,0))
Varughese Abraham says
Hi, I have two columns which I have to match and get the results.please can someone help me with the formula to find the results.
Batch No Partner Result
1 N Merge
1 N Merge
2 Y Reject
2 Y Reject
3 Y Merge
3 N Merge
In a batch if both the partners are Y then I have to update as Reject or else merge
Catalin Bombea says
Hi,
You can try this formula, built based on your sample:
=IF(SUMPRODUCT(($A$2:$A$7=A2)*($B$2:$B$7=”Y”))=2,”Reject”,”Merge”)
Catalin
Rhixie says
Hello can you please help me with these? i have already answered everything but this makes my brain bleed huhu we need to submit these activity on monday @.@
Pre-tax profit in B18 is (-250)
8. Tax at 30% is calculated on all Profit over zero. Use conditional logic to calculate the amount of tax paid each year.
9. Enter a formula that will calculate the Net Profit, by subtracting the Tax from the Gross Profit. Copy this across.
Catalin Bombea says
Hi Rhixie,
Try this in C18:
=If(B18>0,B18*30%,0)
Net Profit in cell D18: =B18-C18…
Catalin
Noel Pajarillo says
Good day, I want to ask, I’m having a problem on this statement : =IF(K2>=9000,1000,0)
if K2=1000 the result will be 0, and if K2=9001 the result is 1000,; it means that if K2 is greater than or equal to 9000 the result will be 1000 otherwise 0.
My question is, if I’m going to type a text or character on K2 it give me a result of 1000, rather than 0.
if K2=A the result is 1000, instead of 0.
Is their any equivalent numeric value of a text or character if I’m using an IF function?
Catalin Bombea says
Hi Noel,
In the ASCII table, numbers from 0 to 9 are in the following range: 48 to 57. The ascii codes for Alphabetic chars are from 65 to 90.
When you compare text to numeric values, excel will compare the ascii codes only for the first char from the 2 cells, and a text will always have a higher ascii code than a number.
You should use a double check in this case, to make sure the value in K1 is numeric: =IF(AND(ISNUMBER(K1),K1>=9000),1000,0)
Cheers,
Catalin
Katherine Baker says
Hi, could you write one that makes this work?
In cells E3:E180 I have a Y or a N, in C3:C180 I have a price. If there is a Y I want it to add the amount in C column to create a total at the bottom. If there is an N the price needs to be ignored. Also need it to apply 7% increase to the total box.
Mynda Treacy says
Hi Katherine,
You’d use the SUMIF function for that. e.g.:
Kind regards,
Mynda
Freddie Ramirez Martinez says
6. In cell L4, enter a formula that uses a nested IF function and structured references to calculate the Discount Amount:
a. If the value in the Discount Y/N column is equal to N, the Discount Amount column value should be 0.
b. If the value in the Discount Y/N column is equal to Y, the formula should check if the value in the Tenure (Yrs) column is less than 4.
c. If the value in Tenure (Yrs) column is less than 4, the Discount Amount column value should be 0.10.
d. Otherwise, the value of Discount Amount column should be 0.15.
(Hint: The Discount Amount column is formatted with the Percentage Number format, so the values returned by the nested IF function will appear as 0%, 10%, or 15%.)
Please Help.
Freddie Ramirez Martinez says
7. In cell M4, enter a formula that uses an IF function and structured references to assign a value rating to each customer. (Hint: You will need to use an AND function in this formula.)
a. The IF function should check if a customer has a Tenure field value greater than 3 years AND 2016 Purchases greater than $250.
b. If the customer meets both those criteria, the function should return the value High.
c. If the customer does not meet both those criteria, the function should return the value Low. If necessary, copy the formula you created in cell M4 to the range M5:M27.
Please Help.
Mynda Treacy says
Hi Freddie,
We’re here to help you when you get stuck. Not do your homework/exam paper for you 😉
Show some attempt at answering your questions and if you get stuck then please show us your attempt and we’ll point out where you went wrong. You won’t learn anything if we do it for you.
Mynda
Russ says
I’m having problems with the same one.
=IF(AND(E4>3,G4>250,”HIGH”,”LOW”))
Catalin Bombea says
Hi Russ,
The closing paranthesis for AND function is not in the right place, it should be after those 2 conditions, not at the end:
=IF(AND(E4>3,G4>250),”HIGH”,”LOW”)
Catalin
Freddie Ramirez Martinez says
a. A customer is eligible for a discount if the value in a customer’s 2016 Purchases is greater than or equal to $250 OR if the customer’s First Order was placed before 2012.
Catalin Bombea says
=OR(C25>=250,YEAR(INDEX($A$1:$A$10,MATCH(B25,$B$1:$B$10,0)))<2012)
In this example, column A will hold the date, column B - Customer ID, and column C- the purchase value.
Catalin
Freddie Ramirez Martinez says
Thank You. Much appreciated.
Eva says
Thank you for the information. But how can I make a function with
If the amount exceed 5000, calculate the amount * 13%; if the amount not greater than 5000, calculate the amount * 12%.
Mynda Treacy says
Hi Eva,
=IF(A2<5000, A2*12%, A2*13%)
Where cell A2 contains your amount.
Mynda
Adilson says
Hi
I really appreciate this information and I have a question:
How can I make a function such as: =if(a certain number is a multiple of 3, true, false)
Regards
Adilson
Mynda Treacy says
Hi Adilson,
Try this:
Where A1 contains your “certain number”.
Assumes there are no zero values in your list of ‘certain numbers’. If you’re likely to have zeros then use this:
Kind regards,
Mynda
Carlena Reynolds says
The formula you have listed makes a lot of sense however, I am trying to understand the following formula can you help me understand it?
=IF(F24<20,-100%,H5
Mynda Treacy says
Hi Carlena,
Your formual is missing the closing parenthesis, however in English it reads:
IF the value in cell F24 is less than 20 then enter -100%, otherwise enter the value from cell H5.
Kind regards,
Mynda
Ruth says
I want the cell with the date something is due to change colour if the date is less than or equal to today. I can do this but all blank cells also change colour. Formula is =F3<=$J$2
I have tried to add in another condition so that another cell must say "Yes" but this formula is not working – I have used =IF(AND(F3<=$J$2, D3="Yes")) but it doesn't work, any ideas what I'm doing wrong??
Thanks
Mynda Treacy says
Hi Ruth,
Try this:
Mynda
Ruth says
Thank you soo much, this worked 🙂 I’ve spent about 5 hours trying to do this , you are a life saver
Mynda Treacy says
Fantastic! Glad I could help 🙂
Jerry says
Today’s
Payment Balance Co pay Ending
Balance pymt pymt pymt
$25.00 $500.00 $20.00 $475.00 $25.00 TRUE TRUE
Hi do you have a formula for this task,
input todays payment, calculate payment (pymt) cell
if first payment cell has a number > 0 then use next pymt cell
they are returning true.
Catalin Bombea says
Hi Jerry,
Please open a new ticket on our Help Desk system and upload a sample file with your data and formulas, it’s hard to see what is under those values you presented.
Catalin
Jerry says
how do I write this in a formula:
input A=1
result if b1 > 0 then place result into in C1
Catalin Bombea says
Hi Jerry,
Which is the calculation for the result?
Anyway, you can place this formula in cell C1, i used A1*B1 as the result:
=IF(B1>0,A1*B1,0)
Please note that the formula must be in the cell where you want the result to be, it’s not possible to send the result from the formula which is in C1 to another cell.
Catalin
Sai says
good evening sir my name sai am from mahabubnagar dist i am using excel formula but not except the formula the use 1 month30day 1 Day absent the salary 4500 1day absent salary is 150 so i am attend the 30 day “Total
days” “Less
days” “Present
days” “Cut of
salary” “Present
salary” ” fixed
salery”
30 1 29 333 9670 10000
please response me sir my mail id
L.E.:
Total Salary 4500
IF(ROUND(E4-F4,0)=0,””,(ROUND(E4-F4,0))) Total 30 days
IF(ROUND(J4*F4/E4,0)=0,”–”,(ROUND(J4*F4/E4,0))) 2 days absent
IF(ROUND(J4*F4/E4,0)=0,”–”,(ROUND(J4*F4/E4,0))) Cut the salary 300
IF(ROUND(J4*G4/E4,-1)=0,”–”,ROUND(J4*G4/E4,-1)) Taken Salary 4200
My Problem is i am working 30 days no absent so present day come the row nill come how to use formula sir please tall me
Catalin Bombea says
Hi Sai,
Please upload a sample file and an example of the desired result, it will be easier to help you. Open a new ticket on our Help Desk system.
Catalin
Finlay says
Hi trying this but I can’t figure it out
cells B1 to H1 contains the following data;200,300,Boy,100,Girl,70 and 40.Add numbers that are greater than 76
count numbers of the house
Sum of numbers less than 50
Catalin Bombea says
Hi Finlay,
Please upload a sample file and an example of the desired result, it will be easier to help you. Open a new ticket on our Help Desk system, it will be easier to understand your situation and help you.
Catalin
nadra says
Hi
If total sale is more than 20 percent above the average than bonus will give 0.5 percent on original sale with IF formula plzzzz help
Catalin Bombea says
Hi Nadra,
Please try this formula, assuming that in A2:A10 you have the sales values:
=IF(A2>=AVERAGE($A$2:$A$10)*1.2,A2*0.5%,0)
Catalin
Ravi says
Hi,
Please verify the formula ” =IF(D4=(VLOOKUP(D4,Generic!AQ4:AQ1851,1,0)),”E”,”F”) “.
I have data in two different sheets in the same file, I need to validate if both the values are same it should show as E(Existing) or it should show as F(Fresh), with the above formula am getting the desired result when both the values are same, but when its different its showing as “#N/A” not showing as expected.
Please help.
Catalin Bombea says
Hi Ravi,
If the value from D4 is not found in Generic sheet, VLOOKUP will return an error.
Try:
=IF(ISNUMBER(MATCH(D4,Generic!AQ4:AQ1851,0)),”E”,”F”)
Catalin
ghreta says
Kindly help
If h12 = 2 and = 2.7 and = 3.5 diplome ba et capable de suivre mba recherche
Catalin Bombea says
Hi,
Not sure what you mean, can you be more clear on this?
H12 cannot have all those 3 values in the same time, most probably you mean OR:
=IF(OR(H12=2,H12=2.7,H12=3.5),”Yes”,”No”)
Catalin
Raj says
Hi,
Could you please help me out on this?
I need the following result in a particular cell, for example C3
I am trying to put formula in C3 so that:
– C3 = B3 if B3 is less than 100
– C3 is 25% of B3 if B3 is between 100 and 200
– C3 is 10% of B3 if B3 is between 200 and 1000
– C3 is 5% of B# if B3 is greater than 1000
I tried the below
=IF(B3>=100,B3*25%,IF(B3>=200,B3*10%,IF(B3>1000,B3*5%,B3*100%)))
But it always returns the 25% of B3 in C3.
Thanks for any help in advance..
Catalin Bombea says
Hi Raj,
You are close, there is one information you seem to not know about multiple nested IF statements: when a condition is met, the rest of conditions will not be evaluated, the function will simply stop at the first true condition. Knowing this, you should start from the last condition:
=IF(B3>=1000,B3*5%,IF(B3>=200,B3*10%,IF(B3>=100,B3*25%,B3*100%)))
Or, you can use an alternative to multiple nested if’s:
=B3*INDEX({1,0.25,0.1,0.05},MATCH(B3,{0,100,200,1000},1))
Catalin
Jerry says
Hi Mynda,
I want to pick one number in column A, 2 numbers in B, and 3 numbers in C. All 6 numbers not to exceed 500 total. Is there a way to find all possible combinations without any repeating? Thank you!
Catalin Bombea says
Hi Jerry,
With built in tools, it’s not possible. You will need to write a User Defined Function for that in Visual Basic.
Cheers,
Catalin
asis says
Kindly help: if B1 is equivalent to 75 do nothing, if B1 is less than 75 subtract
30% and add this to A1. if B1 is greater than 75 add 10% to A1
Catalin Bombea says
Hi Asis,
Try this formula in A1:
=IF(B1=75,0,IF(B1<75,B1-B1*0.3,IF(B1>75,B1*1.01)))
Cheers,
Catalin
Julie Connor says
Thank you for this, helps explain the difference between and/or. This just solidifies my reason to purchase your Excel site.
Mynda Treacy says
You’re welcome, Julie. Glad you found it helpful.
Mynda
karen says
Thank you first time i have ever seen it explained so simply
Mynda Treacy says
You’re welcome, Karen. Glad we could help.
Mynda
Irfan says
Hy
Me to much tired to apply following condition in excel but me fail
Condition is
If amount is less than $20000 then impose GST 9% if amount is greater than equal to 20000 than apply 7% gst
Mynda Treacy says
Hi Irfan,
Assuming your ‘amount’ is in cell A1:
Kind regards,
Mynda
Teresa says
Hi Mynda
I am hoping you can help me with a tricky formula I have been trying to get right? I have four columns but multiple conditions that could be met and multiple returns. This is working for the Year = 11 part but not the Year = 12 or Year = 13 parts. Could you please offer any advice? My formula is:
=IF(AND(D2=11,L2>=10),”OK”,IF(AND(D2=11,L26),”Maybe”,IF(AND(D2=11,L2=10),”OK”,IF(AND(D2=12,L26),”Maybe”,IF(AND(D2=12,L2=10),”OK”,IF(AND(D2=13,L26),”Maybe”,IF(AND(D2=13,L2<=6),"No",)))))))))
Any help would be greatly received.
Regards
Mynda Treacy says
Hi Teresa,
Can you please send me your workbook so I can see the formula in context. You can raise a ticket in the Help Desk and upload it there.
Thanks,
Mynda
Dave says
a4 is date; b4 is day of week; c4 is jackpot; d4, e4, f4, g4, h4 are winning numbers, i4 is empty, j4 equals number of winning numbers. i4 if equals 2 then “$1”, equals 3 then “$10”, equals 4 then, “$100”, equals 5 then, “c4”
Mynda Treacy says
Hi Dave,
Hopefully this is what you want:
Kind regards,
Mynda
Sankari says
Super, it is very useful for MIS. Thank you.
Mynda Treacy says
You’re welcome, Sankari 🙂
GEORGE says
Enjoyed learning the If statement made me understand what i missed in class
Mynda Treacy says
Glad we could help, George 🙂
bonny says
hello, i have tried to add the data analysis toolpak for excel version 2007 and 2010 but they both fail unlike that version of 2003.How else can i do this because i want to analyze data with excel, i have analyzed with excel 2003 before by adding the analysis toolpak
Mynda Treacy says
Hi Bonny,
Sorry the add-in isn’t working for you. Unfortunately we don’t provide support for the software installation problems. You could try Microsoft Support for Excel here: https://support.microsoft.com/en-us/product/office/excel
Kind regards,
Mynda
Pranjal Kumar Deka says
Please anyone help me to get this formula.
I am trying to find out a formula, if date A is less than and equal to date B then result should show Yes else No. This is simple – IF(A<=B,"Yes","No"). Now I an looking for..
1. Considering the above, if date A is 02 March 2015 and B is weekend, let say 01 March 2015 which was Sunday, in tis case I want the result Yes. If I use the above formula then it only gives No.
2. If date A is less than Date B then result will show all the months after date A. Example, if A is 01 Jan 2015 and date B is 01 April 2015 then result will show Feb, March, April.
Catalin Bombea says
Hi Pranjal,
You can continue your formula:
IF(AND(A>B),day(B)<6),"Yes",IF(A<=B,"Yes","No")) IF has a behaviour that you should be aware of: if multiple IF functions are nested, the function will STOP evaluating the rest of conditions, when it meets a condition that returns TRUE. This is the reason for placing weekend condition as the first criteria For the second problem, what are you trying to do? To show in a SINGLE cell the months between those 2 dates? I'm afraid that it's not possible to show in a single cell an array of results. Catalin
Pranjal Kumar Deka says
Thanks a lot Catalin !!
It seems its not possible for the second one..
Thanks a lot !
Catalin Bombea says
You’re wellcome Pranjal 🙂
Stephanie Quiros says
I am trying to create a time off calendar using a table with days/months. For every time a “V” or “S” is entered (representing Vacation or Sick) then I need to have 8 hours subtracted from the time granted balance. I keep trying to use the if/then for the table but I keep getting a name error. Please advise
Mynda Treacy says
Hi Stephanie,
Try this:
Where A1 contains V or S and B1 contains your time granted balance.
Kind regards,
Mynda
mike says
I am afraid that I am not very well versed in understanding how to use these functions. However, I have a weekly schedule that I put out and need to track hours worked during the week for each individual. How can I input a name so that in another column it will reflect the 8 or 12 hours that is worked over the course of a seven day period? Any help is very much appreciated, thank you!
Catalin Bombea says
Hi Mike,
Please upload a sample file with details, it’s hard to understand your situatiion from your description.
Use our Help Desk system.
Cheers,
Catalin
joe says
That was perfect. Thanks so much!!!!
I have one more Question. I need the sum of these cells to increase by 1 for every 25 totaled.
=A18*2+B18*2+C18*2
For example if it adds up to 25, I need it to be 26.
If its 0, it needs to be 0.
If its 24, it needs to be 24.
If it adds up to 165, it needs to be 172.
If it adds up to 300, it needs to be 312.
Again, Thank You so very much.
Catalin Bombea says
Hi Joe,
Try this:
=(A18*2+B18*2+C18*2)+FLOOR((A18*2+B18*2+C18*2)/25,1)
Catalin
joe says
Thank you. Worked great except that when the answer is 0 (nothing entered in cells) it needs to be 0, formula starts cell out with 1. Here is my formula
=INDEX({1,2,3,4},MATCH(F8+F13+B18+C18+D18+E18,{0,101,201,301,401},1)).
Catalin Bombea says
Hi Joe,
Then try inserting a 0 in the index range, with a corresponding value :
=INDEX({0,1,2,3,4},MATCH(F8+F13+B18+C18+D18+E18,{0,0.00001,101,201,301,401},1))
If your calculation is less than 0.00001, it will return the first index value, which is 0. Or, you can check the calculation before using the formula:
=IF(F8+F13+B18+C18+D18+E18=0,0,INDEX({1,2,3,4},MATCH(F8+F13+B18+C18+D18+E18,{0,101,201,301,401},1)))
Catalin
joe says
I need a cell to read “1” for 0< (sum of couple of cells)< 101…….same cell to read "2" for 101<(sum of same couple of cells)< 201…….same cell to read "3"for 301….and so on………please help
Catalin Bombea says
Hi Joe,
Try this formula:
=INDEX({1,2,3,4},MATCH(A1,{0,101,201,301,401},1))
Cheers,
Catalin
Michael Luu says
I downloaded the Excel Blog Worksheet for use as a cheatsheet.
Is the author okay with me sharing this information with my coworkers?
Thank you, we are not selling or making money from it at all.
Just becoming better analysts.
Philip Treacy says
Hi Michael,
Yes certainly. Thanks for sharing with your colleagues 🙂
Regards
Phil
Daryl says
I have a list in one worksheet:
Up $50
Down $65
Across $75
In the other worksheet, I have a drop down list in once cell:
Up
Down
Across
How do I get cell 2 to show, if I click on Down, then cell 2 will be $65. If I click on Up, cell 2 will be 50.
Mynda Treacy says
Hi Daryl,
You need VLOOKUP for this. Here is a tutorial on it:
Let me know if you get stuck.
Mynda
Daryl says
It worked, I was over complicating mine. It was a little more simple with your formula.
Thank You!
Mynda Treacy says
You’re welcome. Glad you got it working.
Mynda
Mushtaq says
Dear Sir
I want to add 200 to basic salaries of employees who completed 2 years. for instance, a guy D.O.J is 2005 and I want to add 200 after each 200 since his joining, please send me the formula..
Thanks & Best Regards
L.E.:
“after each 2 year” please
Catalin Bombea says
Here it is :
=FLOOR(B1-A1)/365,2)*100
Where in B1 is the actual date, and in A1 the employment date.
If you need more help, try uploading a sample file to our Help Desk.
Catalin
G.M.Salauddin says
Dear Sir
I want to know If G8 A than K8 “4”, If G8 B than K8″3″ m uf G8 C than K8 “2” please send me the formula
Catalin Bombea says
Hi,
Please create a sample file with your data, and detailed information on what you are trying to do, at this moment, the information you provided is unclear.
Thanks for understanding.
Catalin
MURALI says
Please use Formula in K8, =IF(G8=”A”,4,IF(G8=”B”,3,IF(G8=”C”,2,””)))
Oswald says
I’m working on understanding multiple if then statements by making sure I can read them properly. Am I understanding the following correctly?
=IF($G5>=$F5,IF(AND(I$3>=$F5,I$3<=$G5),1/$H5,0),IF(OR(I$3=$F5),1/$H5,0))
This piece is simple enough
IF(AND(I$3>=$F5,I$3=$F5 and I$3<=$G5 is true put 1/$H5 otherwise put 0
The second one is also easy enough to understand
IF(OR(I$3=$F5),1/$H5,0)
If I$3=$F5 are true put 1/$H5 otherwise put 0
The last overarching piece is thus,
If $G5>=$F5 is true, follow the first if statement, if its not true, follow the second if statement.
Am I understanding this correctly? Did I misinterpret the original formula?
Thank you for your time
Catalin Bombea says
Hi Oswald,
That IF formula has 3 arguments:
-the logical_test: $G5>=$F5. If this test is true, the formula from value_if_true will be calculated; if FALSE, the formula from value_if_false argument will be calculated.
-value_if_true: IF(AND(I$3>=$F5,I$3=$F5,I$3<=$G5)) to return 1/H5
-value_if_false argument has this formula: IF(OR(I$3=$F5),1/$H5,0). Here, there is a problem in the logical test: OR function has no sense here; OR function returns TRUE if at least one of its arguments is TRUE. For a single argument, OR is redundant, works the same with AND with a single argument, but also , that argument: I$3=$F5 can be used as is, without OR or AND.
Hope i was able to remove some clouds 🙂
Catalin
mildred cc says
hello, i was trying to figure out something but cant get the right formula, would u help me with this.
+- 1.00 perfect positive or perfect negative correlation
+- 0.75 to 0.99 very high positive or negative correlation
+- 0.50 to 0.74 high positive or negative correlation
+- 0.25 to 0.49 moderately small positive or negative correlation
+- 0.01 to 0.24 very small positive or negative correlation
0.00 no correlation
lets say C20 is 0.26
0.26 should be moderately small positive correlation
-0.26 should be moderately small negative correlation
i have started i
=IF(OR(AND(C20>=0.75,C20<=0.99),AND(C20=-0.99)),IF(C20<0,"very high negative correlation","very high positive correlation"……..
thanks in advance
Mynda Treacy says
Hi Mildred,
You’re better off using a VLOOKUP on a sorted list for this. You can see an example and tutorial here:
https://www.myonlinetraininghub.com/excel-2007-%e2%80%93-vlookup-sorted-list-explained
Kind regards,
Mynda
mild says
The given amount can be change to see if the formula really work.i dont think vlookup is good idea.
Mynda Treacy says
Hi Mild,
VLOOKUP is perfect for this. You should read the tutorial to understand how it works with a sorted list. I just tested it and will email you an example.
Mynda
mildred cc says
Mynda,
thank you for the help, it works good..and apologize for having doubt ..
will continue reading your post to learn more..
have a good day 🙂 !
Mildred
Mynda Treacy says
You’re welcome, Mildred. Glad you stuck with me to see it through 🙂
Mynda
John de Munnik says
I have an array of 6 numbers: e.g.
2, 12, 30, 32, 37, 40
located at A1 to F1
I need to find where each number fits as per the following format.
range 1 to 9 inclusive: range 10 to 19 inclusive: range 20 to 29 inclusive:
range 30 to 39 inclusive and range 40 to 49 inclusive.
thanks,
John de Munnik
Mynda Treacy says
Hi John,
You can use a VLOOKUP with a sorted list for that.
Let me know if you get stuck.
Kind regards,
Mynda
Linda Clark says
I need help with a formula, what I am trying to do is working with two sheets where one is recording the date an enquiry is received and whether it is received by email, phone, or dropin, the 2nd sheet is working out the numbers received on a weekly basis with the week beginning date being recorded in Column A. From the formula below it is returning the count of everything and not the count for between the two dates (A10 and A11). What am I doing wrong?
=IF(AND(Database!$A:$A>=’Stats and Calculations’!$A10,Database!$A:$A<'Stats and Calculations'!$A11),COUNTIF(Database!$D:$D,"email"))
in plain English the fomula would read
If the date the entry was put onto the spreadsheet is greater than or equal to a date on the summary sheet and less than a second date on the summary sheet then count the number of email enquiries during this time.
Catalin Bombea says
Hi Linda,
There can be many hidden problems… Including dates seen as text, not numbers.
Please upload a sample workbook to our Help Desk system, so we can see the problem.
Thanks for understanding,
Catalin
Mynda Treacy says
Thanks, BP. There are many more advanced tutorials on our blog and if you want even more you might consider my Excel Expert course.
Kind regards,
Mynda
Jeena Narayan says
Hi,
i need help with the formula
there are 3 types of incentives based on # of tickets sold
you get the highest of the ticket sold amount
Incentive Range % – 100120,150
Incentive amount for % – 100/$80; 120/$110; 150/$203
to get 100% incentive you need to sell 94 ticket
to get 120% incentive you need to sell 114 ticket
to get 150% incentive you need to sell 130 ticket
the person sold 135 tickets
how do i write the formula even though the person met quantity in all categories he will be awarded 150% incentive value of $203
Catalin Bombea says
Hi Jeena,
You can use this formula:
=INDEX({0,80,110,203},MATCH(A1,{0,94,114,130},1))In A1 should be the number of tickets sold by that person; you can change A1 with SUMIF if you want to find the tickets sold by that person from a range of data.
Cheers,
Catalin
Jeena Narayan says
Hi thanks for your help,
however not able to use the formula as I have about 45 staff with different to quality to sell
The incentive $$ amount doesn’t change but payment is based on quantity sold
please advise
regards
Jeena
Catalin Bombea says
Hi Jeena,
Please use our Help Desk to upload a sample file, without knowing your data structure it’s difficult to understand why you are not able to use that formula. Don’t forget to give all necessary details, it will help us understand your situation.
I’m sure there’s a solution for you 🙂
Catalin
Jeena says
hi Catalin,
Employee has 3 award types
Award 1 $150 – quantity to sell to get award 1 is 180
Award 2 $180 – quantity to sell to get award 1 is 200
Award 3 $220 – quantity to sell to get award 1 is 210
Actual quantity sold is 250
so in this case employee will get an award of $220 even though he has met the target amount (180;200;210) for all awards
Employee gets the maximum award out of the 3 award types
(I have factored in all the rest of the formulas except for the above criteria (which i have entered individually as per award) need a combined formula)
thanks for your help
Catalin Bombea says
Hi Jeena,
Seems that you don’t want to use our Help Desk to upload a sample of your calculations. No problem, i’ll try to help you without seeing your data, but this makes things more difficult, you’ll have to adapt the formula yourself to your data.
Even if you changed the awards values and the target sales quantities, the formula is the same:
=INDEX({0,150,180,220},MATCH(A1,{0,180,200,210},1))
In this formula, you will recognize the array of rewards: {0,150,180,220}
The function: MATCH(A1,{0,180,200,210},1) will determine where decide which is the award based on set targets . If an employee selle 205 units, the Match function will return 3 (the position of unit sold in targets array: {0,180,200,210}), and index function will return the value corresponding to the 3’rd award: 200.
For sales between 180 and 200 units, those will match between second and third targets, MATCH function will return the lowest , so the award will be 150.
The formula will return the maximum award possible based on units sold, does not matter if he fits in lower categories too.
Hope it’s clear
Catalin
Mimi says
Hi all, please help, I’m a newbie in excel and I’m trying to create a formula wherein i need to find a value in a table, and if it’s not there, it will look in another table. Please help. Thanks. I tried using IF(OR) and IF(ISNA) and so far, I have not made any progress. Thank you so much in advance for those who will help.
Mynda Treacy says
Hi Mimi,
Have you tried this formula:
https://www.myonlinetraininghub.com/excel-vlookup-multiple-sheets
I hope that helps. Please let me know if you get stuck.
Kind regards,
Mynda
Andy says
= IF(E12-1<TODAY(),"GAUGE DUE","")
IF THE TIME TO SHOW THAT THE GAUGE IS DUE HAS NOT ARRIVED, THEN SHOW BLANK. IF IT HAS ARRIVED MINUS ONE DAY, THEN SHOW THAT THE GAUGE IS DUE.
Why is this not working?
Thanks for your time.
Catalin Bombea says
Hi Andy,
There can be many reasons for that formula to give wrong results. Without seeing a sample of your data structure it’s hard to guess the source of the problem. Please use our Help Desk to upload a sample of your data with details on how it should work, this way we can give you an accurate response, not a simple guess.
Thanks for understanding,
Catalin
Andy says
Ok. Will do. Thanks!
sassityp says
Help me with this please. I am supposed to use the IF function for this formula. How would I formulate this?
4. In cell G2, create a formula using the IF function and structured references to create a calculated column that determines the value of Weeks Vacation based on the following criteria:
a. If the years employed are less than 3, the Weeks Vacation should be 2
b. If the years employed are 3 or more, the Weeks Vacation should be 3
Catalin Bombea says
Hi,
If you have in column A the start date for employees, and you want to keep things simple, try this:
If you want maximum precision, you have to take into account the leap years. To do this, you have to replace (TODAY()-A1) from the previous formula with this formula (that part appears twice in the formula above, maybe it's easier to use another column):
=(((TODAY()-A1)-SUMPRODUCT(--(TEXT(ROW(INDIRECT(A1&":"&TODAY())),"m/d")="2/29"))*366)/365+SUMPRODUCT(--(TEXT(ROW(INDIRECT(A1&":"&TODAY())),"m/d")="2/29")))
This formula will give you a precise calculation for years employed.
Catalin
Kourtney says
Hi Mynda,
What formula is appropriate to evaluate an “if/or” statement for a range of cells, rather than an individual cell? For example,
Range: A1:A10
Values included in the range: Y, N, or NA
Formula cell: A11
Desired result: If all cells included in the range of A1:A10 are equal to “Y” or “NA,” A11 should equal “Y.” If any cell included in the range of A1:A10 are equal to “N,” A11 should equal “N.”
Kind regards,
Kourtney
Catalin Bombea says
Hi Kourtney,
You can try counting “N”, if the count is higher than 0, just return “N”:
=IF(COUNTIF(A1:A10,”N”),”N”,”Y”)
Catalin
Kourtney says
Thank you, Catalin! I would have never thought about it in those terms.
Catalin Bombea says
You’re wellcome 🙂
raimohan says
please solve the formula,as the result showing FALSE…
=IF(AND(E12F12+F12*20%),”*”,IF(AND(E12F12+F12*1%),”**”,IF(E12<=F12,"***")))
Catalin Bombea says
Hi Raimohan,
Please upload to our Help Desk a sample workbook with your data structure and the desired outcome; from first look, seems that you did not covered all possible scenarios and none of the set conditions was met.
Catalin
Joanne says
Hi
I have just found your website and the way you split it into english language and then excel speak is brillant, I hope you may be able to help me. I am trying to compose a formula to check if either column A or column B, or both columns contains Yes, then it returns a 1 but I am having diffulities, please advise.
Joanne
Mynda Treacy says
Hi Joanne,
You can use IF(OR for this:
In English, if A1 OR B1 contain the text ‘Yes’, then return 1, otherwise return 0.
Kind regards,
Mynda.
Joanne says
thank you Mynda, thats great.
I will remember your web site
Kindest Regards
Joanne
Mynda Treacy says
You’re welcome, Joanne 🙂
Gary E Travnicek says
Have you experienced problems using logical functions in array formulae?
Mynda Treacy says
Hi Gary,
Do you have and example of something that’s causing you problems?
Kind regards,
Mynda.
Alan says
If I have List: “A,B,C,D,A,X,B,J,D,B,A,K,Y,M,D.H,C,”.
How do I Count if the Entry is “A” or “B”?
The Answer would be 6.
How do I Write the Excel Formula?
Catalin Bombea says
Hi Alan,
Can you tell me how is your list entered in a worksheet? Is it in a single cell, or each letter is in separate cells?
Catalin
Judy says
Most of the time with a bit or research from sites like yoursI can create IF/AND or IF/OR formulas, but I have a need for an IF/AND, OR IF/AND, OR IF/AND OR IF/AND. Even when one of the IF/AND funtions are true, I am still getting a false.
Anyone have tips on how I can do the following:
IF(AND($BF1″”,$B1=”CLOSED”,$AT1>30),”OVER”,or
IF(AND($BF1″”,$B1=”OPEN”,$AV1>30),”OVER”,or
IF(AND($BF1″”,$B1=”RESOLVED”,$AS1>30),”OVER”,or
IF(AND($BF1″”,$B1=”RESOLVED”,$AW1>30),”OVER”,or
IF none of the 4 separate functions are applicable, then “”
I have tried variations of the following, but still not working.
IF(AND($BF1″”,$B1=”CLOSED”,$AT1>30),”OVER”,IF(AND($BF1″”,$B1=”OPEN”,$AV1>30),”OVER”,IF(AND($BF1″”,$B1=”RESOLVED”,$AS1>30),”OVER”,IF(AND($BF1″”,$B1=”RESOLVED”,$AW1>30),”OVER”,””))))
Probably need to add OR functions in the mix, but I can’t figure out how to do it. Any help is greatly appreciated.
Judy says
Think I got this to work and did not need any OR statements. No need to reply at this time unless anyone has found a cleaner solution.
Mynda Treacy says
Hi Judy,
Glad you got it to work. I had a look at your formula and you have double quotes after the first AND that don’t make sense:
=IF(AND($BF1″”
I’m not sure what you’re trying to do here, but I suspect that was part of the problem with your formula.
Next time you get stuck you can email your file to us via the help desk and we can get a better idea of what you’re trying to do.
Cheers,
Mynda.
BILL says
I AM TRYING TO MAKE A MASTER PAGE I HAVE AROUND 500 INVOICES AND HAVE A SHEET NAMED INVOICE LIST IN D3 NEEDS TO CHECK SHEET 10101 CELL H5 FOR NUMBER 10101 IF FOUND CHECK H30 FOR TAXES IF FOUND THEN CELL C30 I NEED 500 CELL THIS WAY FOR PARTS LABOR TAXES NAMES AND ADDRESSES THERE MUST BE A WAY I PUT THEM IN ONE AT A TIME.
I HAVE MADE THIS WORK BUT CAN I MAKE THIS EASIER
INVOICE LIST CELL D3=IF(AND(SUM(‘2014-10101 (1)’!H5),’2014-10101 (1)’!C30>0),’2014-10101 (1)’!C30,””)
INVOICE LIST CELL D4=IF(AND(SUM(‘2014-10101 (2)’!H5),’2014-10101 (2)’!C30>0),’2014-10101 (2)’!C30,””)
Catalin Bombea says
Hi Bill,
Can you please upload a sample file with detailed informations on your problem?
You can use our Help Desk: https://www.myonlinetraininghub.com/helpdesk/
Thanks,
Catalin
Stephen B. says
Helpful post. It was the small piece of information to spawn a slew of nested logic in Excel.
Mynda Treacy says
Thanks, Stephen. Glad you liked it 🙂
Don’t go crazy with the nesting though!
KIRAN says
=IF((((((((T2>7.96,U2*1.5)+OR(T2>=8.06,U2*1.52)+OR(T2>=8.16,U2*1.56)+OR(T2>=8.26,*U2*1.6)+OR(T2>=8.36,*U21.64)+OR(T2>=8.46,U2*1.65)+OR(T2>=8.56,U2*1.66)+OR(T2>=8.66,U2*1.67)+OR(T2>=8.76,U2*1.68)+OR(T2>=8.86,U2*1.69)))))…
pls see the formula..how to solve this ? reply
Catalin Bombea says
Hi Kiran,
Nesting multiple IF functions is very tricky, you have to be very careful to the logical construction, because the function will STOP at the first match.
This is why i would use a simple index-Match formula.
Please try this :
=U2*INDEX({1.5,1.52,1.56,1.6,1.64,1.65,1.66,1.67,1.68,1.69},MATCH(T20,{7.96,8.06,8.16,8.26,8.36,8.46,8.56,8.66,8.76,8.86},1))
The trick is using 1 as the last argument of Match function , which is “telling” to Match function to look for the value that is “Less Than” the value in T20. For an example, if in T20 we have 8.00, the first value less than 8 in the array is 7.96, the result of Match function is 1, so the INDEX function will return the first value: 1.5, to be multiplied with the value from U2…
Hope it’s clear enough… 🙂
Cheers,
Catalin
Jean Batt says
Was hoping to write the formula for if the cell is Y/N then the following cells value will be add to a distant cell to total and if not it will be added to a separate cell. Might be fairly simple but seems like most explanations, but fairly new to me.
Thank you
Mynda Treacy says
Hi Jean,
I’m sorry I’m not following you. Can you please give me more specific example using cell references or send me an Excel file via the help desk.
Thanks,
Mynda.
JULIE says
thank you !!!!
Mynda Treacy says
You’re most welcome, Julie 🙂
Ali says
Hi,
Please can you help.
I am working on a spreadsheet and don’t know whether I need a conditional formatting formula or just a formula.
I have a figure in cell c2 and in cell d2 I have a drop down which highlights whether the case is “open or closed or ongoing”, how can I get the cell in c2 to read “nil” if the case has been closed in d2?
Thanks for your help guys.
Ali
Mynda Treacy says
Hi Ali,
If the figure in C2 is a result of a formula or link to another cell you could hide it with an IF formula similar to this:
Otherwise if the value in C2 is hard keyed the only other thing (other than VBA), is to use conditional formatting to format the text the same colour as the cell background when D2=”Closed”.
Note: this doesn’t get rid of the value, it merely hides it from view. If you have any formulas referencing C2 they will still ‘see’ the value.
I hope that helps. Please let me know if you get stuck.
Kind regards,
Mynda.
Ronel says
Hi,
I was wondering if you could assist in a formula
=IF(AK120,AK12*(OR(AX1226,AK12*IF(AX12=25,AK12*AX12/25,AK12*IF(AX12>25,AK12*AX12/60)))))
The column AK can be a positive or negative.
AK must be multiplied by AX and if AX is either equal or less than 25 then AK*AX/25 applies otherwise AK must be multiplied by the value in AX/60 nevertheless what the value is.
Thanks
Mynda Treacy says
Hi Ronel,
You can use this formula:
It reads: if AK1 is less than or equal to 25, then AK1*AX1/25, otherwise AK1*AX1/60.
Kind regards,
Mynda.
Ronel says
Much appreciated…I wanted to make it to complicated
Mynda Treacy says
🙂 You’re welcome, Ronel.
James Hindle says
I have a list of dates in column A from Jan 01, 2013 to August 31, 2013.
I am trying to use the IF(AND(OR……… statement in column B to state the month, eg. “January” “February” etc, . I can not get it. Too many brackets for me I guess. Can you help?
Thanks
Mynda Treacy says
Hi James,
Assuming your dates are formatted as dates and not text you can use this formula:
Will give you the month number, then if you want it to ready the name of the month you need to format the cell with a custom number format mmmm.
To set up a custom number format first highlight the cells you want to format, then press CTRL+1 > on the Number tab > under Category select ‘Custom’ > in the ‘Type’ field enter mmmm
Kind regards,
Mynda.
Helen says
Hi there. I have a problem with a marksheet for grade 12’s. All the formulas are in and are working 100%. The problem is that the column where pass/fail appears is also worked out with a formula based on the conditions set to pass. The formula is copied down and here is the problem. It is different schools that are going to use this and all of then have different number of pupils. I want to calculate at the bottom of the column the % pass and fail in the grade. I am using countif. But because all 200 is at the moment fail – (change into pass if conditions are met) and a school only have 100 grade 12’s it will be divided by 200 because that is how big the spreadsheet must be, thus percentage cannot be calculated. Is there another way to do this?
Mynda Treacy says
Hi Helen,
You need to do a couple of things:
1. Change your formula that returns a Pass/Fail to ignore blank cells. e.g.
2. COUNT the total number of grades in the range (the actual numeric grade results, not the Pass/Fail formula column), and divide the COUNTIF by the COUNT result. e.g.
I hope that makes sense. If you get stuck send me your workbook via the help desk so I can give you a specific example.
Kind regards,
Mynda.
Mohammed Mustafa says
=CONCATENATE(“#”,Sheet2!A3,”,”,Sheet2!A4,”.”,Sheet2!A5,”#”)
I have this formula and I need to put the number format #,###.##
the problem is when i have nomber less than 1000 it appears this way 0,###.00 but I need it without the zero & the coma
Will ppreciate if somebody could help me in this
Thanks
Mohammed
Mynda Treacy says
Hi Mohammed,
As I mentioned in my reply to your help desk ticket, you can’t use a custom number format that deals with <1000 and >1000 differently since you can only stipulate formats for positive, negative and zero values.
I recommend you use this formula:
And format the cell with a custom number format “#”#,##0.00″#”
This way if the amount is more than 1000 the custom number format will do the work, and if it’s less than 1000 the value will be formatted as text with the necessary formatting you want.
Kind regards,
Mynda.
Abhishek says
Need excel sheet formula for this statement. we have 200 workers
If once salary is 12001 and mess bill above 2250 get restricted to 2250 or actual value. pls help
Mynda Treacy says
Hi Abhishek,
I’m sorry I don’t fully understand your question. I’m assuming you are saying the figures are for each worker and not all 200. If so:
The above formula reads; where the salary in A2 is greater than or equal to 12001 and the mess bill in cell B2 is greater than 2250 then 2250 otherwise the amount in B2.
Kind regards,
Mynda.
Christene Ellis says
I am in advanced excel and am having trouble understanding the “if” an “or” formula together. Thank you for sharing your knowledge. I don’t know too many people who are familiar with the advanced excel.
Mynda Treacy says
You’re welcome, Christene 🙂 Glad I could help.
Peter Haviernik says
I have noticed the IF AND formulas with mixed data (numerical and text) evaluate the text ones as some numbers and place them in an upper numerical range). This happens at conditional formating, too.
pH
Mynda Treacy says
Hi Peter,
I’m sorry I’m not following you. Do you have an example?
Cheers,
Mynda.
leah says
i need to use the IF/OR/AND function together
We just had our yearly fundraising event and i would like to know any person who donated $100 or more in the past years but not this year. i have columns for each year column. columns N and O is for year ’13. i did not know how to enter an empty (blank), so i used =100,I4>=100,J4>=100,K4>=100,L4>=100,M4>=100(AND(N4<1,O4<1))),"no13","")
Mynda Treacy says
Hi Leah,
You can use this formula:
Note: The COUNTIF tests that all previous 5 columns are greater than or equal to 100.
Kind regards,
Mynda.
Michael says
Hi Mynda,
you are my last hope, I have searched the internet up and down with no results.
What I am trying to do is to find if a date in a range of cells (F3:F200) is less than TODAY() and Conditional Formatting (colouring) a single cell accordingly, green if false or red if true. Some of the cells can contain text (N/A) instead of a date.
Please help.
Kind regards,
Michael
Carlo Estopia says
Hi Michael,
Here are links that may help you:
Conditional Formatting with Formulas
More about Conditional Formatting
Anyhow, I am going to share to you some of my take on this one.
I’ll use formulas in formatting here.
1) Home
2) Conditional Formatting
3) New Rule
a) choose "Use a formula to determine which cells to format" b) enter a formula like this 1st condition/rule (less than/equal today) : =F3<=NOW() applies to : =$F$3:$F$200 2nd condition/rule (beyond today) : =F3>NOW() applies to : =$F$3:$F$200 3rd condition/rule (N/A) :=ISERROR(F3) applies to :=$F$3:$F$200Cheers,
CarloE
Michael says
Hi Carlo,
thanks for your answer. It is not exactly what I am looking for. I will try to better explain my problem.
I want to turn cell F1 (and only F1) red if any (or more) of the cells F3:F200 contain a date with a lesser value than obtained with TODAY() (or NOW()). Some of the cells (F3:F200) can contain text (N/A) instead of a date.
I am already using Conditional Formatting to colour cells F3:F200, but, as the first 2 rows are frozen, and I am working at the end of the list, it is easy to miss some of the alerts because they are out of sight. Hence the requirement to have the alert appearing in cell F1 if any of the cells in this column are highlighted.
I hope this makes it clearer. Please let me know if you need more information.
Thanks and kind regards,
Michael
Mynda Treacy says
Hi Michael,
You can use this formula in your conditional formatting rule:
Where it applies to: $F$1
If you get stuck please send me your file via the help desk so I can see what you’re working with.
Kind regards,
Mynda.
Carlo Estopia says
Hi Michael,
I see. If that’s the case then you can add a new rule in F1.
Just follow the same procedure.
Note: dtDate is a named range from f3:f200
Cheers,
CarloE
Michael says
Hi Mynda, Carlo,
I was pretty close, having fiddled with COUNTIF, but it would have taken me weeks to find this solution.
Would you be able to suggest articles regarding the use of “”, $, &, etc., to further my understanding on why you have used them the way you did in your formula?
Thanks heaps to both of you, you made my day.
Kind regards,
Michael
Mynda Treacy says
Thanks, Michael. Glad we got there in the end.
In terms of other articles you can read:
For the $ – Absolute References
For the “” used with COUnTIF – The COUNTIF function requires the operator to be enclosed in double quotes and concatenated (using &) to the zero. It’s just the way it works! With other formulas you don’t need to always use “” and &.
You can read more on COUNTIF here.
And more on Conditional Formatting using formulas here.
Plus you can find a list of tutorials on Excel Formulas here.
I hope that helps.
Kind regards,
Mynda.
Bhim Prasad Guragain says
Hi Mynda,
could you help me to define the tax amount calculation in excel sheet if following conditions:
Income: 480,000 Yearly
1) upto 250,000 1% tax to be calculated B1 Column
2)More than 250001 less than 350,0000 15% C1 Column
3) Exceed 350,0000 tax 25% C1 Column
Bhim Prasa Guragain
Nepal
Catalin Bombea says
Hi Bhim,
Considering that the Income value is in cell E1, use this formula to calculate tax:
=E1*INDEX({0.01,0.15,0.25},MATCH(E1,{0,250000,350000},1))
Cheers,
Catalin
Gary E Travnicek says
I like how clean your solution is.
Mynda Treacy says
Thanks, Gary 🙂
Leslie Jones says
Hi Mynda and Philip,
I am trying to build a formula that will return a 1,2,3, etc. based on comparing a value in row 7 to values in row 8.
Example: If i7>=d8 and i7=g8 and i7=D8, i7=g8, i7<=h8, 3,""))
Thanks for your time and wealth of information!
Leslie Jones says
Correction to my request and example:
Request formula to give me the following numbers, else give me a BLANK:
(If i7>=d8 and i7=g8 and i7=$D8,K$7=$G8,K$7<=$H8),3,""))
Thanks again! Leslie
Mynda Treacy says
Hi Leslie,
From what you’ve written I’d say you need this:
=IF(AND(i7>=d8,i7=g8,i7=$D8,K$7=$G8,K$7<=$H8),3,"") i.e. every condition must be TRUE to return 3 otherwise return blank. Kind regards, Mynda.
Sandra Carpenter says
I need help writing excel conditional formula to shade either red or green if the following conditions are met:
1. If the value is +/- (plus or minus) 10% and greater than $50K (shade green if positive) or
2. If the value is +/- (plus or minus) 10% and greater than $50K (shade red if negative)
Worksheet column layout example:
(SV = BCWP-BCWS) (CV = BCWP-ACWP)
need to apply conditional formatting to SV and CV values
BCWS BCWP ACWP SV CV
5000 4000 2500 -1000 1500
I can apply the color code okay, it’s the formula that I need help with assuming I should probably use IF, AND, OR functions. Thank you
Mynda Treacy says
Hi Sandra,
I’ve written a tutorial on Conditional Formatting using formulas here:
If you get stuck the best thing is to send me your workbook via the help desk so I can give you a tailored solution.
Kind regards,
Mynda.
Susan says
In Column A I have text responses such as NBDF (Next business Day Fix) being the SLA. In column B I have codes for the responses such as NBD or 4BH.I want to show if SLAs are being met. Eg. if the SLA is NBDF or PN2D and the Response is NBD or 4BH then the SLA is met (True). I think I can use the IF(AND logic but need to understand the format when multiple values in Column A and Column B are to be used.
Please help
Mynda Treacy says
Hi Susan,
You need to write your formula like this:
Kind regards,
Mynda.
Neels says
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?
Mynda Treacy says
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.
Neels says
Hi Mynda,
Thanks for your response. I have in the meantime discovered that I overlooked the basics of preparing the destination cells before entering the formula, though; they must be numeric and not text!! Value 1 is returned if the cells in column F have the same value. Formula working excellently!
Regards
Neels
Mynda Treacy says
Great. Glad you figured it out.
Dave says
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
Mynda Treacy says
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,
Dave says
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.
Dave says
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
Mynda Treacy says
Great. Well done, Dave 🙂
Thanks for letting me know.
Marty says
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
Mynda Treacy says
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.
Marty says
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
———————–
Mynda Treacy says
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.
Felipe Tijerina says
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
Mynda Treacy says
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.
Jeet says
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.
Mynda Treacy says
Hi Jeet,
I’d use a VLOOKUP on a sorted list for this.
Kind regards,
Mynda.
Jeet says
Thanks xl Guru
Mynda Treacy says
🙂 You’re welcome, Jeet.
Jeannine says
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.
Carlo Estopia says
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
Debbie says
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
Mynda Treacy says
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.
knodge says
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
Carlo Estopia says
Hi Knodge,
I suppose you have sent this via Help Desk right?
Please don’t reply here anymore.
Cheers,
CarloE
andrea says
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
Carlo Estopia says
Hi Andrea,
Try this:
Cheers,
CarloE
Suzanne says
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?
Carlo Estopia says
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
Kevin says
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
Kevin says
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!
Carlo Estopia says
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
Kevin says
Thank you!! You are awsome. I’ve been recking my brain on this formula for several weeks. thanks again,
Kevin
Carlo Estopia says
Hi Kevin,
On behalf of Mynda, You’re Welcome.
Cheers.
CarloE
Sheldon says
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)
Carlo Estopia says
Hi Sheldon,
Try this formula:
Try this data-results:
9000 - 22.5
10000 -32.5
351000 -1372.5
Cheers.
CarloE
Abbas Khalid Ali says
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
Carlo Estopia says
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
Maria E Jesus says
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
Carlo Estopia says
Hi Maria,
Please use this formula:
Cheers.
CarloE
Kris says
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.
Kris says
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.
Carlo Estopia says
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
Peggy says
=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?
Carlo Estopia says
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
Shazif says
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
Mynda Treacy says
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.
Shazif says
Thanks a ton…
And again I need to salute you 🙂
liezl says
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.
MikeBanawa says
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
Patel Neel says
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.
Mynda Treacy says
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.
Neel says
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.
Neel says
I found out how to freeze the rows and columns.
Patel Neel says
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!`
Mynda Treacy says
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.
Neel says
Hey Mynda Treacy,
Thank you very much. I really appreciate your work…. 😀
Neel says
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…
Mynda Treacy says
Hi Neel,
Kind regards,
Mynda.
Mo says
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.
Mynda Treacy says
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.
chandu says
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
Mynda Treacy says
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.
chandu says
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.
praveen says
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
Mynda Treacy says
Hi Praveen,
You need a nested IF function.
Kind regards,
Mynda.
Cheryl says
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-
Mynda Treacy says
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.
Alicia says
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!
Mynda Treacy says
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.
Carlos says
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
Carlos says
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
Mynda Treacy says
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.
Carlos says
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!!!!!!
Mynda Treacy says
Hi Carlos,
Thanks for your kind words. You can send me an Excel file via the help desk.
Kind regards,
Mynda.
carmen says
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
Mynda Treacy says
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.
jed says
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.
Mynda Treacy says
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.
bhavanishankar sunkara says
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.
Mynda Treacy says
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.
KAPIL says
Respected Sir,
I want learn all excel formula so send me any guidence & formula
I hope you will send me formula
Tahnking you,
Mynda Treacy says
Hi Kapil,
You can find an index of Excel formulas here.
Kind regards,
Mynda.
kayakbob says
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
Mynda Treacy says
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.
BP Bhattarai says
Hi,
Its really good to learn but not enough to get advanced. Help make people advance.
Out of four or more criteria, if any two are true, how to devise a formula to categorize those items?
Please help!!
Catalin Bombea says
Hi Bhattarai,
If you have 4 criterias, you can try something like this:
=IF(OR(AND(N4=M4,N3=M3),AND(P4=O4,P3=O3)),”True”,”False”)
If you have more criterias, you may need another approach, but i’ll need more details about your data structure and the criteria setup. You can use our Help Desk to create a sample workbook and upload it.
Catalin
Mynda Treacy says
Hi BP,
Catalin beat me to it 🙂
Here was my suggestion:
Where 1=1, 2=2 etc. can be replaced with your logical tests.
Kind regards,
Mynda
Catalin Bombea says
At least, i can try 🙂
Another way might be:
But all formulas will work with a specific criteria structure, so they are all good solutions, depends on specific requirements. Sumproduct requires perfect match (the position of the element in range is important), Countif is not position sensitive, but is sensitive to duplicates…
The test file is here: OneDrive
Catalin
Elly says
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.
Mynda Treacy says
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.
Elly says
Thank you so much – have applied that to my real spreadsheet and it works a treat – fantastic. Am recommending the site to colleagues!
Mynda Treacy says
You’re welcome. I appreciate you spreading the word.
Kind regards,
Mynda.
Chathurika says
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
Mynda Treacy says
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.
Danabalan Selvapandi says
=+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)
Mynda Treacy says
Hi Danabalan,
What’s your question?
Kind regards,
Mynda.
Susan says
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!!
Mynda Treacy says
Hi Susan,
Your formula will be:
=IF(J24>50,(J19+J20+J21+J22)/2,J19+J20+J21+J22)
Kind regards,
Mynda.
chidike N says
Hi Mynda,
I am trying to create a formula that will return a value of either “Over hours”, “under hours” otherwise “OK” but its not working as planned.
IF(AND(OR(Q4>=S4+12,”over hrs”,”Ok”,IF(Q4=(s4+12) then return value “over hours” but if cell Q4 is <=(s4-12) then return value "under hours" otherwise it should return "Ok"
Catalin Bombea says
Hi Chidike,
This is the correct syntax for your formula:
Catalin
ASHISH BHARDWAJ says
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
Mynda Treacy says
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.
Subash says
Hi Mynda,
What Ashish means is:
The meter reading is 1000
We charge 3.54 upto 200
We charge 3.89 above 200
Therefore his formula should be
=IF(A1200,(200*3.54+((A1-200)*3.89)),0))
His answer will be 3820.
Thanks and best regards,
Subash
Mynda Treacy says
Cheers, Subash. I think the comment editor stole the rest of my formula like it’s doing for you too.
Let’s try again:
Hopefully that will come out with the complete formula.
Cheers,
Mynda.
RV says
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.
Mynda Treacy says
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.
Viktor Carmona says
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?
Mynda Treacy says
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.
mohamed says
i like this website so much
we learn from youall what we need
Mynda Treacy says
🙂 Cheers, Mohamed. Glad you like it.
Todd says
nice ideas, thanks
Mynda Treacy says
Cheers, Todd 🙂
Ginette says
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
Mynda Treacy says
Hi Ginette, Glad to have helped 🙂
Kind regards,
Mynda.
ABHAY says
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.
Mynda Treacy says
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.
Lachlan says
this blows my mind. will have to go over it again when I can concentrate more!
Robert Wilson says
thanks for the post
Philip Treacy says
hi Robert,
Glad you liked it. Hope we are providing more useful info in our other blogs too.
Phil
sam says
E4= 500, How can i sum this in %, that is if you achieve 100% you get 5000, achieve 120% you get 6000, achieve 140% you get 7000 and achieve 160% you get 8000. please help.
Thanks
Catalin Bombea says
Hi Sam,
Which value is considered to be 100%?
You can adjust this formula, i considered 5000 to be the value for 100% (change the E4/5000 to your 100% value):
=INDEX({0,5000,6000,7000,8000},MATCH(E4/5000,{0,1,1.2,1.4,1.6},1))
Cheers,
Catalin