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 7. Total comments: 567
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.
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
Guy's please help to fixed the formula in below condition. I have applied formula =(IF(O2<10,"<10",IF(O2<20,"10-20 Min",IF(O2<30,"20-30 Min",IF(O2<60,"30-60 Min",IF(O2=120,">120 Min"," "))))))) but result is not proper.
Time Condition
2:11:59 120 Mins
Hello,
If I understand your task correctly, please try the following formula:
=IF((VALUE(O2)*24*60)<10,"<10",IF((VALUE(O2)*24*60)<20,"10-20 Min",IF((VALUE(O2)*24*60)<30,"20-30 Min",IF((VALUE(O2)*24*60)<60,"30-60 Min",IF((VALUE(O2)*24*60)<120,"60-120 Min",IF((VALUE(O2)*24*60)>=120,">120 Min"," "))))))
Hope it will help you.
Guy's please help to fixed the formula in below condition. I have applied formula =(IF(O2<10,"<10",IF(O2<20,"10-20 Min",IF(O2<30,"20-30 Min",IF(O2<60,"30-60 Min",IF(O2=120,">120 Min"," "))))))) but result is not proper.
Time Condition
2:11:59 120 Mins
Hi,
i'm working on a daily tracker which is updated on a daily basis value "0" or more than 0 . like 1 or 2. in left to right order.
i want something which should indicate me if last 3 days value is "0".
please help me
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.
Hi, i need a formula to calculate a range table of age like 15-34, 35-44, 45-49 and they have to match with 2 different class, with 2 different amounts link to each class, if the amount in column A is <=34 and column B is "2" the amount should be the amount in cell G2 or if Column B is "1" it should be the amount in cell G3. hope you can assist.
Hello,
If I understand your task correctly, you need 2 different formulas for cells G2 and G3:
For cell G2:
=IF(B1=2,IF(AND(A1>=15,A1<=34),"15-34",IF(AND(A1>34,A1<=44),"35-44",IF(AND(A1>44,A1<=49),"45-49",""))),"")
For cell G3:
=IF(B1=1,IF(AND(A1>=15,A1<=34),"15-34",IF(AND(A1>34,A1<=44),"35-44",IF(AND(A1>44,A1<=49),"45-49",""))),"")
Hope this will help you!
Hi, can you help with how do i use 'one of' constraint: like if (A1 is one of ARRAY;Y;N). Haven't found anything that would match and having like 600 lines in the array would be complicated to set OR(A1=value1;A1=value2.....)
Thank you!
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.
I have three colms as
A1 (City), B1 (Quantity), C1 (Amount)
If Colm A1 is Karachi, I want then quantity (B1) should multiply with 15 in Amount colm (C1)
If Colm A1 is Lahore, I want then quantity (B1) should multiply with 15 in Amount colm (C1)
Hello, Azam,
Please try the following formula:
=IF(A1="Karachi", B1*15,IF(A1="Lahore", B1*15,""))
Hope it will help you.
I need a formula to calculate a variable sales commision. if I sell 8 or less, I get $x. if I sell 9-15 units, ill get $y. with a variability of 5 different pay rates for commission.
Thanks
Hello, Patrick,
Please try the following formula:
=IFS(A1<=8,"$x",A1<=15,"$y",A1<=20,"$z",A1<=25,"$v",A1<=30,"$w")
Hope it will help you.
Hi,
I need a formula to calculate a variable sales commision. if I sell 8 or less, I get $x. if I sell 9-15 units, ill get $y. with a variability of 5 different pay rates for commission.
Thanks
hi...can i ask for help..how can i solve this logic delta column:
if stock > max >>> stock - max (the value must be highlighted in red)
if min <=stock >> stock quantity (the value must be highlighted in green)
if stock>> stock-min (the value must be highlighted in yellow)
Hello, jelmer,
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.
some plz help me to use this
400,001 to 500,000 Slab 02 (AZ163-400000)*2%
500,001 to 750,000 Slab 03 2000+(AZ164-500000)*5%
750,001 to 1,400,000 Slab 04 14500+((AZ165-750000)*10%)
1,400,001 to 1,500,000 Slab 05 79500+((AZ166-1400000)*12.5%)
1,500,001 to 1,800,000 Slab 06 92000+((AZ167-1500000)*15%)
1,800,001 to 2,500,000 Slab 07 137000+((AZ168-1800000)*17.5%)
2,500,001 to 3,000,000 Slab 08 259500+((AZ169-2500000)*20%)
3,000,001 to 3,500,000 Slab 09 359500+((AZ170-3000000)*22.5%)
3,500,001 to 4,000,000 Slab 10 472000+((AZ171-3500000)*25%)
4,000,001 to 7,000,000 Slab 11 597000+((AZ172-4000000)*27.5%)
Exceeds from 7,000,000 Slab 12 1422000+((AZ173-7000000)*30%)
IF(AND(F47<=22500000,F4750000000,F47<=100000000),0,E43*0.1))
I AM USING THIS FORMULA BUT THE RESULT IS NOT GIVEN WHERE IS WRONG
PLEASE GIVE ME SALUTATION
IF(AND(F47<=22500000,F4750000000,F47<=100000000),0,E43*0.1))
I AM USING THIS FORMULA BUT THE RESULT IS NOT GIVEN WHERE IS WRONG
PLEASE GIVE ME SALUTATION
hell can you help me. i sen message to facebook and come friend.
I have data like this
123415678
432128765
001218090
These numbers represents ID numbers If the 5th number is 1 then the ID belongs to a male if its 2 then the ID belongs to a female
Hello ,
Go the next column, and use this formula (=Left(A1,5))
in Column C , put this formula ( =Right(C1,1)
Codes Left Coding Right Coding Sex
123415678 """=LEFT(A2,5) """=RIGHT(B2,1) ''''=IF(C2="2","Female","Male")
432128765 43212 2 Female
301218090 30121 1 Male
214510025 21451 1 Male
301228090 30122 2 Female
301228090 30122 2 Female
301218090 30121 1 Male
Hello ,
Go the next column, and use this formula (=Left(A1,5))
in Column C , put this formula ( =Right(C1,1)
Codes Left Coding Right Coding
123415678 """=LEFT(A2,5) """=RIGHT(B2,1)
432128765 43212 2
301218090 30121 1
214510025 21451 1
301228090 30122 2
301228090 30122 2
301218090 30121 1
Wanted to extend my thanks. This is exactly what I was looking for!
Using excel formulae, find out the following
1. How many have neither registered nor completed any of the 3 courses?
2. How many have registered or trained in atleast 2 of the 3 courses?
3. How many have not been trained in any of the 3 yet?
R - Registered for training (training not done yet)
T - Trained
Blank - Neither
Name SQL SAS Excel
Prakash R T
Rahul R
Rajiv
Priya R T
Amit T
=IF(NOT(isblank (M6)), "unaccomplished", if (isblank N6))' unaccomplsihed
can somebody help me?
I have an excel sheet named "ALL".Just trying to apply formula for counting open points related to Business Development.I applied below formula but its not working can you please check & confirm
=COUNT(AND(All!K4:K1000="Business Development",All!N4:N1000="Open"))
Hello, I need help. I need to verify whether my column C's (numeric) information and column B's (description) information are appropriate and accurate for my column A's (code). Thanks in advance.