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 9. Total comments: 567
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.
Hi,
please solve this question.
If Mr.A has 125 coins then he will get "1 Apple" and if Mr.A has 170 coins then he will get "1 Apple" for 125 coins and on balance 45 coins, he will get "1 Lemon" on every 25 coins.
Please convert the same in to logical formula in excel.
LET CELL A1 have the coins MR. A HAS, THEN copy THE following EXPRESSION
=CONCATENATE("IF MR.A HAS 125 COINS, THE HE WILL GET 1 APPLE, AND IF MR. A HAS"," ",+A1," ","COINS"," ","THEN HE WILL GET"," ",ROUNDDOWN(+A1/125,0)," ","APPLE(S)"," ","AND ON BALANCE"," ",A1-(ROUNDDOWN(+A1/125,0)*125)," ","COINS, HE WILL GET"," ",ROUNDDOWN(+(A1-(+ROUNDDOWN(+A1/125,0)*125))/45,0)," ","LEMON ON EVERY 25 COINS")
=IF(G14="Mr.A",(IFS(H14=125,"1 Apple",H14=170,"1 Apple, 1 Lemon"))," ")
Hope this will help you
=IF(AND(V17="B",V18<="470"),"127","0"),IF(AND(V17="BC",V18<="700"),"112","127"),IF(AND(V17="C",V18<="420"),"0","112")
I AM USING THIS FORMULA BUT THE RESULT IS " #VALUE! "
PLEASE GIVE ME SALUTATION.
Muhammad, Possible Solution
1) To make this easier, separate the formulas and evaluate each one separately. I noticed that there is overlapping logic. If formula #1 is False you get a -0- and if formula #3 is True you get a -0-, and the same thing with 127 and 112 across all 3 formulas. How would you know which values gave you the answer? Also, what if the input values do not match your expected answers, do you evaluate them? And your attempt to nest the IF statements is wrong.
Nesting as below will work but you will still only get 112, 127 or -0-.
=IF(AND(B1="B",B2<="470"),"127",IF(AND(B1="BC",B2<="700"),"112","0"))
Hope this gives you some insight.
Hi
I have 9 sheets in the same workbook variously populated with 0 and 1. In a separate sheet I want to fill a cell with "1" if 1 occurs in the same cell in any of the 9 sheets, or "0" if it doesn't occur at all. I have tried using the following:
=IF(OR('Training NetworkB'!C5="1",KnowledgeSharingBtxt!C5="1",'Encouragement NetworkB'!C5="1",'Organisation NetworkB'!C5="1",'Monitoring NetworkB'!C5="1",'Networking NetB'!C5="1",'Labour sharing NetworkB'!C5="1",'Conflict NetworkB'!C5="1",'Phys_Finan Capital NetB'!C5="1"),"1","0")
It returns a 0 in all cells, even if a 1 occurs. Can you help at all?
Many thanks in advance
Hi,
It looks to me like you have a space after all your 1's that are in your speech marks. If the cells on the other spreadsheets just contain a "1" and not a "1 " then the formula won't recognise the 1 because it will be looking for a 1 and a space.
Hi,
It looks to me like you have a space after all your 1's that are in your speech marks. If the cells on the other spreadsheets just contain a "1" and not a "1 " then the formula won't recognise the 1 because it will be looking for a 1 and a space.
Hi,
I need to make a formula to count 3 different columns but counting just one time if they have more than one value for the others 2 columns.
How I can do that??
I try different ways but I don't know do it!
Thanks!!
hi
i need a formula for. .
if found 0152 then get 18
&
if found 6305 then get 5
Hi
Thank you for contacting us.
Please try the following formula:
=IF(A1 = 152, 18, IF(A1=6305, 5, " "))
I hope this helps. Please let me know if you have any other questions or difficulties.
Hi Mary ,
can you help me in macro how to record macro ?
Hi Amit,
First you have to save as your Excel sheet to .xlsm. after this step you have to go view ribbon and choose Macro option. There you can find macro recording.
Start recording and work in the same sheet without using mouse, it works perfectly. Because if you use mouse Macro may not write program as per your clicks.
Once you stop recording you just give an non-calculated report which you did earlier and run macro from same macro option.
Regards,
Mohan
Hi Svetlana
I need a formula for comparison of numbers with text
E.g.
If x is less than or equal to 1600 them yes or if x is not available then "not avl"
But if X is more than 1600 then wow but if x is not avl then not avl
=IF(OR(B6="",B6<=1),"Not Available",IF(B6<=1600,"Yes","Wow"))
=IF(C18>1600,"Wow",IF(C18>0,"yes",Not Available))
=IF(C18>1600,"Wow",IF(C18>0,"yes","Not Available"))
Hi Please use this.
=IF(OR(C24="",),"Not Available",IF(C24>=1600,"Yes","Wow"))
'=IF(+C6=0,"not avl",IF(+C61600,"wow")))
Hi 201, please use below Formula
=IFERROR(IF(VLOOKUP(D1,A1:B13,2,0)>1600,"Wow",IF(VLOOKUP(D1,$A$1:$B$13,2,0)<=1600,"Yes","0")),"Not Avl")
Hello all,
Please help..
Distance 50 kg 100 kg 250 kg 500 kg 1000 kg
Upto 25 NIL NIL NIL NIL NIL
26-100 km 650 750 1000 1150 1500
101-150 km 1000 1250 1500 1750 2000
151-200 km 1250 1500 1750 2000 2250
201-300 km 1500 1750 2000 2500 3000
My query is
If distance 100 km & wg 50 so charge 650/-. if distance 100 km & wg 100 kg charging cost 750/-. if distance 100 km & wg 250 kg charging cost 1000/-. same applicable for 500 kg & 1000 kg.
same for 150 to 300 km & kg 50 to 1000 kg.
my query is condition one is same but condition 2 change so which formula I can use ?
hi Mahesh,
write down your distance on a row on top and quantity in left side colomn such as
100 150 200 250 300 (start this from "B" cell)
50
100
150
200
250
Then apply this in cells and change the value of only 100 in formula with your distance value.
=IF(OR(B18=100,A19=50),650,IF(OR(B18=100,A19=100),750,IF(OR(B18=100,A19=150),850,IF(OR(B18=100,A19=200),900,1000))))
Hi, In my spreadsheet I have the following information:
A2="Gold", B2="Silver" C2="Bronze, D2=a blank field. I'm trying to create a single formula in column E2 which captures the following:
If range A2 to D2 contains "Gold" then E2="Gold";
If range A2 to D2 does not contain "Gold" but it contains "Silver" then E2="Silver";
If range A2 to D2 does not contain "Gold" or "Silver" but contains "Bronze" then E2="Bronze";
If range A2 to D2 fields are all blank fields then E3="unrated"
Hope you can please assist. Thank you.
=IF(OR(A2="Gold",B2="Gold",C2="Gold",D2="Gold"),"Gold",IF(OR(A2"Gold",B2"Gold",C2"Gold",D2"Gold")*OR(A2="Silver",B2="Silver",C2="Silver",D2="Silver"),"Silver",IF(OR(A2"Gold",B2"Gold",C2"Gold",D2"Gold")*OR(A2"Silver",B2"Silver",C2"Silver",D2"Silver")*OR(A2="Bronze",B2="Bronze",C2="Bronze",D2="Bronze"),"Bronze","Unrated")))
May be it will help you
hi
i have one err
exp
sheet1 cell A2 in a name SOM
sheet2 cell b2 in formula given =sheet1!A2
but he shown name a repeat in cell
exp - SOM-SOM-SOM
PLS HELP ME
Hi Svetlana Cheusheva!
Please help me with complete list of excel logical function. it's urgent.
Hi there, I am not sure how to phrase my question, so please be patient with me!
I have a spreadsheet, which identifies results (based on selections) and returns scores
i.e. E3 is the result column
C3 and D3 are Player 1 and 2 respectfully.
Now, Player 1 and player 2 choose a number between 1-6. the result (from the game) goes in column E and the formula in F returns "Player 1" or "Player 2" if their answer matches the result...I hope you follow! this all works great - until, Player 1 and player 2 both choose 3!!!
This gets even more complicated if 3 is the winning result!
I need a win, lose, draw, no result type scenario but NOTHING I have tried will resolve the issue :( PLEASE HELP!
Hi,
I have 2 formulas that both work however I need to have them work together as an "OR" situation and can't come up with the right formula for that. Can you help?
=IF(AND(D45=1,Q45="N"),(I45*0.0925))
=IF(AND(D45=1,Q45="Y"),P45)
HI Dave
do just like this
=IF(AND(D45=1,Q45="N"),(I45*0.0925),IF(AND(D45=1,Q45="Y"),P45),0)
hi,
I want to solve below conditions.
9:00am to 10:00am ="A"
10:00am to 11:00am = "B"
11:00am to 12:00pm = "C"
Hello All, please help to make logical formula in excel
This One:-
=IF(A1<4,"short",IF(A1=6,"Long")))
Hi Roshan,
Try This:-
=IF(A1<4,"short",IF(A1=6,"Long")))
I want to solve simple 3 equation.
Ex:- 6inch> tall, 4 to 6 medium, 4inch<Short.
Hello, Roshan,
try this formula:
=IF(A1>6, "Tall", IF(AND(A1>=4,A1<=6), "Medium", IF(A1<4, "Short", "")))
Can you get an IF AND OR BUT IF formula
Example: =IF((AND(OR(D2="Peach",D2="Orange",D2="Apple"),OR(H2="Standard",H2="Close-Cut")),1,-1) I require a but if Cell D3=1 then the value id 1
Hello,
I'm sorry but your task is not entirely clear.
What do you mean by "value id 1"? Where do you want to see this id number? Do you want to include D3 in this condition as well? Please specify so we could advise you with your formula.
=IF(AND(AB67>100%,AC670),1%,0%)
Plz explain this formula
AB67 contains value 45000
AC67 contains 30000-150000
Here is the data:
Total surveys 20 and the csat is 18 where I'm standing at 90%. Iam checking for the formula where, how many csat's required to hit 95%
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
Priya R T
Amit T
.
.
.
...
.
.
..
.
.
Sir
How can import a particular Column from PDF to Excel file how ever i have many of PDF files and I want Import a particular Column (which contains some value) from PDF to Excel Please Help for this formula or Micro Please and Please
i am trying to develop a syntax that will allow me to classify patients as having metabolic syndrome. The syndrome satisfies the condition meeting any 3 out of 5 criteria. My data is in excel. truly appreciate for any help.
Thanks, Anam
It's almost impossible to give you a definite formula without any details on the data you use in your table. It would be helpful to know whether the results are numbers or text and which criteria they should meet… What I can do is to only show you a super simplified example of what your data and function may look like
=IF(COUNTIFS(B2:F2,"yes")>=3,"metabolic syndrome","")
COUNTIF counts the number of met criteria, and if it's more than or equal to 3, it returns the result with a diagnosis, otherwise the cell remains empty.
E4 shows years experience.
I need H4 to show the percentage equivalent from the table below.
10-15 years = 1.500
15-20 years = 2.250
20-25 years = 3.250
25-99 years = 4.500
I so appreciate any assistance.
Hi
I want to match a column in a table or array by using vlookup formula it give me just one column from the table can i match the column and pick the whole table with help of vlookup if yes then please give me some hints
Brief Explanation:
For example
=VLOOKUP(C17,A3:H7,4,FALSE)
the desire column index is "4" it will take just column 4 what if it take the whole table not just a column?? can i do this please help me
Thank you.
Can someone please help me with this formula
- IF(COUNTIF($AA4:$AD4,"Y")>1, COUNTIF(AA4:AD4, "EXCLUDED")>1, "Y","N"
This helped. I wanted to give scores from result based on percentage. Thanks
=IF(AND(J5>=100%,J5=121%,J5=141%,J5200%,1,IF(J5<100%,5,"")))))
I need help using the IF function, to work out the score range
High =9-13
Moderate =5-8
Low 0-4
Thank you
=IF(AND(A1>=9,A1=5,A1<=8),"Moderate",IF(A1<=4,"Low","")))
Hello,
I need help with using the IF function. In the example that my instructor uses, it looks like this:
=IF([@[Billable Hours]]>4, "highly billable", "low billable"}
when he clicks on cell D2 (the title is called billable hours)it actually shows up like this, however when i click on the cell D2 its just coming up as D2 not as ([@[Billable Hours]]
If i leave the formula as saying D2 verses [@[Billable Hours]] i notice it doesnt come out right. How to I get the formula to recognize the title in the spreadsheet?
thank you
Hi!
the formula is if(and(d2="mkt",e2="manager"),20000,18000),if(and(d2="mkt",e2="officer"),15000,13000)where is worng plz help me.
shukriya
I'm stuck. I have a sheet with a cell, D7, that can have "HM", "WH", "CM" or "HOTH" in it. Then there is a second cell, F7 that can be blank or have an "x" in it. What I need is a formula, lets say in AA7, that will return a "1" if D7 has "HM" and there is an "x" in F7. But it needs to return "2" if D7 has "WH" and there is an "x" in F7. So on and so forth with "CM" returning "3" and "HOTH" returning "4", but remaining blank if there is no "x" in F7. Please help.
I am trying to get get a formula to work for the below can anyone help.
In column c I have either a yes or no answer I'm column d I want to have it that if any of these 5cells in column c have a no answer this cell shows 0% and only if all cells show a yes answer then it shows 25% for all cells together not individually.
Hello,
I want to find out a solution for a simple problem. my query is I have to find out a solution with if statement. the condition is i have one rows and three columns, in that first row only numeric values will be there, in 2nd row it has only either yes or no only, 3rd one will be empty one.In the 3rd column i have display if 2nd columns has yes means it has print numeric value of 1st or else it has to display zero in 3rd. please provide a solutions as soon as possible.
Thanks and Regards,
D.Rameshkumar
Column A includes customers who order in 2015, and column B customers who ordered in 2016. I want to have customers who ordered in 2015 but not in 2016. Is there a formula?
Dear All,
I want to combine the below formula in one formula, how do I do that?
IF(A1="Male", IF(B1>18, "Man", "Boy"), "Boy")
and
IF(A2="Female", IF(B2>18, "Woman", "Girl"), "Girl")
Thanks in advance :-)
create a table with 2 columns and 10 rows in excel and give the name to the first column "color" 2nd column "Green Or Red" .If the First Column Contains green or red value the second column should return true otherwise false.
[Hint:Use Logical Function]
Madam Plzzzzz Solve This One I just want know How Could You Will Do this
I can not see my yesterday post about if formula!
thanks
Hello Sevelana
there is a formula uses for blan means if(isblank( A2,"",..........but it calculate the 0 is nothing it does not shown as blank formula cell.
thanks for feedback
Hi
I want to use a formula like =if A2>01012017,A2<01022017,"Jan",...
thanks for your help
Hello all,
I have a requirement that I am not sure how to handle in Excel.
Here is my scenario
Total Price (this takes the # of Apartment Units and multiplies by the Per Unit Price) OR...I can manually enter in the Total Price and it will then automatically calculate the Per Unit Price.
I want to be able to enter in either field and have the other field calculated AND I don't want to lose the formulas I have in the cells. So, I could change one field and calculate the other field and then change the other field and have the other field calculated.