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.

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:

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.

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

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

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.

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:

`=LEN(A2)=LEN(B2)`

or `=LEN(A2)>=LEN(B2)`

.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 1^{st} 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:

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.

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

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

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 2^{nd} letters are compared, if they happen to be identical too, then Excel gets to the 3^{rd}, 4^{th} 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".

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.

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.

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:

- Using IF function in Excel
- How to use SUMIF in Excel
- Excel SUMIFS and SUMIF with multiple criteria
- Using COUNTIF in Excel
- Excel COUNTIFS and COUNTIF with multiple criteria

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

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 2^{nd} 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:

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.

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`

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!

Excel formulas
CSV
Excel functions
Print
Financial functions
Vlookup
Merge data in Excel
SumIf
CountIf
Excel Compare
If statement
Excel Charts
Pivot Table
Conditional formatting
Excel formatting
Excel duplicates
Excel date
Excel tips
Excel macro
Outlook
Google Sheets
Outlook duplicates
Outlook templates

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

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

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

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

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

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?

HI,

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

Thank You

Hi Frank,

Here you go: =IF(AND(A1=1, B1=2), B16, "")

Thank You Svetlana for the swift reply.

I have 10 variables in cell A1 and 3 variables in cell B1.

How do I include any variation of them in cell B16.

The 10 & 3 variables are already in a drop down list

The formula in your reply works well for a single variation.

Thank You again

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.

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

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

Thank you

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

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

Hi,

How can I find the difference between two columns using the if function?

Thanks and Regards,

Sarfaraj

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

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

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

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

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

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

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

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?

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

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

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

Hello Liz,

If my understanding of the task is correct, you can use the following formula:

=IF(A1>=B1, C1, A1/B1*C1)

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?

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

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.

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

Hello Axel,

Here is the formula:

=IF(A1<5, "NOT CORRECT", "CORRECT")

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

Hello BRIJESH,

To fix a cell reference, put the $ sign in front of the column letter and row number, like $A$1. For the detailed explanation about absolute and relative cell references, please check out the following tutorial: Why use dollar sign ($) in Excel formulas

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

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

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

Hello Joe,

The IF function does not recognize wildcard chars, therefore you have to use isnumber/search like this:

=IF(ISNUMBER(SEARCH(B2,B3)), "OK", "")

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.

Never mind the last post, I figured it out.

Thanks,

Avon

Hi I too am trying to solve this may I ask how you did it

Thanks

Hi Chris,

You can use nested if's, like this:

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

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

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

Hi Aqilah,

Here's the correct syntax:

=AND($Q9="Open", $I9="A")

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

Hi Derek,

Try this one:

=IF(K3<1, "", K3*1.2-G3)

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

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?

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.

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.

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

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.

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"

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

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.

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.

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

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

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

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!

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

Hi,

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

thanks

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

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

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

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

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

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?

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

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

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

I have the question;

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

by using excel work sheet

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.

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.

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.

Hi Svetlana

Can you help?

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

Any ideas?

Many thanks in advance.

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!

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

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

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

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

I need a solution for this

I want to add 1 to cell h1 if value in cell k11:k2000 is greater than 0.

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!

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"

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

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

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

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

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

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

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

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

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 Truebox is checked for every rule you created.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!

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.

=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

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.

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

The formula is:

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

Thanks for help provided.

John

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

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!

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!

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

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,

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

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

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