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 21. Total comments: 1256
Hi thanks for the article. Very clear and informative, as always.
I recently experienced a curious problem related to the operators. Assume that I have the following cells:
In A1 I have the formula =TODAY()
In B1 I have an alphanumerical string, say QWRSTY-11. The last two numbers represent the calendar week
In C1 I have the following formula
=IF(ISOWEEKNUM(A1)<RIGHT(B1,2),"X","0")
Since today is June 14, 2016 and the week number is 24, the formula should return "0". Instead, it returns "X".
To double check my formulas, in D1 I entered the following:
=ISOWEEKNUM(A1)-RIGHT(B1,2)
This formula returns "13", which is correct!!
I will be grateful if you could help me understand what's going on here. Thanks
I want a formula for excel for below example:
Column A (Medicine Tablets required) has Value 17
Column B ( One strip has maximum tablets) has value 10
Formula required in column C is compare B with A. and decide to order number of strips of medicine. Column C should show strips to be ordered as 2.
Hello, thanks for the great article.
I have a simple question. I have the word "test" in A1 and the formula "=A1>0" in B1. B1 shows "TRUE". How can "test" be greater than zero? I would expect some kind of error message instead of "TRUE".
Hi John,
In Excel any text including an empty string ("") has a higher value than any number. To return an error message for text values and blanks, you can use a formula similar to this:
=IF(AND(ISNUMBER(A1), A1>0), TRUE, IF(ISTEXT(A1), "error: text", IF(ISBLANK(A1), "error: blank")))
Hi i am doing a vacation, sick leave, personal days that employees are taking. I have created the table and coded vacation as (V), sick leave as (S) and personal as (P). I also have them by month so what i want to do know is to do as when i select V on an employee reflects on the vacation colunm and in sick leave the samething to happen in there repective colunms. Can some one please help me?
Thank you in advance,
Vasni
i want to highlight false if condition is
A B C
650000 650000 650000
if a=0 and b>0 = c>0
Hi, I need a formula for one cell:
IF A2 has a value between 101-122, then text "below average"
IF A2 has a value between 123-144, then text "average"
IF A2 has a value between 145-200, then text "above average"
IF A2 has a value between 201-250, then text "not acceptable"
Thank you.
Hi, I need help on a formula :
Cell A1 is a drop-down list with "Apple", "Orange", "Carrot".
Cell B1 is a vlookup on item in Cell A1 with value for "Apple" = 200, "Orange" = 300 and "Carrot" = 400.
Cell C1 is the quantity to order.
Cell D1 is a vlookup on the minimum amount that we need to pay to the vendor for each type of goods with "Apple" = 2000, "Orange" = 2500 and "Carrot" = 3500. (If order amount is less then the minimum amount, we would have to pay the minumum amount).
Cell E1 - Would like to have a formula to calculate the amount that we need to be paid after selecting the item in A1 and input the quantity in C1.
Thank you.
Edit: I should have mentioned also my text is being generated through a data validation list.
Hello I'm after some help on a solving a problem driving me crazy:
Basically I would like to increase days in a date (e.g. 1/6/16) based on text in other cells then reset/override the original 'if' statement if need be.
For example
Cell E5 = 1/6/16, I now select text in cell F5 "add 20" the date is now 21/6/16
Cell E5 = 21/6/16, I now select text in cell G5 "reset back to original date" and basically want the date to reset back to the 1/6/16
Is this possible ?
Hello I'm after some help on a solving a problem driving me crazy:
Basically I would like to increase days in a date (e.g. 1/6/16) based on text in other cells then reset/override the original 'if' statement if need be.
For example
Cell E5 = 1/6/16, I now select text in cell F5 "add 20" the date is now 21/6/16
Cell E5 = 21/6/16, I now select text in cell G5 "reset back to original date" and basically want the date to reset back to the 1/6/16
Is this possible ?
1/6/16
Hi I need solution for this in a single formula :
when value is less than or equal to 50 it gives result zero
when value is between 50 to 70 it multiplies with 4 & when value is more than 70 or more it multiples with 3
50 = 0
50-70 = 58*4
70 plus = 110 * 3
DEAR CONGRATS,
YOUR SUPPORT IS AMAZING,
PLEASE HELP ME IN SAMPLE FORMULA OF
TRG VS ACH IF 100% ACH THEN YES WITH COLOR TAP IF NOT THEN WITH OTHER COLOR TAP.
THANKS
Hello looking for help on a spread sheet that I am making for the baseball team, basically you have two teams score one in A1 and the other in A2, say the score in A1 is 5 and A2 is 8. then I have a win and a lost column say B4 is win and B5 is lost. I am trying to make it once you put the score in it will mark the win or lost box for me.
thanks
The formula above got a little funky, this is the one I am using (Only the first one is different)
=IF(AND(B4>=1,B4=51,B4=76,B4=101,B4=126,B4=151,B4=176,B4=201,B4=226,B4=251,B4=276,B4=301,B4=326,B4=351,B4=375,B4=401,B4=426,B4=451,B4=476,B4=501,B4=534,B4=567,B4=600,B4=634,B4=667,B4=700,B4=733,B4=766,B4=799,B4=832,B4=865,B4=898,B4=931,B4=964,B4=997,B4=1051,B4=1101,B4=1151,B4=1201,B4=1251,B4=1301,B4=1351,B4=1401,B4=1451,B4=1501,B4=1551,B4=1601,B4=1651,B4=1701,B4=1751,B4=1801,B4=1851,B4=1901,B4=1951,B4=2001,B4=2101,B4=2201,B4=2301,B4=2401,B4=2501,B4=2601,B4=2701,B4=2801,B4=2901,B4=3001,B4=3101,B4=3201,B4=3301,B4=3401,B4=3501,B4=3601,B4=3701,B4=3801,B4=3901,B4=4001,B4=4101,B4=4201,B4=4301,B4=4401,B4=4501,B4=4601,B4=4701,B4=4801,B4=4901,B4=5001,B4=5101,B4=5201,B4=5301,B4=5401,B4=5501,B4=5601,B4=5701,B4=5801,B4=5901,B4=6001,B4=6101,B4=6201,B4=6301,B4=6401,B4=6501,B4=6601,B4=6701,B4=6801,B4=6901,B4=7001,B4=7101,B4=7201,B4=7301,B4=7401,B4=7501,B4=7601,B4=7701,B4=7801,B4=7901,B4=8001,B4=8101,B4=8201,B4=8301,B4=8401,B4=8501,B4=8601,B4=8701,B4=8801,B4=8901,B4=9001,B4=9101,B4=9201,B4=9301,B4=9401,B4=9501,B4=9601,B4=9701,B4=9801,B4=9901,B4<=10000),"135","")
Hi!
Your website has been a tremendous amount of help. Thank you for taking the time and effort to put this together.
I'm presently working on a formula that has got me somewhat stumped.
The issue I'm having is figuring out how to have my final formula infinite. I'd like the formula to increase the "" within every hundred.
I could manually do an additional formula but I would be doing it forever!
Example:
If there was an additional formula after 10k it would be this but I would have to keep doing that for every 100.
IF(AND(B4>=10001,B4=1,B4=51,B4=76,B4=101,B4=126,B4=151,B4=176,B4=201,B4=226,B4=251,B4=276,B4=301,B4=326,B4=351,B4=375,B4=401,B4=426,B4=451,B4=476,B4=501,B4=534,B4=567,B4=600,B4=634,B4=667,B4=700,B4=733,B4=766,B4=799,B4=832,B4=865,B4=898,B4=931,B4=964,B4=997,B4=1051,B4=1101,B4=1151,B4=1201,B4=1251,B4=1301,B4=1351,B4=1401,B4=1451,B4=1501,B4=1551,B4=1601,B4=1651,B4=1701,B4=1751,B4=1801,B4=1851,B4=1901,B4=1951,B4=2001,B4=2101,B4=2201,B4=2301,B4=2401,B4=2501,B4=2601,B4=2701,B4=2801,B4=2901,B4=3001,B4=3101,B4=3201,B4=3301,B4=3401,B4=3501,B4=3601,B4=3701,B4=3801,B4=3901,B4=4001,B4=4101,B4=4201,B4=4301,B4=4401,B4=4501,B4=4601,B4=4701,B4=4801,B4=4901,B4=5001,B4=5101,B4=5201,B4=5301,B4=5401,B4=5501,B4=5601,B4=5701,B4=5801,B4=5901,B4=6001,B4=6101,B4=6201,B4=6301,B4=6401,B4=6501,B4=6601,B4=6701,B4=6801,B4=6901,B4=7001,B4=7101,B4=7201,B4=7301,B4=7401,B4=7501,B4=7601,B4=7701,B4=7801,B4=7901,B4=8001,B4=8101,B4=8201,B4=8301,B4=8401,B4=8501,B4=8601,B4=8701,B4=8801,B4=8901,B4=9001,B4=9101,B4=9201,B4=9301,B4=9401,B4=9501,B4=9601,B4=9701,B4=9801,B4=9901,B4<=10000),"135","")
Alternatively - If having it be infinite won't work, is there a way to make it so that a message "EXAMPLE" is returned instead of invalid if something over 10,000 is entered.
Thank you so much in advance for your help.
Hopefully you can help me out, I need to subtract 15 minutes when a value (A1) duration is below 6 hours and 30 minutes needs to be subtracted when the value is above 6 hours.
You could help me out by getting the value 0:15 or 0:30 in a cell so I can subtract the values.
thank you in advance
Hello Jacy,
Try this formula:
=IF(A1<TIME(6,0,0), A1-TIME(0,15,0), A1-TIME(0,30,0))
For more information, please check out this article: Adding and subtracting time in Excel.
I want to set a function in my sheet. My requirement is, If I type AIR/SEA in cell a1 then it should be convert on cell d1 for AIR=10 & SEA=30.
Regards,
Rozer
i want to sum cell a5*b5 but the answer should be less than 16
its important
Check if E1(15) divided by A1(3) is greater than B1(6)???=if(E1............
Like this!!!!
Hi ,
Good day!
i want to compare the values in s26, s27, s28, s29,s30,s31,s32 and begest one shows in u31:
if t>0 then u31show (Ok)
if t=0 then u31 show (Good)
otherwise (No)
Regards
Majed
Hi Svetlana,
Good day!
i want to compare the values in s26, s27, s28, s29,s30,s31,s32 and begest one shows in u31:
if t=0 then u31show (Ok)
if t=0 then u31 show (Good)
otherwise (No)
Regards
Majed
hi,
i have some problem with my work .
there is tow coloum and have different value .i need whichever value is greater in both coloum with value in another coloum.
We have a golf tournament every year. If a player has a handicap e.g 8 he gets a stroke reduction from his score at every hole that has a stroke index of 8 or less. What would be the formula that calculates the players score against the stroke index that reduces the gross score automatically by 1 shot at every hole that has a stroke index of 8 or less?
Team 1 Hole 1 2 3 4
S/I 11 7 5 17
Kevin Kirk H'Cap 8 ? ? ? ?
I am trying to set a banding
if column A=B great but if not i need it to look at a different column and refer to a banding
S>=8 anything above( goto columnx and subtract 8 from total to leave Y
Hope this is enough info
can someone help me how to Use a formula to define the age as given below.
>=80 is ""Old"", >=60 to =35 to <59 is ""Middle"", <34 is ""Youth""."
Hi BK
the conditions which you have given are confusing
assume that the conditions look like
than you can use this formula
Age should be 60-80 then old
35-60 then middle
below 35 youth
=IF(AND(A1>=60,A1<=80),"Old",IF(AND(A1>=35,A1<=60),"Middle",IF(A1<35,"Youth"," ")))
hope this is helpful
how can I put this in formula?
if cow# is above ave, multiply to 10%,if below ave multply 5%
cow 12410
cow 12979
cow 13789
cow 13861
cow 13910
cow 14320
cow 14400
ave 13667
above ave 10%
below ave 5%
I WANT TO TAKE OUT THE PRICE OF A STONE SUPPOSE IT IS .66 AND IT SHOULD BE MULTIPLY BY ITS PRICE THEN WE SUB 32% FROM IT SO I TYPE THE FORMULA IN EXCEL SUPPOSE =(.66*28000-32%)THE RESULT IS 18479.68 I AM GETTING ITS VARRIES WHICH I HAVE TAKEN OUT FROM CALCULATOR THE CALCULATOR SHOWS 12566.4 PLZZZ HELP ME OUT I WANT TO DO IT IN LIMITED TIME WITH THE HELP OF EXCEL.
I WANT TO TAKE OUT THE PRICE OF A STONE SUPPOSE IT IS .66 AND IT SHOULD BE MULTIPLY BY ITZ PRICE THEN WE SUB 32% FROM IT SO I TYPE THE FORMULA IN EXCEL SUPPOSE =(.66*28000-32%)THE RESULT IS 18479.68 I AM GETTING ITS VARRIES WHICH I HAVE TAKEN OUT FROM CALCULATOR THE CALCULATOR SHOWS 12566.4 PLZZZ HELP ME OUT I WANT TO DO IT IN LIMITED TIME WITH THE HELP OF EXCEL.
I need a formula that turns a cell yellow, if the entered data is greater than the data from the previous day. And also turns the cell red once the high limit is reached.
-Svetlana,
Great site- thanks for such a great resource!
I'm trying to create formula to show when a cell/number/Sum is higher than 42.5, then show that number in decimals. Can you help please?
Hi Fred,
Thank you for your kind words. I am not sure I fully understand your goal. Do you want to display no decimal places for numbers less than 42.5 and a certain number of decimal places, say 2, for numbers greater than 42.5? If so, you can use a formula similar to this:
=IF(A1<=42.5, ROUND(A1,0), ROUND(A1,2))
Instead of ROUND you can use any other Rounding function as your business logic requires.
If you are looking for something different, please clarify.
Hi Svetlana,
Please help me.. my requirement are:
condition=> =if(and(z9>=j10,z9 z10=i10
or else=> z10="v"
my current formula is =if(and(z9>=j10,z9<=k10),"wd","v"))
where it is fine, but I want to reconstruct that "wd" or cell value appear to z10 is dependent to cell value of i10 (where I can enter either "wd" or "wn" from cell i10 but will appear to z10 once satisfy the condition if.
Please note z9,j10,k10 are date format; and z10,i10 are text format.
Many thanks!
Marlone
Hi Svetlana,
Good day!
I figured it out the correct syntax that I needed. I inspired with this blog and reviewed old comments until I got an idea and executed it, then it works...Good luck and thanks, you have a very helpful blog!
Thanks and Regards!
Hi Svetlana,
i need when:
h2>8 and i2>123900 then k2give (good)
h2=8 and i2=123900 then k2 give(ok)
h2<8 and i2<123900 then k2 give (0)
many thanks
Hi Majed,
Here's the formula for K2:
=IF(AND(H2>8, I2>123900),"good", IF(AND(H2=8,I2=123900), "OK", IF(AND(H2<8,I2<123900), 0, "")))
how to add farmula
if B3>12 then + 500 ???
how ???????
kindly make farmula
Hi Benson,
You can use a formula similar to this:
=IF(B3>12, B3+500, "")
Hi Svetlana,
The values of column Q are 100, 99.71, 99.21, 94.99, 80.00 & in some places it is <70.
I want the result in column R like, IF Q=100, R = 2, IF Q is b/w 99.99 to 99.50, R = 1.6. If Q is b/w 99.49 to 95.00, R = 1.2. IF Q is b/w 94.99 to 90.00, R = 0.8. If Q is <70 then R = 0.
Please help me with the formula to capture the required value.
Hello Shashikumar,
You can use the following nested If functions:
=IF(Q1>=100,2, IF(Q1>=99.5, 1.6, IF(Q1>=95,1.2, IF(Q1>=90, 0.8, IF(Q1 >=70,0.6,0)))))
You didn't not specify R for Q b/w 70 to 89.99, and I added 0.6. Just replace it with the right value in the last IF statement.
Hi Sveltana,
I am trying to match with an if function the foloowing:
if (item1=y,"y","n")
But then I'm getting error Name?
Thanks,
Item 1 Item 2
Y
Y
Y
Y
Y
Y
Y
Hi Mona,
Try the following formula:
=IF(A2="y", "y", "n")
Where A2 is the first cell of the Item 1 column.
l need help on if the value greater than 50 subtract 30 and is less than 50 add 10
Hi Shylet,
Here you go:
=IF(A1>50, A1-30, A1+10)
My comments are getting broken just as yours were. I'l try again.
For Category 1 (Greater or equal to 12500) use:
=SUMPRODUCT(--(A:A>12499),A:A)
For Category 2 (Greater or equal to 7500, but less than 12500) use:
=SUMPRODUCT(--(A:A>7499),--(A:A2999),--(A:A<7500),A:A)
These formulas will add all the data as well. The "--" identifies a function as Boolean (True or False), so that if it doesn't meet this criteria, it multiples the other values by 0, which in turn makes the total 0. Hope that makes sense and helps. I'm hoping you are familiar with the Sumproduct formula.
sorry that last post did not come out right it should have said -
11=1
7= 2
3=3
<4 =4
HI - WOuld really appreciate help with this pleae x
=IF(J611,1,IF(J67,2,IF(J63,3,IF(J6<4,4))))
What I am trying to do is if J6 is
11 put 1 in the cell with the formula
7 Put 2 in the cell with the formula
3 put 3 in the cell with the formula
<4 put 4 in the cell with the formula
Can anyone help ?
JO
I need help with a formula for a commission grid for my sales team.
I'm trying to get the following answer from the formula.
IF cell G34 is less than 29939.99 = blank
IF cell G34 is greater than 4500 - blank
IF cell G34 is greater than 3000 but less than 4499.99, multiply G34 x D37
Thanks in advance for your help...
Hi Charles,
Try the following formula:
=IF(AND(G34>=3000, G34<=4500), G34*D37, "")
A 14
B 23
C 83
D 74
E 38
hi, i want least 3 value with name. please help......
Hi if I want a cell to display zero or - if the value is equal to or less than lets say 150, how do I do that?
If the numerical value of G4 is between 0-30,then in cell I4,it should show the value of D4,or 0.How to put the formula in I4.
Thanks
Hi i have the follwoing excel exercise and i need assistance;
1.Number Student passes B5=40,B6=25,B7=32,B8=48
2.Life guards allocations are as follows;
Student pass ranges: 0-30 = 2 life guards, 31-60= 4 life guards,61-90= 6 life guards,150+ = 12 life guards
Assume the Number of Lifeguards needed in a day depends on the number of Student passes for that day as given above. Use an appropriate formula to determine the expected number of lifeguards for each day to go into a separate column called Number of lifeguards. Hint: Vlookup formula.
Please provide the Vlookup formula or some formula i can use in my excel.Thanks a lot in advance.
----------------------- | Student |No of life|
|Number of | Guards| |pass range| guards |
|student passes| | ----------------------
-----------------------| | 0-30 | 2 |
| 40 | ? | | 31-60 | 4 |
| 25 | ? | | 91-120 | 6 |
| 32 | ? | | 150+ | 8 |
| 98 | ? | | | |
Table 2:
Hi please assist;
i have the following question pertaining to the above;
Assume the Number of Lifeguards needed in a day depends on the number of Student passes for that day as given in Table 2 above. Use an appropriate formula to determine the expected number of lifeguards for each day to go into Column G. Hint: Vlookup formula.
Please provide the Vlookup formula i can use in my excel.Thanks a lot in advance.
I have 6 columns in Excel (A, B, C, D, E, F). In column A and B containing source of number. And in column C,D,E,F i want formula that comparing column A and B that is containing smaller number,Can you help me please?
A B C D E F
"Permissible
10% payable "Permissible
5% payable
1963 -651.291 0 0 98.15 553.141
1963 -449.040 0 0 98.15 350.89
1963 -336.882 0 0 98.15 238.732
1963 144.131 144.131 0 0 0
1963 467.218 196.300 270.92 0 0
1963 888.713 196.300 692.41 0 0
1963 830.873 196.300 634.57 0 0
1963 824.150 196.300 627.85 0 0
1963 762.288 196.300 565.99 0 0
1963 698.961 196.300 502.66 0 0
a*10%(Positive) (B-C) prositive a*5%(Negitive) (B-E) Negitive
Hi,
I have 3 columns in Excel (A, B and C). In column A and B containing number. And in column C i want formula that comparing column A and B that is containing smaller number and then stating in what column that smaller number. Can you help me please?
A | B | C
------------
3 | 2 | .....
Good afternoon!
I have two columns (M & N) containing dollars. I need to compare the two columns and highlight at least one of them when the difference between the two columns is greater than 25%. Can you please help with a formula for this?
Thanks!
Hello I have a condition where SLA will b written as Meet o Not Meet
And i wan a formula where i can get 95% automatically if that cell contains Meet and 0% if Not meet...
Do we have any way to make e happen?
Hi,
If a cell is having some text & some country name then how can i get only country names in a second cell. I know the exact spell of the country name.
Hello Vaibhav,
The formula depends on your data pattern. For example, i.e. all cells contain some text, followed by a single space, followed by a country name (like text USA, then you can use the following formula:
=RIGHT(A1,LEN(A1)-LEN(LEFT(A1,FIND(" ",A1))))
If your data has some other pattern, please provide a few examples, and we will try to work out a proper formula.
Hi Svetlana;
i have the following excel exercise and i need your assistance;
1.Number Student passes B5=40,B6=25,B7=32,B8=48
2.Life guards allocations are as follows;
Student pass ranges: 0-30 = 2 life guards, 31-60= 4 life guards,61-90= 6 life guards,150+ = 12 life guards
Assume the Number of Lifeguards needed in a day depends on the number of Student passes for that day as given above. Use an appropriate formula to determine the expected number of lifeguards for each day to go into a separate column called Number of lifeguards. Hint: Vlookup formula.
Please provide the Vlookup formula or some formula i can use in my excel.Thanks a lot in advance.
Hi, comparision of more than 2 value, with shows lowest value as L1, 2nd lowest as L2 & as so on. e.g.
A B C D
25000 35000 24000 38000
A L1 24000
B L2 25000
C L3 35000
D L4 38000