Excel logical operators: equal to, not equal to, greater than, less than

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.

Excel logical operators - overview

A logical operator is used in Excel to compare two values. Logical operators are sometimes called Boolean operators because the result of the comparison in any given case can only be either TRUE or FALSE.

Six logical operators are available in Excel. The following table explains what each of them does and illustrates the theory with formula examples.

Condition Operator Formula Example Description
Equal to = =A1=B1 The formula returns TRUE if a value in cell A1 is equal to the values in cell B1; FALSE otherwise.
Not equal to <> =A1<>B1 The formula returns TRUE if a value in cell A1 is not equal to the value in cell B1; FALSE otherwise.
Greater than > =A1>B1 The formula returns TRUE if a value in cell A1 is greater than a value in cell B1; otherwise it returns FALSE.
Less than < =A1<B1 The formula returns TRUE if a value in cell A1 is less than in cell B1; FALSE otherwise.
Greater than or equal to >= =A1>=B1 The formula returns TRUE if a value in cell A1 is greater than or equal to the values in cell B1; FALSE otherwise.
Less than or equal to <= =A1<=B1 The formula returns TRUE if a value in cell A1 is less than or equal to the values in cell B1; FALSE otherwise.

The screenshot below demonstrates the results returned by Equal to, Not equal to, Greater than and Less than logical operators:
Using Equal to, Not equal to, Greater than and Less than in Excel

It may seem that the above table covers it all and there's nothing more to talk about. But in fact, each logical operator has its own specificities and knowing them can help you harness the real power of Excel formulas.

Using "Equal to" logical operator in Excel

The Equal to logical operator (=) can be used to compare all data types - numbers, dates, text values, Booleans, as well as the results returned by other Excel formulas. For example:

=A1=B1 Returns TRUE if the values in cells A1 and B1 are the same, FALSE otherwise.
=A1="oranges" Returns TRUE if cells A1 contain the word "oranges", FALSE otherwise.
=A1=TRUE Returns TRUE if cells A1 contain the Boolean value TRUE, otherwise it returns FALSE.
=A1=(B1/2) Returns TRUE if a number in cell A1 is equal to the quotient of the division of B1 by 2, FALSE otherwise.

Example 1. Using the "Equal to" operator with dates

You might be surprised to know that the Equal to logical operator cannot compare dates as easily as numbers. For example, if the cells A1 and A2 contain the date "12/1/2014", the formula =A1=A2 will return TRUE exactly as it should.

However, if you try either =A1=12/1/2014 or =A1="12/1/2014" you will get FALSE as the result. A bit unexpected, eh?

The point is that Excel stores dates as numbers beginning with 1-Jan-1900, which is stored as 1. The date 12/1/2014 is stored as 41974. In the above formulas, Microsoft Excel interprets "12/1/2014" as a usual text string, and since "12/1/2014" is not equal to 41974, it returns FALSE.

To get the correct result, you must always wrap a date in the DATEVALUE function, like this =A1=DATEVALUE("12/1/2014")
Using Excel's equal to operator with dates

Note. The DATEVALUE function needs to be used with other logical operator as well, as demonstrated in the examples that follow.

The same approach should be applied when you use Excel's equal to operator in the logical test of the IF function. You can find more info as well as a few formula examples in this tutorial: Using Excel IF function with dates.

Example 2. Using the "Equal to" operator with text values

Using Excel's Equal to operator with text values does not require any extra twists. The only thing you should keep in mind is that the Equal to logical operator in Excel is case-insensitive, meaning that case differences are ignored when comparing text values.

For example, if cell A1 contains the word "oranges" and cell B1 contains "Oranges", the formula =A1=B1 will return TRUE.

If you want to compare text values taking in to account their case differences, you should use the EXACT function instead of the Equal to operator. The syntax of the EXACT function is as simple as:

EXACT(text1, text2)

Where text 1 and text2 are the values you want to compare. If the values are exactly the same, including case, Excel returns TRUE; otherwise, it returns FALSE. You can also use the EXACT function in IF formulas when you need a case-sensitive comparison of text values, as shown in the below screenshot:
Using the EXACT function for case-sensitive comparison of text values

