Last week we tapped into the insight of Excel logical operators that are used to compare data in different cells. Today, you will see how to extend the use of logical operators and construct more elaborate logical tests to perform more complex calculations and more powerful data analysis. Excel logical functions such as AND, OR, XOR and NOT will help you in doing this. Continue reading
Comments on: Using logical functions in Excel: AND, OR, XOR and NOT
by Svetlana Cheusheva, updated on
Comments page 15. Total comments: 567
I have a problem regarding a calculation of commission total in a month with a condition that if actual sales is greater than the weekly target sales in all four weeks in a month plus monthly sales is also greater than the target monthly sales, then commission will be paid as a certain percent of the monthly sales, if in any week actual sale is greater than target weekly sale but not in all 4 weeks then commission will be paid as a certain percent of weekly sales, it will be not paid as a certain percent of monthly sales, even though monthly sale is greater than target monthly sale. Otherwise,if actual sale is not greater than target sale in any week, no commission will be paid at the end of the month.
Hello
I have a problem in a scenario of 1q1
if cell value in minus than "Extra" and if 0 than "OK" and greater than 0 than Short
suggest some formula
Excel A1 cell 6Doz+ 6pics so how formula use and create answer 78 pls help
Hi
I have a cell(W28) which has only 2 drop down "Yes" and "No". But this affects my formula in cell (W42).
If (W28="Yes") - I have to use formula as (A+B^0.5)/C
If (W28 ="No")- I have to use formula as (A+(B/2)^0.25)/C
I am not able to use the the formula based on my selection in Cell (W28).It gives only one value based on the formula i put
Here is my formula -
=IF(W38="Yes",(((W39*(W31/2)^0.5)/W40)^1.33),(((W39*W31^0.5)/W40)^1.33))
Can anyone please help
Hi, I'm trying to find a way of achieving the following,
if A1 has a text entry of "W" then add 1 to cell B5, if A2 has a text entry of "L" then add 0 to C5. These have to be interchangeable so if A1 has a "L" entry enter 0 to B5, and if A2 is "W" add 1 to C5
Hello Graham,
You can use the following formulas.
For B5:=IF(A1="W", 1, IF(A1="L", 0, ""))
For C5:=IF(A2="W", 1, IF(A2="L", 0, ""))
hi
I Have 1 Sheet How i Can See On Sheet All negative value In One Sheet
Example
Name Total Amount
Ram 60
Sita -50
Hari 150
Gopal -140
Like That
i Need all Navigate Amount
Hello, i am trying to create a formula similar to this but the result i am getting is "#NAME?.. Can you please help me out..
IF(C2=ic/Freelancer, "5", IF(C2=Temp/Contractor, "1"))
Thank you
I have 3 columns
A: has member ID
B: Date
C: Trainer ID
I want to highlight members who saw different trainers on the same day..
Will this work?
=IF(XOR(AND((A3=A2),(B3=B2)),(C3=C2)),0,1)
Appreciate your help
Hi Asiya,
Try the following formula:
=IF(COUNTIFS(A:A,A1,B:B,B1,C:C,C1)>1, IF(COUNTIFS(A:A,A1,B:B,B1) - COUNTIFS(A:A,A1,B:B,B1,C:C,C1) = 0,"not different", "different"),IF(COUNTIFS(A:A,A1,B:B,B1)>1,"different","not different"))
I have a cell (A1) that can contain the values of 6, 12 or 18.
If A1 is 6, I want cell B2 to show 0; if A1 is 12, I want cell B2 to show 12; if A1 is 18, I want cell B2 to show 18.
I've tried various combinations using IF and OR but can't arrive at the desired conclusion. Can you help?
Hello Uncle Dave,
Try the following formula:
=IF(A1=6, 0, A1)
Hi,
working on an excel with a lot of tabs.
I need a sum, that checks different values, and sums them if all of them are true. Only the last part of the formula should check, if either one of the two conditions are true.
Currently I am working with Sumifs, that works pretty good, except for the last part, where it should only sum, if one of the two values is true.
This is the formula:
=SUMIFS(Export!$D:$D;Export!$G:$G;B$17;Export!$Z:$Z;"2016-11-*";Export!$H:$H;"Forecast*";Export!$K:$K;"Open";Export!$AH:$AH;"*Medium*";Export!$AI:$AI;"*Medium*")
So basically it should sum the values, if "Medium" is mentioned in the column AH AND/OR AI .
Thank you very much for your help.
Hi Jean-Luc,
Try the following formula:
= SUMIFS(Export!$D:$D;Export!$G:$G;B$17;Export!$Z:$Z;"2016-11-*";Export!$H:$H;"Forecast*";Export!$K:$K;"Open";Export!$AH:$AH;"*Medium*") + SUMIFS(Export!$D:$D;Export!$G:$G;B$17;Export!$Z:$Z;"2016-11-*";Export!$H:$H;"Forecast*";Export!$K:$K;"Open";Export!$AI:$AI;"*Medium*") - SUMIFS(Export!$D:$D;Export!$G:$G;B$17;Export!$Z:$Z;"2016-11-*";Export!$H:$H;"Forecast*";Export!$K:$K;"Open";Export!$AH:$AH;"*Medium*";Export!$AI:$AI;"*Medium*")
IF SALARY >= 5565.61 THEN SALARY X 12.5%
IF SALARY >= 3566.05 OR SALARY =1783.84 OR SALARY <=3566.04 THEN SALARY X 17.5%
IF SALARAY <=1783.83 THEN SALARY X 20%
How i write this in excel? need help
IF SALARY >= 5565.61 THEN SALARY X 12.5%
IF SALARY >= 3566.05 OR SALARY =1783.84 OR SALARY <=3566.04 THEN SALARY X 17.5%
IF SALARAY <=1783.83 THEN SALARY X 20%
Hi Svetlana,
I need help to solve the following problem.
If Line A2 Have a Word "TEXT" & B2 is Greater then B1 Then A2 otherwise Goto Next Line Number A3, if A3 Fines then stop other wise Go to A4,A5,A6,A7 So on ... on ... on... till A500 until Criteria Match with above said statement
I look forward to hearing from you soon. Thanks in advance.
AdiL
Hi Svetlana,
Need your help in calculating the following problem
Volume Product Type
200 A
300 B
200 C
200 A
100 B
All the product type columns are selected via drop down list using data validation option in excel. My problem is that i need to sum up each product type separately.So can you help me out in this..
Hi Shash,
Please use Pivot Tables to solve your task.
Hi Svetlana,
I need help to solve the following problem.
Cells
A B C D E
1 Yes Yes Yes Yes Yes
2 Yes Yes Yes No No
3 No No No No No
4 Yes Yes Yes
Svetlana,
If all cells in Row 1 = Yes then response is Yes in cell E1
If any cell in Row 2 = No then response is No in cell E2
If all cells in Row 3 = No then the response is No in cell E3
If any cell in Row 4 has a blank then response in E4 is blank, empty.
Hope you can help with a formula.
Hi Henry,
Try the following formula:
=IF(COUNTIF($A1:$E1, "")>0, "blank", IF(COUNTIF($A1:$E1, "yes")=5, "yes", "no"))
Hello, What is this? i tried to post something, but after send i got different how i sould i delete this post :(,
Guys if you have any excel issue please try to use Mr. excel. Perfect Site to learn excel
"=IF(AND(A2="White",B21),15,IF(AND(A2="Not white",B21),12,0))))"
=IF(AND(A2="White",B21),15,IF(AND(A2="Not white",B21),12,0))))
=IF(AND(A2="White",B21),15,IF(AND(A2="Not white",B21),12,0))))
Hello Svetlana,
*Something went wrong in my previous question. Here's the correct question.
I have been trying to crack this for quite a while now. Your help will be much appreciated.
I am trying to achieve the following rate/pc:
If color is white and pcs is less than or equal to 1, rate/pc = 10
If color is white and pcs is greater than 1, rate/pc = 5
If color is not white and pcs is less than or equal to 1, rate/pc = 15
If color is not white and pcs is greater than 1, rate/pc = 12
I have been able to get the first 3 conditions correct using if(and) and if(not) however I can't get the 4th condition to work. Is there a formula for 2 not conditions?
I look forward to hearing from you soon. Thanks in advance.
=IF(AND(A2="White",B21),15,IF(AND(A2="Not white",B21),12,0))))
Hello Svetlana,
I have been trying to crack this for quite a while now. Your help will be much appreciated.
I am trying to achieve the following rate/pc:
If color is white and pcs is =1, rate/pc = 5
If color is not white and pcs is =1, rate/pc = 12
I have been able to get the first 3 conditions correct using if(and) and if(not) however I can't get the 4th condition to work. Is there a formula for 2 not conditions?
I look forward to hearing from you soon. Thanks in advance.
what formula can i use for example if cell a1:g1 is 60..i want to sum a1:g1 in one cell to be 40 and excess (20) in another cell?
Sir
How can import a particular Colum from PDF to Excel file how ever i have many of PDF files and I want Import a particular Colum (which contains some value) from PDF to Excel Please Help for this formulla or Micro Please and Please
hello, i am stucked while preparing a XL for my investments. Every investment has end date. so I want to put formula in such way that when it comes to the end date status should show "mature" or else show "active".
Please help.
Help in Excel formula that is If sheet 1 cell C2 are equal or greater than 21 then copy data sheet 1 cell A2 to C2 and paste in Sheet 2 A2 to C2 if not equal 21 then do not copy data
Hello Nisar,
Enter the following formula in Sheet2 A2, and then drag it rightwards up to cell C2:
=IF(Sheet1!$C$2>=21, Sheet1!A2, "")
i AM HIGHLY THANK FULL FORMULLA WORKS
A1 B1 C1 D1 E1 F1 D1
_________________________________________________
1450 1440 1073 301 66 73 D2
HOW I USE FUNCTION IN G2 CELL IN ABOUT CONSTANT B1 WHEN F1 IS GREATER THAN EQUAL TO E1
OTHER WISE MINUS E1 FROM B1 WHEN E1 IS GREATER THAN F1
Hello LAXMIDHAR,
If my understanding of your task is correct, you can use the following formula:
=IF(F1>=E1, B1, B1-E1)
hi, can anyone help me?
I don't to write like this
=IF(A1=10,F1=0,IF(A1=12,F1=0,IF(A1=13,F1=0,IF(A1=16,F1=0,IF(A1=18,F1=0,IF(A1=90,F1=0,IF(A1=91,F1=0,IF(A1=92,F1=0,IF(A1=93,F1=0,10000)))))))))
I want a shorter
Hi!
You can use the OR function instead of nested If's:
=IF(OR(A1=10, A1=12, A1=13, A1=16, A1=18, A1=90, A1=91, A1=92, A1=93), 0, 10000)
I want copy particular cell formula in google sheet and I want to paste special to other 3 column which macro program I have to use..
Hello! Good evening.
Pls guide me solve the problem.
1- If Table A1 value is 1, B1 is 54,
2- In continuously If A2 value is 0, b2 value is 0.
then whats the formula apply to get the total no. of receipts in both tables C1,C2. Pls give the one formula for both tables.
Recpt. No.
From To Total
1 54 ?
0 0 ?
Hello, Prem,
For us to be able to assist you better, please give us the result you need in C1 and C2.
Hi,
I need a formula whereby, when the value ranges from -0.03 to 0.03, it should appear as True and for other values it should appear as False.
i have a question..there are 4 subjects namely s1,s2,s3,s4 each 100 marks in a single group .a student should get aggregate morethan 200 to pass group and he must get 40 or above in each subject.if he get morethen 200 and failed in one subject than he is nt qualified for group........solve me showing pass/fail in each subject along with group
Can you help me with this? If A1:B1 match any A:B then add matching C cells?
Hello, TJ,
Please try this formula:
=IF(AND(INDIRECT("A1")=A3,INDIRECT("B1")=B3),"Matching","Non-matching")
A1 = RECEIVED / CANCELLED / DECLINED (Dropdown)
If A1="CANCELLED" then A2, A3, A4... will show CANCELLED
If A1="DECLINED" then A2, A3, A4... will show DECLINED
but
If A1="RECEIVED" then A2, A3, A4... must be blank
Please advise. Thank you
GOTCHA! :D
Hi, I am lost. can you help me with this problem.
I want to choose from a drop down list for the following
1) choose either ATM or CDM
2) choose either Shift1 or Shift2
3) choose the units available example 100, 200, 300 etc
4) choosing from 1) and 2) and 3) will get the result
the result will show
ATM Shift1 100 units = xxx
ATM Shift1 200 units = yyy
ATM Shift1 300 units = zzz
CDM Shift1 100 units = aaa
CDM Shift1 200 units = bbb
CDM Shift1 300 units = ccc
ATM Shift2 100 units = ddd
and so on
Is it possible? Please advise. Thanks
Hello, Michelle,
You can read how to create a dropdown for a worksheet in Making a cascading (dependent) Excel drop down list. For us to be able to assist you better, please describe the condition for the expected result.
=IF(AND(C26>=0,C26=3,C26=5,5,"error")))))
Hi
I have a 3 in one if statement to put value to certain conditions:
=IF(AND(C26>=0,C26=3,C26=5,5,"error")))))
If I make the last condition = 5, it gives me the "error" msg for anything else typed in - which is correct. When I say >5 it gives me 5 for any word typed in which should be giving me "error" msg? How do I fix this?
Thank you.
Hello, Shirleen,
This formula isn't true since a value cannot be equal to 3 and 5.5 at the same time. We suppose that you need a formula like this:
=IF(OR(C26>=0,C26=3,C26=5.5),"error", "")
Hi Svetlana!
I need assistance calculating the following information in Excel 2013, if possible. If you can simplify, please do.
--
* The “Severity” and “Status” of a case, determines how often the case should be updated
* Based on the “Last Worked” date, automatically calculate the “Due Date” for updating the case. The “Due Date” is based on business days and does not include U.S. holidays.
* Automatically countdown the difference (“Days Remaining”) between the “Last Worked” date and the “Due Date”
* Based on the “Status,” determine whether the case is “On Time,” “Overdue,” or “Completed”
--
Sev 1: If status is “In Progress” or “Customer Response Received”, update case everyday (1 day)
Sev 2: If status is “In Progress” or “Customer Response Received”, update case at least every 1 - 2 days
Sev 3/4: If status is “In Progress” or “Customer Response Received”, update case at least every 2 - 3 days
Regardless of the severity, an update is not needed for cases with a status of “Closed” or “Resolution Provided.”
--
Example 1 (based on current date 11/2/2015):
Case: 1234543 | Sev: 1 | Status: In Progress | Last Worked: 10/22 | Due Date: 10/23| Days remaining: -9 | Progress: Overdue
Example 2 (based on current date 11/2/2015):
Case: 5432123 | Sev: 1 | Status: Resolution Provided | Last Worked: 10/31 | Due Date: 11/2| Days remaining: | Progress: Completed
--
Thank you, in advance!
Hey Svetlana,
I might be going past what Excel is capable of doing, but I'm trying to find a way for a function in excel to filter information from a cell(ie 1.2.3.4 in A1, to 1 in A2, 2 in A3, 3 in A4, and 4 in A5), and I've figured that one out using a LEFT, MID, RIGHT combination.
The problem comes when I put it into practice, because I have some that I can use a simple =LEFT(A1,1) =Mid(A1,3,1) =Right(A1,5,1) etc. and then I have some that I can't use that for(ie 12.34.56.78 or 123.456.789.123)
Essentially I'm trying to find a way to have excel differentiate, or at the very least stop when it gets to the "." I've been looking for a few hours, and I'm pretty good at figuring out how to do things in unorthodox ways, but I've hit the limit of my knowledge on this one.
I filter through large sums of data a day, so I'm trying to find a way to simplify this instead of typing these things out hundreds of time. Any advice you can give would be appreciated
i want to determine date taxaton
A1=date(21/10/2015
B1=date(19/10/2015
I want to show in C1
I A1 date later then B1 date C1date=B1date
Hi Svetlana
When all the cells are blank. As the cells are filled "pass"/"fail", then filled appropriately.
Thanks
Hi Phil ,
Then you can add one more AND function that checks for blank cells:
=IF(AND(D45="", D46="", D47="", D48=""), "", IF(AND(D45="Pass", D46="Pass", D47="Pass", D48="Pass"), "Pass", "Fail"))
Alternatively, you can use COUNTIF to make the formula more compact:
=IF(COUNTIF(D45:D48, "")=4, "", IF(COUNTIF(D45:D48, "pass")=4, "Pass", "Fail"))
Hi
I have a column of cells in which "pass" or "fail" is entered. I have a formula in the last cell (D49) set to auto fill, as an overall "fail" if only one of the cells has "fail" in it.
=IF((AND(D45="Pass", D46="Pass", D47="Pass", D48="Pass")), "Pass", "Fail")
This is what I want. However, when the other cells are blank, it auto fills to "fail", as no "pass" is present. I would like D49 to be blank if there is no text in the Cells D45 to D46. What can I add to the formula to keep it blank if cells D45 to D48 are blank?
Thanks
Hi Phil,
Do you want D49 to be blank when all the cells (D45 to D48) are blank or when at least one cell is blank?
Sir,
Thanks for Ablebits.
It helped me lot about how to make combined use of IF, OR, AND logical functions.
Regarads,
P.G. Kerkar
Goa.
My question is related to And function.
I have the below Duration in Dates
Phase 1 Start : Phase 1 End | Phase 2 Start : Phase 2 End.
October 1 : October 5 | October 4 : October 10
I am comparing each October date if it falls in Phase 1 then return "X" Value and if falls in Phase 2 return "Y" Value.. However if you see OCT 3 and 4th falls in both Phases how to generate "Z" Value for those dates.
what logic should i use for developing a duty roster of 40 people with the conditions as:
No repetition of duty till each out of 40 personnel performs duty.
No two personnel from same section within a week.
HI i need help. im trying to ask excel to evaluate a less than but greater than scenario. for example: IF(A3 is greater than 1 but less than 100) , "...." ... Anybody?
Hi Francisco,
You can use an AND statement, like this:
=IF(AND(A3>1, A3<100), value_if_true, value_if_false)
Hi,
I need a Function that is yes and no column are multiple as below, how use function.. i have tried =if(and(
FUNCTION, But not able, plz help
South9601 NO South9601 NO 0 N/A
South9567 Yes South9566 Yes SOUTH7065 Yes
SOUTH5965 NO SOUTH5965 NO SOUTH5965 NO
SOUTH7146 NO SOUTH7146 NO SOUTH7146 NO
SOUTH5067 NO SOUTH5067 NO SOUTH5067 NO
South9734 Yes South9574 Yes 0 N/A
SOUTH8400 NO SOUTH8400 NO SOUTH8400 NO
SOUTH8288 NO SOUTH8288 NO SOUTH8288 NO
I appreciate your skill & devotion to make known Excel Technology to the world. By the help your free Excel tutorials, I have developed my Excel skill & used it to solve many practical problems. Eg. I have created a smart Excel format to identify the least bidder with a short period & with 100% accuracy.
Dear Sir: I have one question to be answered.
I'm a teacher in a high school and a home room teacher in particular for 50 students. They are learning 10 subjects in a year. Thanks for your smart Excel Tutorials that enables me to manage the students mark using Excel like their total mark, average, Rank and their mark analyses. But at the end the year I'm still unable to decide (using Excel formula) whether each student is passed/Failed under the following conditions.
A student failed/Detained if.....
1. His total average mark is < 51% & scored <50% by one subject OR
2. His total average mark is <53% & scored <50% by two subjects OR
3. His total average mark is <55% & scored <50% by three subjects OR
4. Failed by any four or more subjects.
Thanks in advance
Maru from Ethiopia
hi,
I have a column with 500 numbers from A1 to A500 , ranging from 1 to 999,999 and numbers ending in M ( million ). I want a IF function to convert them all to numbers without letter M ( million ).For example i want 1.7M to be converted to 1,700,000. Please help
Hi,
I have a spreadsheet that has three columns of 200 lines
Column A has values of I,II,III and IV
Column B has values of A,B,C,D,E,F
Column C would be either High, Medium or Low based on the IA,IB,IC, IIA, IIB etc
What is the calculation for this.
To further complicate things I also need High to fill the cell Red background, Medium to fill the cell Orange background and Low to fill the cell Green background.
Its driving me mad.
=IF(AND(O13="",I13="",S13="",W13=""),"",IF(J13="NO","",IF(O13="",TODAY()-I13,O13-I13)))
How can I make this formula work, IF Cell "O13 is Blank and the Data is available with Cell S13 or W13.
I have a column with two forced choices (i.e., Group or individual). There are ten rows in the column. I am trying to create a formula that returns a "Yes" or "No" if the text "individual" is entered in any one of the ten rows in the column. The formula for one row in the column is =IF(F3="Individual", "Yes", "No"
How do I get the formula to recognize all ten cells (i.e., F3:F12) to determine if the value is present in just one of the ten cells?