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 6. Total comments: 567
I need formular for 2 criterias,
Increase base salary offer (X)
if GPA is
>3.5 by 2000
>3.0 by 1000
>2.5 by 500
and additional bonus
if experiential activity
>4.5 $3,500
>4.0 $3,000
>3.0 $2,500
SL P A = Error
- P - = P
SL P - = SL
- P A = A
hi,
What formula could i use if i don't want a number to go over 7.5...If a number is lower, is displays the actual number but if it's higher it shows only 7.5
THank you,
HI..
=IF(H19<7.5,H19,7.5)
How to write a formula for this
10 - 64 Reading below grade level
65- 81 On Level
82 - 100 Reading above grade level
Thank you so much
Hello, Arcita,
Please try the following formula:
=IF(A1<=64, "Reading below grade level", IF(A1<=81, "On Level", IF(A1<=100, "Reading above grade level")))
You can learn more about Excel Nested IF in Excel in this article on our blog.
Hope you'll find this information helpful.
Hi all
How can i insert any function in the criteria of countif
For example i have a marks sheet i want to count that how many student get 80% or more then 80% marks in examination
One way is i have to calculate the 80% and write in the criteria
What is another way??
I have A2=28, B2=45 to get a smaller value i applied the formula =IF(A2>B2,A2-B2,A2). So i got an answer has 28.
My question is if i change a value in A2=50 now i should get a value has B2 in C2 cell.
So i have entered has =IF(A2>B2,A2-B2,A2)*OR(IF(A2<B2,B2,B2))
Still it is not working, can someone assist me how to put a formula with a proper condition?
You have eliminated my sleepless night for weeks now.
Thanks
COMPLETE SUCCESS
I used the following formulas to test and see if the date format translates to a number, which it did
=LEFT(MONTH(D13),2)
Then expanded it to check for output for two months - it worked
=IF(LEFT(MONTH(D11),2)="5","May",IF(LEFT(MONTH(D11),2)="6","Jun",""))
FORMULA WHICH WORKED (expanded it to 12 months, put Jan as 01)
------------------------------
=IF(LEFT(MONTH(D10),2)="01","Jan",IF(LEFT(MONTH(D10),2)="2","Feb",IF(LEFT(MONTH(D10),2)="3","Mar",IF(LEFT(MONTH(D10),2)="4","Apr",IF(LEFT(MONTH(D10),2)="5","May",IF(LEFT(MONTH(D10),2)="6","Jun",IF(LEFT(MONTH(D10),2)="7","Jul",IF(LEFT(MONTH(D10),2)="8","Aug",IF(LEFT(MONTH(D10),2)="9","Sep",IF(LEFT(MONTH(D10),2)="10","Oct",IF(LEFT(MONTH(D10),2)="11","Nov",IF(LEFT(MONTH(D10),2)="12","Dec",""))))))))))))
Used the LEFT(MONTH(CELL_REF),2) to verify the translation of the date to number, THEN expanded it to the following, it worked.
=IF(LEFT(MONTH(D10),2)="01","Jan",IF(LEFT(MONTH(D10),2)="2","Feb",IF(LEFT(MONTH(D10),2)="3","Mar",IF(LEFT(MONTH(D10),2)="4","Apr",IF(LEFT(MONTH(D10),2)="5","May",IF(LEFT(MONTH(D10),2)="6","Jun",IF(LEFT(MONTH(D10),2)="7","Jul",IF(LEFT(MONTH(D10),2)="8","Aug",IF(LEFT(MONTH(D10),2)="9","Sep",IF(LEFT(MONTH(D10),2)="10","Oct",IF(LEFT(MONTH(D10),2)="11","Nov",IF(LEFT(MONTH(D10),2)="12","Dec",""))))))))))))
Sunny:
Alright! That's a lot of nested IF's but you figured it out. Congratulations on a good use of the MONTH function.
Thank you for sharing. I'm sure others will benefit from your work.
SUCCESS:
I was able to achieve and find the conversion of the date format to a number with "=LEFT(MONTH(D13),2)"
THEN, I expanded the formula to:
=IF(LEFT(MONTH(D12),2)="1","Jan", IF(LEFT(MONTH(D12),2)="2","Feb", IF(LEFT(MONTH(D12),2)="3","Mar",IF(LEFT(MONTH(D12),2)="4","Apr",IF(LEFT(MONTH(D12),2)="5","May",IF(LEFT(MONTH(D12),2)="6","Jun",IF(LEFT(MONTH(D12),2)="7","Jul",IF(LEFT(MONTH(D12),2)="8","Aug",IF(LEFT(MONTH(D12),2)="9","Sep",IF(LEFT(MONTH(D12),2)="10","Oct",IF(LEFT(MONTH(D12),2)="11","Nov",IF(LEFT(MONTH(D12),2)="12","Dec",""))))))))))))
BUT
The blank cells are coming up with "Jan" where it should be a blank "" as in the formula above.
Any suggestions or input?
I am trying to find a formula where if a LEFT 2 is 01, 02, 03 etc (for date 01/01/18 style), and it enters:
- Jan if it is 01 of a 01/01/18 date format
- Feb if it is 02 of a 02/01/18 format
I have tried the following, but it did not work:
=IF(LEFT(D21,2)="01","Jan",IF(LEFT(D21,2)="02","Feb",""))
Kindly assist.
Sunny
Hi,
Please Help.
For testing some lower percentages requires logic test result in the list.
READING NOW PREVIOUS RESULT ACTION
-10% -8%
-25% -12% REJECT
10%
25% POSITIVE
FOR - VE UP TO 10% IF PREVIOUS TEST IS POSITIVE (ABOVE 0%) RE-TEST
FOR - VE UP TO 10% IF PREVIOUS TEST IS NEGATIVE (ABOVE 0%) DOSE CHANGE
FOR + VE UP TO 10% IF PREVIOUS TEST IS NEGATIVE (BELOW 0%) FOR CURING
FOR + VE UP TO 10% IF PREVIOUS TEST IS POSITIVE(BELOW 0%) NEXT TEST
READING NOW PREVIOUS RESULT ACTON
-8.0% 5.0% RE-TEST
-12.0% -8.0% DOSE CHANGE
8.0% -12.0% FOR CURING
12.0% 8.0% NEXT TEST
22.0% 10.0% IF(A1825%,"POSITIVE,IF(AND(A18=0%,"RE-TEST"),IF(AND(A18>=0%,B18<=0%,"FOR CURING")))
17.5% 4.0%
2.0% -8.0%
-6.0% -12.0%
8.0% -1.0%
-5.0% 8.0%
i want to have logical comparision or any suitable formula for folowing condition pls guide.
if column one value is pipe ,column 2 value is carbon steel,column 3 value is
2Inch,column 4 value is -40 schedule,column 5 value(heat no) will be "abcd"
also in same formula column 4 may varry like it may40,80,160 many numbers then my column 5 will be identified value to add in column 5.i.efor 40 sch colm5 value is abcd if column 4 value is 80 then column5 value is efgh,if column 4 value is 160 then column5 value is ijkl.
I need help with an "IF" "AND" formula.
I am looking for the logic when I have a test score in box B1 and want to create a rule:
IF B1 > 0.5, but B1 0.6 and smaller than 0.7 make C1 = Level 2
AND IF B1 > 0.7 make C1 = Level 3
IF FALSE = Unfit
This might be one of the best excel tutorials I've ever read! Very well written, and the examples really help show how the functions work.
Thank you!
Hello, I need some help with formula related to day of the week. If day = Tuesday or Thursday, a formula needs to be incremented by 1. I can get it to return True if the day is Tuesday or Thursday and have that in a column.
I tried the formula below but it increments by 1 no matter what h3 is.
=IF(h3="FALSE",(1+$k$3-j3),($k$3-j3))
I also tried this but it also increments by 1 no matter what h3 is.
=IF(h3="FALSE",($k$3-j3),(1+$k$3-j3))
If have to add the print the the value in C1.
I need add the student marks for the following condition A means if student Absent in subject.A1=Theory Marks,B1=Practical Marks,C1=Final marks Means A1+B1=C1
case-1
A1=5 B1=10 C1=15
Case-2
A1=5 B1="A" C1=05
Case-2
A1="A" B1=10 C1=10
Case-2
A1="A" B1="A" C1=0
any body please help to write formula for that.
Kindly give me formula for that
Anurag:
If you can simply put a "0" in A or B when student is absent then the formula is very simple. In C1 just enter =A1+B1.
If you must enter an "A" in a cell when student is absent then put this formula in C1: =IFERROR(IF(A1="A",(0+B1),IF(B1="A",(0+A1),(A1+B1))),"Student Was Absent for Both Subjects")
If A1 and B1 both hold "A" the IFERROR returns the message, "Student Was Absent for Both Subjects".
You can change the cell addresses and the message to suit your needs.
Hi :). I have two possible outcomes for my formula - either cell 'J' (if cell E is greater or equal to cell G and less than or equal to cell H) or cell 'K' (if cell E is less than cell G or greater than cell H). here is my formula: =IF(E3>=G3*(AND(E3<=H3)),J3),IF(OR(E3H3))),K3) which returns #VALUE
Please help :(.
Carl:
Text and numbers don't mix very well in Excel formulas, so make sure your numbers are actually formatted as numbers.
I'm looking for the correct and/or statement. When it was just AND statement, it worked. Now that I need to incorporate OR, it's not working.
Current: =IF(AND(A46="Pending", TODAY()>E46), TODAY()-E46,"Awarded")
Need to turn it into something that will give me the following responses.
If A46="Closed", "Closed"
Pretty much A46 will either be Pending, Awarded or Closed.
Pending = Today -A46 (to give me outstanding number of days)
Awarded = Awarded
Closed = Closed
HELP!
Hello. how do I write for formula for:
if K70>=33 A, if less than 33 but more than equal 28 B, if less than 28 C
?
thank you
macaduta,
what i did understand was
if there is a value >= to 33 is "A"
then if there is a value between 28-32 is "B"
and if there is a value 27),AND(K7032,"A",K70<28,"C")
IFS(AND(AND(K70>27),AND(K7032,"A",K70<28,"C")
Macaduta:
The logic in your statement needs to be clearer.
It should read like "If K70 is greater than or equal to 30 then A, etc.
What is "A", "B" and or "C". Values held in other cells? Partial cell addresses?
The second condition needs to be clarified. Is this an AND or an OR relation to the first statement?
After Excel checks these conditions what is it supposed to do? What if none of the conditions are true?
Hi all
I need a forumla/macro for the following argument.
The results should be returned in cell C1 by way of a changed cell colour only.
There are a bunch of arguments applied to each cell based on the values given in cells A1 and B1.
If any of the conditions are met in the below table then the resulting colour should be shown in cell C1
Cell A1 AND Cell B1 then
VL VL Green
VL L Green
VL M Green
VL H Amber
VL VH Amber
L VL Green
L L Green
L M Amber
L H Amber
L VH Amber
M VL Amber
M L Amber
M M Amber
M H Red
M VH Red
H VL Amber
H L Red
H M Red
H H Red
H VH Black
VH VL Red
VH L Red
VH M Red
VH H Black
VH VH Black
I'm assuming I'm going to need a macro for this?
Many thanks
Jo:
Before you look over my solution, let me say to you and anyone else seeing it that I know this formula contains about 20 too many IF/AND statements. It should be in a VLOOKUP, INDEX/MATCH or ideally in VBA code. However, the VLOOKUP and INDEX/MATCH solutions are more difficult for me to explain and the VBA is outside the scope of this blog. So, with that said I will caution you that something like this could be very difficult for anyone coming after you to follow and if you need to modify it, will be a pain. Anyway, these were the conditions you outlined so here it is.
=IF(AND(A1="VL",B1="H"),"Amber",IF(AND(A1="VL",B1="VH"),"Amber",IF(AND(A1="M",B1="M"),"Amber",
IF(AND(A1="M",B1="VL"),"Amber",IF(AND(A1="M",B1="L"),"Amber",IF(AND(A1="L",B1="VH"),"Amber",IF(AND(A1="L",B1="H"),"Amber",
IF(AND(A1="L",B1="M"),"Amber",IF(AND(A1="H",B1="VL"),"Amber",IF(AND(A1="H",B1="VH"),"Black",IF(AND(A1="VH",B1="H"),"Black",
IF(AND(A1="VH",B1="VH"),"Black",IF(AND(A1="VL",B1="VL"),"Green",IF(AND(A1="L",B1="VL"),"Green",IF(AND(A1="L",B1="L"),"Green",
IF(AND(A1="VL",B1="L"),"Green",IF(AND(A1="VL",B1="M"),"Green",IF(AND(A1="H",B1="L"),"Red",IF(AND(A1="H",B1="L"),"Red",
IF(AND(A1="H",B1="M"),"Red",IF(AND(A1="H",B1="H"),"Red",IF(AND(A1="M",B1="H"),"Red",IF(AND(A1="M",B1="VH"),"Red",IF(AND(A1="VH",B1="VL"),"Red",
IF(AND(A1="VH",B1="L"),"Red",IF(AND(A1="VH",B1="M"),"Red"))))))))))))))))))))))))))
This will put the color's words in the C cells because I needed something easy to follow during the testing. Also, you can now conditionally format the column using a IF cell text is "Amber" or whatever approach to format the cell's color. Otherwise, the Conditional Formatting formula would be a mess.
As I say, I know there are other ways to accomplish this, but at the end of the day, I think this is the easiest, in this forum.
I sell a product that has different names ( up to 700) is there a formula that I can use that can count how many times I have sold a product with one name by comparing the sales data against the list of names ?
Maria:
Enter SUMPRODUCT in the search field here on AbleBits. I think you'll find the answer to your question.
Is there some form of if statement that has more than 2 logical parts.
For instance, instead of outputting TRUE or FAlSE, or the equivalent. IT could output any number of different responses.
To copy your demonstration system;
IF([logical1], [logical2], … [logical{n}], …, [Response1], [Response2], Response{n})
Sam:
There are several techniques that work the way you're asking about. Their use depends on the required logic of the situation.
Nested IF statements are one. They look like this:
=IF(Q2,"Paid",IF(S2>=TODAY(),"Not due","Overdue"))
IF AND statements look like this:
=IF((AND(D1="eng", B11="bank1")), IBAN1
IF OR statements look like this:
=IF( OR( A120), "Less than 10 or more than 20")
Then there are COUNTIF, SUMIF, IFS and others most of which are explained here in the various ABLEBITS articles. Just enter one of the above titles in the Search box and begin to learn about IF statements.
IMT 23 clause
If IMT 23 'Yes', 50 % depreciation charges on assessed amount, if No 0 % depreciation excel formula & function
PLEASE CONVERT THE FOLLOWING FORMULA
IF "A1">TODAY DATE AND EQUAL TO DATE (2018,10,1) THEN "YES" AND IF "A1">TODAY DATE AND > DATE(2018,10,1) THEN "NO" AND IF "A1" < TODAY DATE THEN "EXPIRE".
please help me for below I need formula for below things
1 TO 15000 500
15001 TO 100000 1000
100001 TO 250000 2500
250001 TO 1000000 5000
10000001 AND MORE -0.60%
Ramanan:
I think this is what you want.
Where the value you want to check is in cell H16
=IF(H16>1000000,(H16*-0.06),IF(H16>250000,5000,IF(H16>100000,2500,IF(H16>15000,1000,IF(H16>1,500)))))
1 TO 15000 500
15001 TO 100000 1000
100001 TO 250000 2500
250001 TO 1000000 5000
10000001 AND MORE -0.60%
Hi All,
I need a formula to do the following;
if it starts with a Letter remove all the dash, add space after the last letter,and add 1st 2 digits from the column next to it
QP_-00091948-00-0 01.3
The data is in 2 columns A and B.
thank you
Hello, can I ask for help?
I have to count how many times each person went to some places in a certain range of date from different sheet.
For example
(Sheet 1)
PLACES DATE PERSON
Sydney 2018/4/3 A
Perth 2018/3/7 A
(Sheet 2)
DATE RANGE TIMES Of VISIT
2018/3/10~2018/4/5 ???
I have tried use 'COUNTIFS' but I got '0' for the result.
Can you tell me problem that might happen in this case?
Thank you!
hi
i need a formula for. .
if year 2015 then get interest 8%
&
if year 2016 then get interest 7.9% if year 2017 then get interest 7.8%
Your question is unclear, but I'm thinking you should somehow separate the data by year and then apply the interest. Otherwise, how will Excel know what year it is?
HOW TO MERGE BOTH FORMULA IN A CELL
=IF(E53=12,"BENF-CPI",IF(E53=1,IF(OR(F53="DN12",F53="DB12"),"CPI-NSDL POA",IF(OR(F53="DN22",F53="DB21"),"CPI-CDSL POA"))))
=IF(E53=11,IF(AND(J53="02-12047200-00100398",AG53="02-12047200-00100383"),"IST",IF(J53=AG53,"IST","P2P")))
Create a whats app group for Excel where we can ask any Questions and reply to all that will be great.
Good job you are doing.
I got it
Hi,
I am new to excel. I need a formula for following situation:-
A5=886.7, B5=1.5%
A6=900, B6=1.5% , C1=0.0%, C2=0.1%, C3=0.2% , C4=0.3%
Now Condition when,
0<A6<=999, B6=B6+C1
1000<=A6<=1499 , B6= B6+C2
1500<=A6<=1999 , B6= B6+C3
20002000
Thanks for the help!!
I would like to insert a value in E4 if d4 is = to a certain value,but there are 7 possible values:
if d4 = 8.0 then 5
d4 = 8.1 then 7
d4 = 8.2 then 9
d4 = 8.3 then 11
d4 = 8.4 then 13
d4 = 8.5 then 15
Thanks!
In Cell A1 I simply have the number 1.
What I need is the following:
In cell B1 I need an if statement that returns the letter D if A1 is less than 5, C if less than 10, B if less than 15 and A if less than 20.
Thanks
How to make a formula for time and minute for example i have to make a table for my employer when they came to work and when they finish work.cell A 7:30 and cell B 15:30 how much time they work.
=IF(AND(D63>=4000,D63<=7000),"1400","2200")
I Have Used This Formula In excel Sheet, But In This If The Valuve Is Less Then 4000, So Then Then the Folmula Value Show Is "0"
Can U Guide What The Formula.
Hello, Farooq,
If I understand your task correctly, you should use the nested IF functions and your formula should look as follows:
=IF(D63>7000,2200,IF(D63>=4000,1400,0))
Hope this is what you need.
I need to nest the following formula so that it goes horizontally across my spreadsheet. I either get FALSE or #VALUE. The numerical value in row 4 is a score dependent on the result content of row 6. Just using the formula below I get the answer 5 which is correct but I need to nest 19 columns worth!
=IF(XOR(G6="Yes",G6="NA"),G4,"")
I have already create this sheet ,but I'm creating my own sheet specific total amount.
Exp: motor,Marine,hull(col1)
Exp: Total Taka(col1)
="motor taka"
(how to solve this problem)
How do I use an AND function To return a value other than True or False,For example: If cell A2 is greater than 20 and B2 is greater than 10 it should return Pass not true. How can I do that?
Hello, Omar,
Please try the following formula:
=IF(AND(A2>20,B2>10),"Pass","")
Hope this will help you.
PLS I NEED YOUR HELP, BEEN THINKING ABOUT THIS IN THE PAST 2 MONTHS BUT UNTIL NOW I DONT HAVE ANY SOLUTION TO THINK OF.
RIGHT NOW IM USING 2 WORKBOOKS TO WORK ON THIS. BUT I WANT TO USE ONLY ONW WORKBOOK . THIS WORKBOOK HAS TO BE USE EVERY YEAR WITHOUT CHANGING THE EXCHANGE RATE OF 2017 TO 2018. IF YOU WANTED TO KNOW MORE ABOUT MY WORKBOOK I CAN EMAIL YOU THE TEMPLATE.
Looking for a formula as follows:
If Cell A1 contains DELETE then return nothing, else return Cell contents of A1 to C1.
And another formula: If Cell C1 contains nothing, do nothing. Else return contents of Cell B1 to D1.
Basically, if this example uses a period to indicate a new cell it should look like this.
Sample 1: Apple.27.Apple.27
Sample 2: DELETE.31. .
Key: (value1.value2.formula1.formula2)
Hi
i am trying to use these logical function OR, AND, NOT in farmula with offset function i dont know how to write this farmula,
i want make a sheet for Patient Origin and want to calculate that patient origin by week number to select week number 1 from the week list and all patient origin of week 1 apear, if you want to look my database/spread sheet let me know i can send that to you..,
Amjad khan
Need a formulas to create an A,B & C analysis
A >8000 Irs, B <8000 Irs and C as <1000 Irs.
Thanks
I want a cell to check another cell for the list below and add the appropriate number if it is found. Here is list of items:
TS=2.50
SL=2.50
W SERV=3.00
G SERV=2.50
S SERV=5.00
Please help and thank you
I figured it out:
=IF(O2="TS","2.5", IF(O2="SL","2.5",IF(O2="W SERV","3","?")))
Thanks
please i need formula for adding and subtracting of date, eg: add 35 days to 23/2/2014 that will give me 30/3/2014
Please need a formula for the following:
If the following cell (g2) contains any of these: early 5, mid 5, late 5 and the other cell (h2) has a value >=38 then I need cell (I2) to say "YES" but if cell (h2) has a value<=12 then I need cell (I2) to say "NO" otherwise cell (I2) needs to say "NA"
I want formula for date.
I have to find out the month of increment from joining date of an employee on excel sheet.
Such as if date of joining is up to 15th the date of increment will be same month and if date of joining is after 15th then date of increment will be next month.
Example, Date of joining of any employee is 12/03/2017 then increment month will be March, whereas date of joining is 16/03/2017 then increment month will be April.
Hello,
If I understand your task correctly, please try the following formula:
=TEXT(IF(DAY(A1)>15,DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)),A1),"mmmm")
Hope this will help.
I want formula for date. I have to find month of increment from his joining date.
Such as if date of joining is up to 15th the date of increment will be same month and if date of joining is after 15 then date of increment will be next month.
Example, Date of joining of any employee is 12/03/2017 then increment month will be March, whereas date of joining is 16/03/2017 then increment month will be April.
Hi Frnds
I need help to solve the below condition
=IF(OR((M10+N10+O10)=0),"",(M10+N10)/(M10+N10+O10)*100),IF(OR((M10+N10+O10)=""),"",(M10+N10)/(M10+N10+O10)*100))
If anyone help me to solve this plz
Need to implement urgently
Hello,
For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
Please also don't forget to include the link to this comment into your email.
I'll look into your task and try to help.