Note. If you want to compare the length of two text values, you can use the LEN function instead, for example =LEN(A2)=LEN(B2) or =LEN(A2)>=LEN(B2).

Example 3. Comparing Boolean values and numbers

There is a widespread opinion that in Microsoft Excel the Boolean value of TRUE always equates to 1 and FALSE to 0. However, this is only partially true, and the key word here is "always" or more precisely "not always" : )

When writing an 'equal to' logical expression that compares a Boolean value and a number, you need to specifically point out for Excel that a non-numeric Boolean value should be treated as a number. You can do this by adding the double minus sign in front of a Boolean value or a cell reference, e. g. =A2=--TRUE or =A2=--B2.

The 1st minus sign, which is technically called the unary operator, coerces TRUE/FALSE to -1/0, respectively, and the second unary negates the values turning them into +1 and 0. This will probably be easier to understand looking at the following screenshot:
Comparing Boolean values and numbers

Note. You should add the double unary operator before a Boolean when using other logical operators such as not equal to, greater than or less than to correctly compare a numeric and Boolean values.

When using logical operators in complex formulas, you might also need to add the double unary before each logical expression that returns TRUE or FALSE as the result. Here's an example of such a formula: SUMPRODUCT and SUMIFS in Excel.

Using "Not equal to" logical operator in Excel

You use Excel's Not equal to operator (<>) when you want to make sure that a cell's value is not equal to a specified value. The use of the Not equal to operator is very similar to the use of Equal to that we discussed a moment ago.

The results returned by the Not equal to operator are analogous to the results produced by the Excel NOT function that reverses the value of its argument. The following table provides a few formula examples.

Not equal to operator NOT function Description
=A1<>B1 =NOT(A1=B1) Returns TRUE if the values in cells A1 and B1 are not the same, FALSE otherwise.
=A1<>"oranges" =NOT(A1="oranges") Returns TRUE if cell A1 contains any value other than "oranges", FALSE if it contains "oranges" or "ORANGES" or "Oranges", etc.
=A1<>TRUE =NOT(A1=TRUE) Returns TRUE if cell A1 contains any value other than TRUE, FALSE otherwise.
=A1<>(B1/2) =NOT(A1=B1/2) Returns TRUE if a number in cell A1 is not equal to the quotient of the division of B1 by 2, FALSE otherwise.
=A1<>DATEVALUE("12/1/2014") =NOT(A1=DATEVALUE("12/1/2014")) Returns TRUE if A1 contains any value other than the date of 1-Dec-2014, regardless of the date format, FALSE otherwise.

Greater than, Less than, Greater than or equal to, Less than or equal to

You use these logical operators in Excel to check how one number compares to another. Microsoft Excel provides 4 comparison operates whose names are self-explanatory:

  • Greater than (>)
  • Greater than or equal to (>=)
  • Less than (<)
  • Less than or equal to (<=)

Most often, Excel comparison operators are used with numbers, date and time values. For example:

=A1>20 Returns TRUE if a number in cell A1 is greater than 20, FALSE otherwise.
=A1>=(B1/2) Returns TRUE if a number in cell A1 is greater than or equal to the quotient of the division of B1 by 2, FALSE otherwise.
=A1<DATEVALUE("12/1/2014") Returns TRUE if a date in cell A1 is less than 1-Dec-2014, FALSE otherwise.
=A1<=SUM(B1:D1) Returns TRUE if a number in cell A1 is less than or equal to the sum of values in cells B1:D1, FALSE otherwise.

Using Excel comparison operators with text values

In theory, you can also use the greater than, greater than or equal to operators as well as their less than counterparts with text values. For example, if cell A1 contains "apples" and B1 contains "bananas", guess what the formula =A1>B1 will return? Congratulations to those who've staked on FALSE : )

When comparing text values, Microsoft Excel ignores their case and compares the values symbol by symbol, "a" being considered the lowest text value and "z" - the highest text value.

So, when comparing the values of "apples" (A1) and "bananas" (B1), Excel starts with their first letters "a" and "b", respectively, and since "b" is greater than "a", the formula =A1>B1 returns FALSE.

