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 5. Total comments: 567
I had been using lotus 123 a lot.
In lotus for doing data querry which i filtering data in excel, i could use criterion as logical conditions like age>5#and#age5, age<9)
does not seem to work
Any solutions
Please can someone help me solving the following problem.
In my case
cell values are as follows:
D67=0,5
D68=0,7
D69=0,9
When I select the value 0,5 (D67) i want it to move to D21, D22 and D23 into my calculation sheet
and when I select 0,7 (D68) I want that this value moves to D21, D22 and D23 of my calculation sheet instead and replacing the forgoing value 0,5 ...and so on.
Is there a solution to this problem?
Kindest thanks for your help
Peter
Hi!
I’m doing some investment cost analysis on a power plant project and could need some help.
So if I have a plant from 400 MW higher use this formula to estimate cost, if below 400 MW then use this other formula with a coefficient. Do you have any ideas how I could write this?
BR
Marco
Hi All,
I need a formula for the following:
If the sale date is more than a year from the purchase date then give me half the gain, if not give me the full gain.
So...
Purchase Date Sale Date Gain Net gain
11/03/2018 12/03/2019 5,000 2,500
13/03/2018 12/03/2019 5,000 5,000
I have seven cells (week days),
Monday 15
Tuesday 20
Wednesday 16
Thursday 0
Friday 0
Saturday 0
Sunday 0
and, i update everyday respective day of week. So, I need an "If" formula, that calculate average only for days that their cell is different from 0? (So, if it is Thursday, when i update that cell of Thursday, I want that the average formula to calculate average of days Monday-Thursday only that their value is more than 0, not rest days).
Thank you :)
Hi
Could you please help me
For example
A1. 100
B1. 50
I need 10%,more than B1 not greater than A1 if the value less than 10% I need A1 valvu
JSC_GPA SSC_GPA HSC_GPA Out_Come
Best Best Best ?
Best Best Good ?
Good Good Medium ?
Best Good Best ?
Best Best Good ?
Best Best Best ?
There are 4 possible categories
* Best
* Good
* Medium
* Low
if 2 of then Best then Out_Come Best
if 2 of then Good then Out_Come Good
if 2 of then Medium then Out_Come Medium
if 2 of then Low then Out_Come Low
Please Help Me....
sir,
please help how to coding in Ms Excel for the give table in ONE cell.
=IF(D10-E10=1,"1",IF(D10-E10=1, AND C10=2,"2",IF(C10-D10=2, AND E10=1,"3",IF(C10=1, ANDE10=2,"4",IF(C10-E10=2, AND D10=1,"5",IF(C10-E10-D10=2,"6"))))))
PLZ CORRECT THE ABOVE FORMULA
Hello,
Could you please help me why my function does not work:
=IF(OR(CO2="4 - High Professional",CO2="7 - Versatile Performer",CO2="8 - Future Star",CO2="9 - Star Performer","Top Talent",IF(CO2="0","Not Reviewed","Rest")))
Thank you for your suggestions
one day i'll understand
same bro same ajwndoiwejfie
Hey so im trying to pick up numbers in negative in the tabel
Cell1 Cell2 (this is in a tabel)
A 2
D -1
A -3
A 1
D 3
E 2
A -1
If i want to only pick up the sum of all "A" that is negative numbers ignoring the positives and the other letters, anyone know how to do that? (the result should be -4 in this case)
I have tried this one but dosent work.
=SUM.IF(Cell1,"A",IF(Cell2<0,Cell2;0))
I am applying double OR function inside IF but getting results in both digits as well as words
Please suggest me
rollno name eng hindi sci maths Total
1 ram 56 100 57 76 289
2 sham 76 68 14 20 178
3 sita 24 91 66 59 240
4 gita 99 83 13 77 272
5 radha 60 63 40 40 203
6 mohan 32 77 65 52 226
Enter roll or name
ram /1 56 100 57 76 289
if enter roll name or name then all sub marks dispaly is it possible using vlookup??
Hi, i want to know the formula in Excel if i need to identify the negative figures and make it zero automatically then what is the formula.
I have an excel sheet with many statements giving results as "True" or "False"
1) Now I need to introduce another column which will say if Cell A1, Cell A2, Cell A3 and Cell A4 are "True" say "Yes" if not say "No"
2) I also need another formula which says if Cell A1 and Cell A4 are "True" and Cell A2 or Cell A3 are "True" say "Yes" if not say "No"
Can Someone please help me
Hi!
I have 2 worksheets containing an identical layout - one is data ran last week called ‘old”containing 6000 rows of customer data and then data ran this week ‘new’ containing 7000 rows of data. Both have one header row. I need to find any changes occurred on each customer record - column A contains a unique ID for each customer and column P is the data range I need to look for changes in). NB; the data will be held on different numbered rows . So to match and highlight unique id’s/customers from column A on the old sheet to column A on the new sheet ( I can do this by using conditional formatting =COUNTIF(old!$A;$A,A2)=1 with a range of $A$:$A$9999. Then I format fill in green to highlight but then of these matched green cases I need to look in column P for any changes to compare old and new values and it’s this part of the formula I need help with.... thanks in advance for any help anyone can offer.
I've been trying to figure this out for two days using various resources and I'm stuck. I feel like the solution is easy and I'm just overthinking it at this point.
Let's say I have a table in Excel with headers and many rows of varying text and numbers, with some cells containing both (random ID numbers generated by an outside source). The table and headers have names for use in other formulas (no trouble there).
Col_With_Hdr_1
A Specific Name In This Cell
Another Specific Name In This Cell
Col_With_Hdr_8
(123abc678ruw9257xyz)
this is literally a blank cell
(2756imv47zqp115mv2)
this is literally a blank cell
Col_With_Hdr_11
formula result displayed as text goes here
formula result displayed as text goes here
this would be blank if formula result is 0 or No
I can't figure out how to write a formula that shows
IF Col_With_Hdr_1="A Specific Name in This Cell" then put specific text in Col_With_Hdr_11
OR
IF Col_With_Hdr_8 IS NOT BLANK then put same specific text in Col_With_Hdr_11
I hope this makes sense, and I appreciate any ideas anyone may have. Thank you!
Hi, I need to calculate the tax for the incomes. If the income is 5000 that is tax free. If the income is greater than 5000 and less than 12500 then, it is 12500-5000 and the remaining is taxed 2% and if the income is more than 12500 and less than 100000 then first 5000 is deducted then 12500 is deducted to be taxed 2% and then the remaining is taxed 10% and if the income is more than 100000 then the 87500 is to be taxed at 10% and then the remaining is taxed 20%. Is it possible to bring them in only one function? Thanks for your kind support.
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
I wrote the following code. The code provides partial result correctly, but not all! I don't know what is the wrong with my code? =IF((Distance/C4)<1, E_saved,(IF((Distance/C4)<2, (E_saved-(1.8*((Distance/C4)-1))),(IF((Distance/C4)<4, (E_saved-(1.8+(3.35*((Distance/C4)-2)))),(IF((Distance/C4)<11,(E_saved-(1.8 +(3.35*2)+(2.375*(Distance/C4)-4))),0)))))))
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.