Many tasks you perform in Excel involve comparing data in different cells. For this, Microsoft Excel provides six logical operators, which are also called comparison operators. This tutorial aims to help you understand the insight of Excel logical operators and write the most efficient formulas for your data analysis. Continue reading
Comments on: Excel logical operators: equal to, not equal to, greater than, less than
by Svetlana Cheusheva, updated on
Comments page 14. Total comments: 1256
Cumulative Random Number
665 4,680
1,584 13,031
2,995 21,382
3,658 29,733
4,089 38,084
5,253 46,435
5,687 54,786
6,676 63,137
7,381 71,488
8,116 79,839
8,793 88,190
11,253 96,541
13,331 104,892
13,918 113,243
14,369 121,594
How can i find random number equal or just greater than 4,680 in cumulative column by using formula?
Hi
I would like to write a formula in a cell1 to show that any value equal to or less than cell 1, written in cell 2
Could some one help!
Thanks
Harry
Hi,
I need a formula where I can Min percentage of different cells but ignores the zeros. For example, D4=89%, D15= 70%, D25=0% and D36=60%. How do I get the Min from this?
Thanks,
Di
Hi, I have one sheet where i want to calculate total GST based on percent.
for example :
1 Brass Nozzel 7419 1000 10.00 28 10000.00
2 brass Nipple 1000 100.00 18 100000.00
3 Brass Valve 1000 100.00 28 100000.00
4 Brass Cap 1000 10.00 28 10000.00
5 Brass connector 1000 10.00 18 10000.00
6 Brass Reducer 1000 100.00 18 100000.00
here i want total for all amount for 28% in single formula
and total for all 18% amount in another formula.
Please help me if possible.
Thnks in advanced.
Regards,
Amit
hello There,
try this..
IF(J1860,0,J18))
I need a formula for the below statement:
If J4 is between 30 and 60 then enter the number in G4. If not enter 0
Dear fren,
can u all help me for this formula? even the result below -15% and above 15%, its follow the condition at range 10%.
=IF(AND((-10%)>=Z10=Z10>(-10.001%)),"NEED IMPROVEMENT",IF(AND((-100%)<=Z10<(-15.001%)),"BAD",IF(AND(1%<Z10<=10%),"GOOD",IF(AND(10%<Z1015%,"BAD",FALSE))))))
thanks
(-0.001%) to (-10%) GOOD
(-10.001%) to (-15%) NEED IMPROVEMENT
>(-15.001%) BAD
0% to 10% GOOD
10.001% to 15% NEED IMPROVEMENT
>15.001% BAD
I don't know if this is possible but I want to get a cell to fill a certain color if another one is equal to or less than a certain number and vise versa i.e. if D5 is equal to or less than 84% than B5 will fill a certain color. Thanks.
I am trying to find out a formula for this one
eg :
1st 2nd 3rd 4th 5th 6th 7th
21.80 61.40 120.80 200.00 299.00 417.80 556.40
if answer has come greater than 300, value should '0'
in this eg 6th & 7th one should 0.
if it is come less than 300, value should true value, like 21.8, 61.4, 120.80
so please help me
1st = 21.8
2 = 61.40
3 = 120.8
4 = 200
5 = 299
6 = 417.8
7 = 556.4
Hi Friends,
I am facing a problem when I write a formula in Excel.
The data: when value is upto $200,000 then 3% of the value, more then $200,000 but upto $1,000,000 then 4% and more then $1,00,000 upto $10,000,000 then 5% of the value, how can I write the formula? please help me. its urgent. thanks to all
HI MADAM
1 - 5m3= 1Sample
6 - 15 m3 = 2Samples
16 - 30 m3= 3Samples
31 - 50 m3= 4Samples
51 m3 and above = 4 plus one additional Sample for each additional 50 m3 or part thereof
HOW TO INPUT VALUE FOR PARTICULAR CUM QTY IN EXCEL
(IF(AND(I120),6,IF(AND(I125),9,IF(AND(I1215),12,IF(AND(I1230),15,IF(AND(I1250),18,IF(AND(I12100),21,(IF(AND(I12150),24,"VALUE")))))))))
I TRIED ABOVE WAY I DIDN'T GET RESULT
=(IF(AND(I120),6,IF(AND(I125),9,IF(AND(I1215),12,IF(AND(I1230),15,IF(AND(I1250),18,IF(AND(I12100),21,(IF(AND(I12150),24,"VALUE")))))))))
IN THIS FORMULA RESULT VALUE NOT GETTING BLANK IS SHOWING
TELL ME SOLUTION FOR THAT ....
=(IF(AND(I120),6,IF(AND(I125),9,IF(AND(I1215),12,IF(AND(I1230),15,IF(AND(I1250),18,IF(AND(I12100),21,(IF(AND(I12150),24,"VALUE")))))))))
THIS FORMULA RESULT NOT GETTING TELL ME SOLUTION FOR THAT ....
Hi
100
200
300
400
500
600
700
I wnat formula for 100 at time up to 1000
how can we write this in excel formula "All student whose mark is more than 200 and less than 250 label it as “average” ".
Hi Sheryl,
we'll keep it simple-
Assume Column A is the students name and Column B contains their grade.
in C1 type;
=IF(AND(D3>200,D3<250),"Average","")"
Hope this helps!
HI,
I am making a spread sheet for conditional formula with if function.
if A10=250 and B10= opc (or Any text) then write specific number in third cell C10
Example: A10 = 250 , B10 = OPC or (Any text) , C10 = 170
Formula =if(A10=250 & B10=OPC,"170","0")
Actually i have cement Weight 250 in Cell A10 and Cement Type in OPC in Cell B10 Now i want that if i write 250 in Cell A10 and OPc in Cell B10 Then Right Price exact 170 in Cell C10
can you give me formula or any solution plz.
Hi, Usman,
Try this one in C10:
=IF(AND(A10=250,OR(B10="OPC",ISTEXT(B10))),170,0)
Hope it helps.
Thanks its works
You're welcome!
Hello, I need help to compare two cells like this. Want to compare cell B1 to A1 and soo on and I need to count every equal result, every A smaller then B and every B smaller then A.
A B Miks S = 1 Result
Miks M = 2 A = B = ?
Miks M Miks L Miks L = 3 A B = ?
Miks S Miks S
Miks L Miks M
A B
Miks M - Miks L
Miks L - Miks M
Miks S - Miks S
Miks S - Miks L
Miks S = 1
Miks M = 2
Miks L = 3
Need to count every equal, smaller and bigger.
A = B = ?
A B = ?
Hello! I have read through many of the comments and have a question:
I am working on a spreadsheet and if the number of years is equal to 0, I need it to be a value, if the number is between 1 and 5 I need a value, if it is 6 to 10 I need a value and if it is 10 to 15 I need a value. Can you help me figure this out?
=0 = $10.50
1 to 5 = $10.75
6 to 10 = $11.15
10 to 15 = $12.75
Thanks so much
You have to use the AND function, so something like =IFS((A1=>1)AND(A1<=5)),10.75), etc.
I have a similar issue but I need it to be if zero equals A5, if 1-5 equals B5, if 6-10 equals C5 and 10+ equals D5.
Can I do the ranges of numbers/years and have the formula grab the answer from calculations in the other cells?
I have one problem please help me
i have 2 value cell D6 value & E6
if value D6 >= E6+(E6*0.5%) answer is E6
if value D6 <=E6-(E6*0.5%) answer is E6
if value D6 < E6-(E6*0.5%) answer is S6
thanks & regards
Niranjan
I have the following formula and Excel continues to give me errors. Help, please!
=IF(B126>20,"A",IF(AND(B126=0),"B",IF(AND(B126(-20)), "C"), IF(B126<(-20), "D")))
Hello, Matthew,
first of all, you don't need to use AND function because you specify only one criterion after it. Then, you missed the logical operator in B126(-20): should B126 be equal to -20? Also, there's no need to use round brackets for negatives in this case.
So, try this formula:
=IF(B126>20,"A",IF(B126=0,"B",IF(B126=-20,"C",IF(B126<-20,"D")))) Please note that you didn't specify what to return when B126<0 but greater than -20; when B126=20 and when B126>0 but lower than 20. Logically the formula requires these criteria too.
But I do hope that the formula above is what you were looking for :)
Hi Svetlana,
Scenario:
F8 = 1
D10 = 0
E10 = 7
I want to create an IF formula to ask if F8 is greater than D10 but less than E10, then return a value of 1. If not, then return a blank.
I created the following:
IF(F8>D10<=E10,1,"")
but this returns a blank instead of 1. I am not sure why.
I am sure it is probably quite simple, but can't seem to solve it - I would be grateful for your help.
Many thanks!
Hi Aron,
you need to use AND in the condition of your formula. Try the following:Excel IF tutorial.
=IF(AND(F8>D10,F8
Hi,
I have a problem if somebody can help me it would be very appreciable. I had wrote many formula but did not get results.
We have data like:
Age Actual Value Exempted Value
50 30000
50 14500
50 25000
60 30000
60 19100
60 35000
We want to get results:
Age Actual Value Exempted Value
50 30000 25000
50 14500 14500
50 25000 25000
60 30000 30000
60 19100 19100
60 35000 30000
Note: There are upper cap for 25000 60
Hi,
Need help on this'
1-24 = A
25-30 = b
31-48 = c
This is my formula but not working
=IF(A1=25,A1=31,"C")))
many thanks,
necie
Hi Guys
some one help me for fixing up this issue,
formula for:-
If the amount is less than 50,00,000 then its 0%, if the amount is between 50 lakhs and 1 cr its 10% and above 1 cr its 15%.
Hi can someone help me in this.
I need to add numbers between given two numbers
Eg.
if I give first number 18 and last number 20 in different cell
the number should generate Automatically in this sequence
18.25
18.50
18.75
19.00
19.25
19.50
19.75
20.00
the sequence should stop to given last number.
please help me out
thank you
Hi,
Can someone assist me with the following request please.
In Column A1, if someone types or pull the following hours of data from the list (8,16,24,32,40,48,56,64,72). The max is 72 hours.
Column B1 should provide the following:
1. Remaining hours left
Column C1 should provide the following:
1. Percentage of hours left from 100%
2. Color scales of stop light (Green, Yellow, Red)
I hope that I was able to make a sense of my request! Thank you in advance!
I am seeking assistance on a formula: If C3-B3 is greater than 51% then E14 is C3-B3*.10
If 100-35 > 51, then 65*.10
Thank you in advance!
Hello,
I'm trying to setup a formula within the conditional formatting. If i have two cells that have the same name in them, then I'm hoping to have the conditonal formatting kick in and shade the a cell a certain color. Where I get stuck is getting the formula to refer to the conditional formatting instead of giving me a text output as the result.
For example, =IF(AND(A1=Bob,A2=Bob),X,X) Where X represents the instruction to shade or not shade a cell.
I know I can put a formula in a conditional format rule but not sure how to make the true/false variables refer to the shading.
Any advise/help would be appreciated!
Thanks,
Corey
The formula is:
IF((D3="Men"),(E11>19,E1115,E11<25),RED,NO FILL)
Thanks for help provided.
John
i have a sheet with a drop down list of "Men" and "Women". i have put the following IF formula based on the criteria of BMR between the range for Men >19 but 15 but 19,E1115,E11<25),RED,BLUE)
I am getting all wrong somewhere. Please rectify the same.
Thanks in advance.
John Sanil
I have a question. I'm trying to work out a formula where, if a figure (A) is lower than a figure range it will equal YES. And if a figure (A) is greater than figure range it will equal NO
Figure range
1 to 12
1 to 24
1 to 36
1 to 120
1 to 240
example: IF (A) is between 1 to 12 = YES, greater than 12 = NO
I hope this makes some sort of sense and you can help.
=IF(AND(G3>=$C$3,G3<=$D$3),"Pass","Fail")
I want to be able to Round both the Less than or Equal to value and the Greater than or equal to value. I want to be able to Round the greater than or equal to value up and and the less than or equal to down. Every time I try and add rounding to the above formula I get errors.
Example Nominal Low Tol. Upper Tol. Act. Act. Form.Above
PD Go Top 0 deg. 27.735 27.735 27.747 27.7471 27.747 Fail
Help! I trying to find if cell A1 is equal to B1 and if it is highlight it a secific color, red. There are about 15k so I'll need to drag the formula down. Please help!
Hello, Anna,
if I understand your task correctly and you need to fill the cells with a colour depending on the result of the formula, then conditional formatting is what you need to take a look at.
You create the following rule:
=$A1=$B1
and apply it to
=$A:$B
Hope this helps.
Hi,
I am trying to apply a formula that returns a Text answer to the greater or less than number difference compared to an adjacent cell.
The purpose is for a 14 question questionnaire with either an A. or B. answer. The answer is determined by the criteria as follows:
"To score your test, add the number of questions you answered A and the
number you answered B"
- If your number of A answers is 3 or more than B answers, you are a
Protein Type.
- If your number of A and B answers are tied or within 2 of each other, you
are a Mixed Type.
- If your number of B answers is 3 or more than A answers, you are a
Carbohydrate Type.
I would like to return the text either "Protein Type", "Carb Type", "Mixed Type" based on the A. and B. totals.
Many Thanks
Hi Brad,
Let's assume that your data is arranged in the following way:
A1:C15 is the table with questions (Qs) and answers (As).
Using COUNTIF in F2 and G2 we can find out the number of 'A' and 'B' answers accordingly:
=COUNTIF(C2:C15,"A") (for F2)
=COUNTIF(C2:C15,"B") (for G2)
Then, in F4 we need to use nested IF function to apply all the conditions you described:
=IF((F2-G2)>=3, "Protein Type", IF(AND((F2-G2)<=2,(F2-G2)>=-2),"Mixed Type","Carb Type"))
or a bit shorter version:
=IF((F2-G2)>=3, "Protein Type", IF(ABS(F2-G2)<=2,"Mixed Type","Carb Type"))
Try to adjust this example formulas to your data, and learn more on the used functions from the links above. Hope it helps!
Hi
I am trying to write a formula to colour code some values by looking at a formula. If the value is higher than the target it should print P, if it is equal to the target G and if it is within 90% of the target A and below 90% of target R. I have it working for some values but not for others.
eg the target is 0.4 - students made 0.4 progress so it should go green but it is going Amber. The 0.4 is calculated from another formula. If I type 0.4 then it goes green. I'm not sure what is going wrong.
Any help greatly appreciated.
Hi, Sarah,
could you send us a workbook sample with the data you use and the result you expect to get to support@ablebits.com? If it's impossible, then open your 'Conditional Formatting Rules Manager' and make sure that Stop If True box is checked for every rule you created.
Hello everyone
Kindly help me out as today i was trying to find out some.i can explain you by an example
Column A
2.5
2.8
2.7
2.44
2.32
1.8
3.2
I want to put them in two different column
Column 1- Value upto 2.5 but if less than 2.5 then put the lower value means if we select 1.8 then result will b 1.8
Column 2 - value which is greater then 2.5 but after deduction of 2.5 mean if we select 3.2 then the result will be 0.7
Please HELP...
im not so familiar using TRUE and FALSE, but i want to know more.
example i have a constant value of 10,000.
i want to know if [cell[A1] 5,000 ] is lesser than [cell[B1] 10,000, and [cell[B1] 10,000 is lesser than [cell[C1] 20,000.
i made this formula, please correct me.
= if(A1<B1<C1, "false","true")
then, if i change the constant value [cell[B1] to be 1,000] the answer must be "FALSE".
how to make this right? my answer always "TRUE" though i change the constant value at cell[B1] to a lesser value.
i know this is wrong, but please correct me, what it should exactly be.
THANKS..
You need to add AND function into your formula for multiple conditions. Try using the following:
=IF(AND(A1<B1, B1<C1),"true","false")
Hi,
I'm having the same problem, only I'm trying to determine a "True","False" outcome for numbers different by a specific amount. How do I write the above solution to include a specific difference. Eg: If A1 is different than B1 by 3 or more then "Text A", but B1 is different by 3 then "Text B". If they A1 and B1 are within a difference of 2 then "Text C".
I have posted a comment below, but found it difficult to explain myself properly.
Many Thanks
Hi Brad,
If my understanding of the task is correct, the following formula should work a treat:
=IF(A1-B1>=3,"text A",IF(B1-A1>=3,"text B","text C"))
If a1 is 40 b1 should be 40 if a1 is 50 b1 should be 50 if a1 is 60 b1 should be 50 kinly send a formula
Hi guys,
How can i create a formula in google spreadsheet if the scenario is this:
I would like to filter per quarter the regularization anniversary of our employees like this
Regularization date example Mar 3, 2017
i want to get the 4th quarter which is October to november
if the regularization date is in October to November it must say Yes, if not NO and N/A.
Please help me
let me know your thought about this.
Thanks
Jomarie
I have a spreadsheet which calculates the time in, time out, and lunch time, giving me a total of working time.
Example below:
7:54 AM 12:00 PM 12:30 PM 2:32 PM 6.13 1.87
A1 B1 C1 D1 E1 F1
Spreadsheet work great for A1 to E1, the problem that I have is F1, I need a maximum number there of no more the 2.0, and for the life of me I can't figure out the formula.
Example that is there now. =IF(A1>0, 8-E1)
Can anyone help me out how to figure the formula so that F1 can only equal up to 2.0 or less.
Thanks, Tammy
The score - 21-16 = 5, 16 - 21 = -5 ok but I would like to put "D" instead of blank when the score is draw (21-21)
Thanks in advance
If cell a has 5 and cell b has 4 I want to know if cell a is greater then cell b …….BUT only if it is only great then 1
Hi everybody,
I am using a simple Excel function
IF(A5=B5,"OK","False")
up to a certain amount it says OK but if the amount of A5 and B5 goes above a certain amount it says False although they are the same.
For examole:
A5=500 B5=500 OK
A5=600 B5=600 False
A5=700 B5=700 OK
Hello Mam,
I would like to know how to formula for date value is equal or greater than or less then in two cells.
example:
03/04/2017 03/04/2017 true
03/04/2017 03/04/2017 true
03/03/2017 03/04/2017 greater than
03/06/2017 03/04/2017 Less than
03/05/2017 03/04/2017 Less than
03/08/2017 03/03/2017 Less than
Hi, Need help!
If Cell A is grater or less than cell B by 10 units say "check" if not say "ok"
So would look like:
if A1 = 42 and B1= 42 then "OK"
if A1=53 and B1=42 then "check"
if A1 = 31 and B1 = 42 then "check"
Greetings,
Can someone explain to me how I would interpret this?
I understand the TEXT and the RIGHT syntax, but how are the 4 cells being compared? What is the result?
=TEXT(RIGHT(F12,2)>RIGHT(F21,2)>RIGHT(F32,2)>RIGHT(F41,2))
Thank you!
I need a solution for this
I want to add 1 to cell h1 if value in cell k11:k2000 is greater than 0.
Can we apply a formula greater than equal to when i have dates in two different rows.
Hi
Im looking for a formula that if
A2 or A3 are less than or equal to 3 but also greater than or equal to 1, D8 will equal 125. If either one is greater than 3, it will equal 0.
Please help :) Thank you
Hello,
I need help with a formula. I need it to say the following
If SUM A1:A15 >= 20,000 Then return 5% of SUM A1:A15
If 10,000 <= SUM A1:A15 < 20,000 return 4% of SUM A1:A15
If SUM A1:A15 < 10,000 return 3% of sum
How do I do this???