If the first letters are the same, then the 2nd letters are compared, if they happen to be identical too, then Excel gets to the 3rd, 4th letters and so on. For example, if A1 contained "apples" and B1 contained "agave", the formula =A1>B1 would return TRUE because "p" is greater than "g".
Using Excel comparison operators with text values

At first sight, the use of comparison operators with text values seems to have very little practical sense, but you never know what you might need in the future, so probably this knowledge will prove helpful to someone.

Common uses of logical operators in Excel

In real work, Excel logical operators are rarely used on their own. Agree, the Boolean values TRUE and FALSE they return, though very true (excuse the pun), are not very meaningful. To get more sensible results, you can use logical operators as part of Excel functions or conditional formatting rules, as demonstrated in the below examples.

1. Using logical operators in arguments of Excel functions

When it comes to logical operators, Excel is very permissive and allows using them in parameters of many functions. One of the most common uses is found in Excel IF function where the comparison operators can help to construct a logical test, and the IF formula will return an appropriate result depending on whether the test evaluates to TRUE or FALSE. For example:

=IF(A1>=B1, "OK", "Not OK")

This simple IF formula returns OK if a value in cell A1 is greater than or equal to a value in cell B1, "Not OK" otherwise.

And here's another example:

=IF(A1<>B1, SUM(A1:C1), "")

The formula compares the values in cells A1 and B1, and if A1 is not equal to B1, the sum of values in cells A1:C1 is returned, an empty string otherwise.

Excel logical operators are also widely used in special IF functions such as SUMIF, COUNTIF, AVERAGEIF and their plural counterparts that return a result based on a certain condition or multiple conditions.

You can find a wealth of formula examples in the following tutorials:

2. Using Excel logical operators in mathematical calculations

Of course, Excel functions are very powerful, but you don't always have to use them to achieve the desired result. For example, the results returned by the following two formulas are identical:

IF function: =IF(B2>C2, B2*10, B2*5)

Formula with logical operators: =(B2>C2)*(B2*10)+(B2<=C2)*(B2*5)
Using Excel logical operators in mathematical calculations

I guess the IF formula is easier to interpret, right? It tells Excel to multiply a value in cell B2 by 10 if B2 is greater than C2, otherwise the value in B1 is multiplied by 5.

Now, let's analyze what the 2nd formula with the greater than and less than or equal to logical operators does. It helps to know that in mathematical calculations Excel does equate the Boolean value TRUE to 1, and FALSE to 0. Keeping this in mind, let's see what each of the logical expressions actually returns.

If a value in cell B2 is greater than a value in C2, then the expression B2>C2 is TRUE, and consequently equal to 1. On the other hand, B2<=C2 is FALSE and equal to 0. So, given that B2>C2, our formula undergoes the following transformation:

Formula with logical operators

Since any number multiplied by zero gives zero, we can cast away the second part of the formula after the plus sign. And because any number multiplied by 1 is that number, our complex formula turns into a simple =B2*10 that returns the product of multiplying B2 by 10, which is exactly what the above IF formula does : )

Obviously, if a value in cell B2 is less than in C2, then the expression B2>C2 evaluates to FALSE (0) and B2<=C2 to TRUE (1), meaning that the reverse of the described above will occur.

3. Logical operators in Excel conditional formatting

Another common use of logical operators is found in Excel Conditional Formatting that lets you quickly highlight the most important information in a spreadsheet.

For example, the following simple rules highlight selected cells or entire rows in your worksheet depending on a value in column A:

Less than (orange): =A1<5

Greater than (green): =A1>20
Using logical operator in Excel conditional formatting

For the detailed-step-by-step instructions and rule examples, please see the following articles:

As you see, the use of logical operators in Excel is intuitive and easy. In the next article, we are going to learn the nuts and bolts of Excel logical functions that allow performing more than one comparison in a formula. Please stay tuned and thank you for reading!

1,097 responses to "Excel logical operators: equal to, not equal to, greater than, less than"

  1. ganesh says:

    hi
    if cell A 700 then cell B indicates Thousand amount so 0,Cell C indicates hundred amounts like 7(cell a=700) Cell D indicates 00
    If u have any formula to divided cash amount in following format
    1245=1,2,4,5

  2. Wayne Fletcher says:

    Hello,
    I need a formula that looks at a cell and determines the following :
    If the cell is less than 1 or less than -1 then return the text "balanced" ... otherwise return "not balanced".
    Thanks so much,
    Wayne

  3. STANLEY JASIAK says:

    I have multiple lines that have different surcharges bases off of over all weight. I am looking to have the cost read in a cell when weight is in that range and be 0.00 when out of the range. Below at 501# price should be 16.25 and the 10.00 should now be 0.00.
    $10.00 501 $10.00 1-500
    $16.25 501 501-1000
    $25.00 501 0.00 1001-1500

  4. Rajnish says:

    i want same number in 2 different cell and also if i edit one cell value the other should automatically happen.
    How should I do it????

    you need to be use a formule Equal2

  5. XLstudent says:

    Hi,
    I am new to the world of excel and I am trying to figure out how to do the following:

    If value in cells A1:A9 are between 500 and 3000 then total the figures and multiply by cell B to give me the total sum figure. I can't figure this out. can anyone help please?

  6. Frank says:

    HI,
    I'm trying to create a formula to ask(if a1=1 & b1=2 return the contents of b16. Please help.
    Thank You

  7. Ansh Bhardwaj says:

    Delivery Time Receving time

    08-08-2016 13:15 08-08-2016 10:00
    12-08-2016 15:02 10-08-2016 13:20
    15-08-2016 18:05 15-08-2016 14:40
    25-08-2016 15:05 20-08-2016 9:55
    30-08-2016 18:03 28-08-2016 9:55
    1.delivery Time always greater then Receiving time(Date Also) then nothing count.

    2)If delivery time below 1 hour compare to Receiving time then count it.
    3)Delivery time and receiving time gap is 1 hour no issues but below 1 hour and above should be count.

    it means that i need all variable conditions please help me.

  8. Kamil says:

    Hi

    I got an excel sheet that contains data of soil moisture content (h), Field Capacity (hfc) permanent wilting point (hpwp) and evapotranspiration (ETp)
    in order to calculate actual evapotranspiration (ETa), the following condition has to be build in excel

    ETa = ETp     when h ≥ hFC
     ETa = ETp * ( h-hWP / hFC  - hWP )  when hWP< h < hFC  
    Et = 0              when h ≤ hWP

    Thank you

  9. Kamil says:

    Any idea in how to perform this equation with these conditions in Excel

    Thank you

  10. Ulderic Limbu says:

    How will I use "IF" formula for the following condition,
    if less than 1.49 print 1
    if greater than 1.49 and less than 2.49 print 2
    if greater than 2.49 and less than 3.449 print 3 ...and so on...

  11. Mukesh says:

    i m trying to create a formula for postal charges

    where

    1) postage upto 5000

    2% of the amount recovered or Rs 50 whichever is more

    2) above 5000

    Rs. 100 + 1% of amount exceeding Rs. 5000

    regards

    Mukesh
    7358027745

  12. SARFARAJ says:

    Hi,
    How can I find the difference between two columns using the if function?
    Thanks and Regards,
    Sarfaraj

  13. NADEEM says:

    i have formula for pre vat calculation for exampal dealer price 10000/- then after show the pre vat amount

  14. sunil says:

    I have formula applied for true false if greater or less but if the result is in negative it is saying true but in actual it should false, please suggest

  15. prasad says:

    i need fomula
    A1>=0 B1>= 0

    i want to add A1& B! both to display in C1
    condtion : to show the value once enter in the A1 or B1 only.
    =IF(A1+B1>=0,A1+B1)
    no need false option. i need to show value 0 in C1 only when i enter A1 or B1 =0

  16. Joy says:

    Hi, I need a formula. if AP column is equal to 0, "FULLY PAID", if AP column is equal to D column, "UNPAID" and if AP column is not Zero, "PARTIALLY PAID"

    Thanks! it will be a great help

  17. Dakota says:

    Hi,
    I need your help. I am trying to create a formula to compare two columns - column A & B. If Column A is greater than Column B, then I'd like the cell in column A to be highlighted. I've tried to create the formula using conditional formatting, rules, "IF" and I've made no progress. Does it matter that the values in column B are the result of a formula? Help!?

  18. mawunyo mensah says:

    how do i use if formula for the below
    underweight <18.5
    normal weight 18.5 to 24.9
    overweight 25 to 29.9
    obesity BMI of 30 0r greater

  19. SI says:

    I want to compare 2 cells means suppose if A1 < minus 10% and B1 < minus 2% then it should give me result "ABC" else "XYZ"

    Can somebody please help me with the formula for this.

    Thanks in advance

  20. rj blue says:

    If Prepayments' column is greater than 0 (zero), then less prepayments amount from Payables cell Total of next month.

    how this will be performed in excel 2010?

  21. JAIVINDAR says:

    I have different value in colum A, I like to put a formula in colum B1 if A1 below 70 than show "a" if between 71 to 90 than show "b" and above 91 than show C

  22. raza says:

    I want salary sheet in this format e.g when we write its designation its daily wage rate is automatically fullfilled.
    WE insert computer update
    computer operator 500
    Clerk 400

  23. Liz says:

    hello
    I need a pretty complicated formula.
    We deal with traveling nurses and their hours and housing.
    I'm needing to know how to calculate if cell A is equal to or great than cell B then cell C stays the same. But is cell A is less than cell B then cell C will be lowering.
    So, if cell A is less than B the equation we use (on paper lol) is A/B=Total then we take the total x C = The actual total I'm needing

  24. Heather says:

    I'm trying to figure out a formula in excel. I'm trying to say if E11:E26 is 299,999 and under then place 1500 in cell, if E11:E26 is 300,000-599,999 then place 3000 in cell, if 600000 and over place 5000 in cell. I can't get equation.. This is what I have
    IF(E11:E26=300000,"3000",>600000,"5000")

    Where am I making the mistake?

  25. Heather says:

    IF (E11:E:26<299999,"1500",600000,"5000")
    Not sure why it changed my formula

    • Hello Heather,

      Our blog engine sometimes mangles formulas in comments, sorry for that.

      As for the IF formula, write it for one cell (E11) rather than for a range, and then copy the formula down to other cells. If you use relative cell references (without the $ sign), Excel will properly adjust the formula for each row. So, here goes the formula:

      =IF(E11>=600000, 5000, IF(E11>=300000, 3000, 1500))

  26. Sonia says:

    Hi Svetlana,

    I need to put formula for below condition
    IF A Scores 0-19% ,He gets -20 Marks
    A Scores 20-39%,He gets -15 Marks
    A Scores 40-50%,He gets -10 Marks.

    Kindly help me out with formula.

  27. axel says:

    please help,

    can u give the formula,, my problem is, 1f the cell A is less than .5 the result should be, "NOT CORRECT" statement, but if cell A is higher .5 the result is CORRECT

  28. BRIJESH says:

    hello mam,
    when we can use
    if(exact(cell 1,cell 2),"true value","false value")
    formula or drag the mouse than both cell value increase but i want only cell 2 value increase or cell 1 stable.

    thanx

  29. Crystal says:

    I am trying to subtract 1 from time if the sum is greater than 5.. So I am using as example =SUM(G5-F5)*24-1 now I only want to subtract 1 if the number is greater than 5. Essentially I am doing a schedule that will calculate hours worked and will automatically subtract 1 hour for a lunch if the person has worked 5 hours to get a lunch..

  30. Chad says:

    If column C1=B1 then show the value of C1. How is this done?

  31. Joe says:

    Hi All,

    I have the following problem:

    A2=First name, B2=Last name, B3=Full name - for example:

    A2=Joe, B2=Hart, B3=Joe Hart

    What i need is if B3 contains B2 = OK - unfortunately i can only find the exact formula but not an partial recognition.

    Hope you can help me somehow. Thanks

  32. Avon says:

    Having trouble getting a formula to work that will give me an outcome for a number between two values, tried this but no luck, what am I missing:

    =IF((A1<=100),11,0)+IF(100.1<=A1<=140,12,0)

    I want it to show "11" if the number in A1 is less than or equal to 100, I want it to show 12 if between 100.1 and 140.

    Thanks.

  33. Aseem Dogra says:

    Hello

    i have a some Data and i want to achieve this formula can you please help.

    if Cell A1="A,B,C,D" and B1='B,C,D,A then C1="Pass" Else Fail )

    thanks

  34. Aqilah says:

    Hye,
    How if I want put two string condition? I'm using this [=AND ($Q9="Open" + $I9="A")] but its not working.

    refer to: 3. Logical operators in Excel conditional formatting

  35. Derek says:

    Hi,

    Need some help:
    Want to leave H3 blank if K3 is less than 1.
    Want to use calculation =K3*1.2-G3 if K3 is greater or equal to 1

    What I have so far:
    =IF(K3>+,"",(K3*1.2-G3))

  36. Mary Jo says:

    I need a formula where a retainer payment is less than $5,000, the referral fee would be 25%; if the retainer is more than $5,000, then the referral fee would be 33%.

  37. Greg says:

    Svetlana,

    I whant to compare and take the value of a cell, but this have a restriction,

    where

    A "If the material is +45kg or -100kg = $2.50/kg"
    B "If the material is +100kg or -250kg = $2.00/kg"
    C "If the material is +250kg or -500kg = $1.5/kg"

    My bulks have weight as:

    Bulk A = 89 kg
    Bulk B = 184 kg
    Bulk C = 349 kg

    How can I use a formula that can take the correct value/kg?

  38. PJ says:

    Hi, im trying to get the criteria for this: i want to get the number of ranges that falls in 31-60 days. Or should i say "less than or equal to 60 but not less than 31" ???????

    Formula: COUNTIF(ranges,"___________")

    Hope you can help me.. Thank you very much.

  39. Sara says:

    Hi,

    I have these commission Payout % :
    10% Of revenue for reaching target
    12% Exceeding target by ≥ 10%
    14% Exceeding target by ≥ 20%

    for example if the :
    Revenue = £2,474
    Target = £2,000
    To Target % = 124%

    How can excel automatically calculate "Commission payable" using the "If FUNCTION" ?

    Thank you a lot for your help.

  40. Anonymous says:

    I'm not sure about this problem, I can't decide what the answer is. If Jeremy is selling m of his models on the Internet for $12.50 each, then which one would describe the amount he will be paid? Is it a)$12-m. or b)$12.50 divided by m. or is it c) m times 12.50. PLEASE HELP I'M SO CONFUSED

  41. Jeremiah says:

    I'm working on this formula to where column "C" contains a value of 8 chars for example "6246FQ76"

    =IF(LEFT(C2,4)>6274,0,IF(N2="CANCL",IF(F2="Y",(I2*E2)*-1,(J2*E2)*-1),IF(F2="Y",I2*E2,J2*E2)))

    I'm trying to create a formula to where if the left 4 values of C2 is less than 6274 then put a zero, if greater than use the formula. the formula works without the IF(left). any recommendations? Thank you.

  42. Whitney says:

    hello,

    I'm working on excel and the formula I need to come up with is "a function to calculate the number of days that are equal to or below 25"

  43. Hannah says:

    Hi,

    I have 3 columns of numbers that I need to compare, ideally they need to be equal to but there is some leeway.

    I'd like to be able to say if A1 is equal to A2 and A3 TRUE, but also if A1 is within 10% of A2 and A3 also TRUE.

    Is this possible?

    Thanks,
    Hannah

  44. Rey says:

    hi,

    im working on excel and in 1 cell i have 3digit number example 345 then i want only to disply in cell is last digit only 5.

  45. Ayman says:

    Hello,

    Could you please help me with the following IF function:

    In cell F9, use an IF function to test if the value in cell D9 is less than or equal to 8. If this condition is true, then multiply cell D9 times cell E4 (the regular rate). If the condition is false, then multiply 8 times E4 and add to that E9*E5 (the overtime rate). Make sure you use absolute cell references in the formula where needed.

  46. Umesh says:

    Hi,
    can you help to sort.
    if the cell (B2 or C2)= zero value then move it to B3 & C3.
    if the cell (B3 or C3)> 0 value then move it to B2 & C2.
    (like largest to smallest)
    for Exm:
    Apple 0 0
    Mosambi 3 12
    Watermelon 0 0
    Banana 0 17
    Orange 4 8

    expected as below

    Mosambi 3 12
    Orange 4 8
    Banana 0 17
    Apple 0 0
    Watermelon 0 0

  47. Jaydon says:

    Hi, i am trying to find a formula in which if a specific value is greater then another (if A1>B1 ), it will input how much larger the value is in another cell ( if A1 > B1 by 5, it will input 5 in C1 for e.g. )

  48. brian says:

    Need help. Creationg daily budget. Need formula to deduct from previous day IF I exceed that day's limit. I want that previous day's value to filter to the next day IF the value is less than zero. Any suggestions?

    Thanks,
    Brian

  49. Kinger says:

    Excellent article. I was struggling to figure out why I was getting the false value when I was using TRUE in quotations "TRUE". I didn't realize TRUE and FALSE behaved differently. THANK YOU!

  50. Craig says:

    Hi,
    I need the formula to show the output below

    0-29,999*7%= ?
    any amount over 30,000*8%= but does not include under 30K amount
    thanks

  51. Rahman says:

    Hi,

    i want automatic sort out the all values less then 9.8 an other sell. so plz tell about formula.

    thanks

  52. ExcelEnthusiast says:

    Hi, i need help...is there a formula where i can add a number if a cell is bigger than another cell by a certain number EXAMPLE...

    if A is greater than B by 2 then add 3, if A is greater than B by 3 then add 5

    Also is there a formula where if C contains "draw" add 1, if C contains "win" add 2

  53. Baiju says:

    Hi Svetlana,

    Need a formula based on below criteria:
    I have two cells, A and B,
    if the difference btw A & B is below 6 or equal to 6, "No Change"
    if above 6 and below 9, "Slight Change"
    if above 10 "Significant Change"

    Also the formula should override the difference with positive numbers, even the B is larger than A, vice-versa

    Please help!!

  54. Baiju says:

    Hi Svetlana,

    Need a formula based on below criteria:
    I have two cells, A and B,
    if the difference btw A & B is less than 6 or equal to 6, "No Change"
    if greater than 6 and less than 9, "Slight Change"
    if greater than 10 "Significant Change"

    Also the formula should override the difference with positive numbers, even the B is larger than A, vice-versa

    Please help!!
    Reply

  55. Florencia says:

    Hello,

    I have a little problem. I have the following formula:

    =COUNTIFS(Doigahama!H5:H214,"=0",Doigahama!P5:P214, ">=0")

    However, the result I get is as if formula said only

    =COUNTIFS(Doigahama!H5:H214,"=0",Doigahama!P5:P214, "=0")

    The > is ignored. If I change the order of the signs to =>, then the = is ignored and I get only the values over, but not including 0. How can I fix this? I was searching for advice, but everywhere I look it seemed that the way to write greater or equal was like that.
    Just in case i also tried using ≥, which did not work either, it was not recognised at all.

    Any advice would be welcomed.
    I am using Office 2010 with Windows 7.

    Thank you,
    Sincerely,

    Florencia

  56. Bharat says:

    Hi,

    How to write in if formula that a cell is greater than 31/12/2015 and less than 01/01/2017. I can't use IF(AND function because I already used AND function for another argument. Or is there any way to specify a particular time period in IF function?

    Thanks in advance.
    Bharat

  57. filafisa33 says:

    Hello,
    I have some Amount like as 20,50,70,100,1000,2000,5000,10000. I am calculating with 10% for each data,but i want (%) value will not less than 10 and not more than 100. How can it possible?

  58. ABDUL HAMEED says:

    A B
    1 0
    -1 0
    0 0
    13 1
    5 0.5

    I need the usage of if condition like above answer
    if less than 5 = 0
    if greater than 5 and less than 13 = .5
    if equal to 13 = 1

  59. Jason says:

    Hi Svetlana,

    If I wanted to perform a mathematical function on a "<" less than number, what would I enter for the formula? e.g. <0.5 * 0.002 = <0.001

  60. Mark says:

    want a formula for .

    if A >= B , IN 90 DAYS , THEN GOOD OR BAD .

    PLEASE SOME 1 CAN HELP PUT THIS IN A FORMULA .

    REGARDS,
    MARK

  61. MISS ROSE says:

    I have the question;

    how to calculate attendance percentage of student who sit to all course assessment and semester exam

  62. MISS ROSE says:

    by using excel work sheet

  63. neeraj sahu says:

    1 A B C D
    2 Date1 Date2 Date3 Date4
    3 1/23/2017 1/22/2017 1/21/2017 1/20/2017
    I want to retrieve the status if A>B, B>C, C>D, "OK", ELSE "NOT OK"

    PLEASE HELP ON THIS.

  64. Kim says:

    Hi
    Please help. How to make condition monitoring with 2 conditons:
    Column Z5 = RELMNC (text)
    Column AA5 = 10 (number)

    In column AA5 I want :
    if left Z5;3 = "REL" , then value >=7, color fill Red
    if left Z5;3 = "REL" , then value <=7, color fill Green

    Thank you.

  65. Raj kumar says:

    Hi

    Need a formula

    if we cosider 3 cells A1, B1, C1

    lets suppose c1=100

    i want to have A1+B1=100(c1) all the time

    if i change A1 to 60 then B1 should automatically display 40
    and if i change B1 to 30 then A1 should automatically change to 70.

    please help me out.

  66. Matt says:

    Hi Svetlana

    Can you help?

    If CELL A1 is greater than Value 75, show Value 4.50

    Any ideas?

    Many thanks in advance.

  67. Rick says:

    Hi there! I'm trying to do what I thought was something simple. I have a column that has 4 possible text inputs: T1, T2, T3, T4. I want the cell to be red if the text is either T3 or T4. How do I set that up? I can easily do conditional formatting if I use just *one* text input, but I can't figure out how to do it with an OR operator. I appreciate any guidance, from anyone!

  68. MADHU says:

    Hi
    I need formula if I travel >=100 kms, it should calculate as Rs 600, If it's <100Kms it should consider Rs 350, please help

  69. Medra says:

    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???

  70. Emily says:

    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

  71. MUKUL says:

    Can we apply a formula greater than equal to when i have dates in two different rows.

  72. Deepak says:

    I need a solution for this
    I want to add 1 to cell h1 if value in cell k11:k2000 is greater than 0.

  73. DMMD says:

    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!

  74. KLEE says:

    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"

  75. SAIRAJ says:

    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

  76. Nabi Ghanizada says:

    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

  77. mike says:

    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

  78. Anthony says:

    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

  79. Tammy says:

    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

  80. Jomarie Pangadlin says:

    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

  81. Sadanandan says:

    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

  82. kyrie says:

    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")

      • Brad says:

        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

  83. Furqan says:

    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

  84. Sarah says:

    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.

  85. Brad says:

    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:
      NestedIf

      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!

  86. Anna Marie says:

    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!

  87. James says:

    =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

  88. Mark H says:

    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.

  89. John Sanl says:

    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

  90. John Sanl says:

    The formula is:
    IF((D3="Men"),(E11>19,E1115,E11<25),RED,NO FILL)

    Thanks for help provided.

    John

  91. Corey L says:

    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

  92. Bobby says:

    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!

  93. Zayn says:

    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!

  94. huzzayf says:

    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

  95. bharat says:

    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%.

  96. NECIE says:

    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

  97. Shanu Singh says:

    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

  98. Aron says:

    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!

  99. Matthew R May says:

    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 :)

  100. Niranjan says:

    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

Post a comment



Thank you for your comment! When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to your query. We cannot guarantee that we will answer every question, but we'll do our best :)