# Using logical functions in Excel: AND, OR, XOR and NOT

The tutorial explains the essence of Excel logical functions AND, OR,  XOR and NOT and provides formula examples that demonstrate their common and inventive uses.

Last week we tapped into the insight of Excel logical operators that are used to compare data in different cells. Today, you will see how to extend the use of logical operators and construct more elaborate tests to perform more complex calculations. Excel logical functions such as AND, OR,  XOR and NOT will help you in doing this.

## Excel logical functions - overview

Microsoft Excel provides 4 logical functions to work with the logical values. The functions are AND, OR,  XOR and NOT. You use these functions when you want to carry out more than one comparison in your formula or test multiple conditions instead of just one. As well as logical operators, Excel logical functions return either TRUE or FALSE when their arguments are evaluated.

The following table provides a short summary of what each logical function does to help you choose the right formula for a specific task.

 Function Description Formula Example Formula Description AND Returns TRUE if all of the arguments evaluate to TRUE. `=AND(A2>=10, B2<5)` The formula returns TRUE if a value in cell A2 is greater than or equal to 10, and a value in B2 is less than 5, FALSE otherwise. OR Returns TRUE if any argument evaluates to TRUE. `=OR(A2>=10, B2<5)` The formula returns TRUE if A2 is greater than or equal to 10 or B2 is less than 5, or both conditions are met. If neither of the conditions it met, the formula returns FALSE. XOR Returns a logical Exclusive Or of all arguments. `=XOR(A2>=10, B2<5)` The formula returns TRUE if either A2 is greater than or equal to 10 or B2 is less than 5. If neither of the conditions is met or both conditions are met, the formula returns FALSE. NOT Returns the reversed logical value of its argument. I.e. If the argument is FALSE, then TRUE is returned and vice versa. `=NOT(A2>=10)` The formula returns FALSE if a value in cell A1 is greater than or equal to 10; TRUE otherwise.

In additions to the four logical functions outlined above, Microsoft Excel provides 3 conditional functions - IF, IFERROR and IFNA.

#### Excel logical functions - facts and figures

1. In arguments of the logical functions, you can use cell references, numeric and text values, Boolean values, comparison operators, and other Excel functions. However, all arguments must evaluate to the Boolean values of TRUE or FALSE, or references or arrays containing logical values.
2. If an argument of a logical function contains any empty cells, such values are ignored. If all of the arguments are empty cells, the formula returns #VALUE! error.
3. If an argument of a logical function contains numbers, then zero evaluates to FALSE, and all other numbers including negative numbers evaluate to TRUE. For example, if cells A1:A5 contain numbers, the formula =AND(A1:A5) will return TRUE if none of the cells contains 0, FALSE otherwise.
4. A logical function returns the #VALUE! error if none of the arguments evaluate to logical values.
5. A logical function returns the #NAME? error if you've misspell the function's name or attempted to use the function in an earlier Excel version that does not support it. For example, the XOR function can be used in Excel 2016 and 2013 only.
6. In Excel 2016, 2013, 2010 and 2007, you can include up to 255 arguments in a logical function, provided that the total length of the formula does not exceed 8,192 characters. In Excel 2003 and lower, you can supply up to 30 arguments and the total length of your formula shall not exceed 1,024 characters.

## Using the AND function in Excel

The AND function is the most popular member of the logic functions family. It comes in handy when you have to test several conditions and make sure that all of them are met. Technically, the AND function tests the conditions you specify and returns TRUE if all of the conditions evaluate to TRUE, FALSE otherwise.

The syntax for the Excel AND function is as follows:

AND(logical1, [logical2], …)

Where logical is the condition you want to test that can evaluate to either TRUE or FALSE. The first condition (logical1) is required, subsequent conditions are optional.

And now, let's look at some formula examples that demonstrate how to use the AND functions in Excel formulas.

 Formula Description `=AND(A2="Bananas", B2>C2)` Returns TRUE if A2 contains "Bananas" and B2 is greater than C2, FALSE otherwise. `=AND(B2>20, B2=C2)` Returns TRUE if B2 is greater than 20 and B2 is equal to C2, FALSE otherwise. `=AND(A2="Bananas", B2>=30, B2>C2)` Returns TRUE if A2 contains "Bananas", B2 is greater than or equal to 30 and B2 is greater than C2, FALSE otherwise.

#### Excel AND function - common uses

By itself, the Excel AND function is not very exciting and has narrow usefulness. But in combination with other Excel functions, AND can significantly extend the capabilities of your worksheets.

One of the most common uses of the Excel AND function is found in the logical_test argument of the IF function to test several conditions instead of just one. For example, you can nest any of the AND functions above inside the IF function and get a result similar to this:

`=IF(AND(A2="Bananas", B2>C2), "Good", "Bad")`

For more IF / AND formula examples, please check out his tutorial: Excel IF function with multiple AND conditions.

#### An Excel formula for the BETWEEN condition

If you need to create a between formula in Excel that picks all values between the given two values, a common approach is to use the IF function with AND in the logical test.

For example, you have 3 values in columns A, B and C and you want to know if a value in column A falls between B and C values. To make such a formula, all it takes is the IF function with nested AND and a couple of comparison operators:

Formula to check if X is between Y and Z, inclusive:

`=IF(AND(A2>=B2,A2<=C2),"Yes", "No")`

Formula to check if X is between Y and Z, not inclusive:

`=IF(AND(A2>B2, A2<C2),"Yes", "No")`

As demonstrated in the screenshot above, the formula works perfectly for all data types - numbers, dates and text values. When comparing text values, the formula checks them character-by-character in the alphabetic order. For example, it states that Apples in not between Apricot and Bananas because the second "p" in Apples comes before "r" in Apricot. Please see Using Excel comparison operators with text values for more details.

As you see, the IF /AND formula is simple, fast and almost universal. I say "almost" because it does not cover one scenario. The above formula implies that a value in column B is smaller than in column C, i.e. column B always contains the lower bound value and C - the upper bound value. This is the reason why the formula returns "No" for row 6, where A6 has 12, B6 - 15 and C6 - 3 as well as for row 8 where A8 is 24-Nov, B8 is 26-Dec and C8 is 21-Oct.

But what if you want your between formula to work correctly regardless of where the lower-bound and upper-bound values reside? In this case, use the Excel MEDIAN function that returns the median of the given numbers (i.e. the number in the middle of a set of numbers).

So, if you replace AND in the logical test of the IF function with MEDIAN, the formula will go like:

`=IF(A2=MEDIAN(A2:C2),"Yes","No")`

And you will get the following results:

As you see, the MEDIAN function works perfectly for numbers and dates, but returns the #NUM! error for text values. Alas, no one is perfect : )

If you want a perfect Between formula that works for text values as well as for numbers and dates, then you will have to construct a more complex logical text using the AND / OR functions, like this:

`=IF(OR(AND(A2>B2, A2<C2), AND(A2<B2, A2>C2)), "Yes", "No")`

## Using the OR function in Excel

As well as AND, the Excel OR function is a basic logical function that is used to compare two values or statements. The difference is that the OR function returns TRUE if at least one if the arguments evaluates to TRUE, and returns FALSE if all arguments are FALSE. The OR function is available in all versions of Excel 2016 - 2000.

The syntax of the Excel OR function is very similar to AND:

OR(logical1, [logical2], …)

Where logical is something you want to test that can be either TRUE or FALSE. The first logical is required, additional conditions (up to 255 in modern Excel versions) are optional.

And now, let's write down a few formulas for you to get a feel how the OR function in Excel works.

 Formula Description `=OR(A2="Bananas", A2="Oranges")` Returns TRUE if A2 contains "Bananas" or "Oranges", FALSE otherwise. `=OR(B2>=40, C2>=20)` Returns TRUE if B2 is greater than or equal to 40 or C2 is greater than or equal to 20, FALSE otherwise. `=OR(B2=" ", C2="")` Returns TRUE if either B2 or C2 is blank or both, FALSE otherwise.

As well as Excel AND function, OR is widely used to expand the usefulness of other Excel functions that perform logical tests, e.g. the IF function. Here are just a couple of examples:

IF function with nested OR

`=IF(OR(B2>30, C2>20), "Good", "Bad")`

The formula returns "Good" if a number in cell B3 is greater than 30 or the number in C2 is greater than 20, "Bad" otherwise.

Excel AND / OR functions in one formula

Naturally, nothing prevents you from using both functions, AND & OR, in a single formula if your business logic requires this. There can be infinite variations of such formulas that boil down to the following basic patterns:

`=AND(OR(Cond1, Cond2), Cond3)`

`=AND(OR(Cond1, Cond2), OR(Cond3, Cond4)`

`=OR(AND(Cond1, Cond2), Cond3)`

`=OR(AND(Cond1,Cond2), AND(Cond3,Cond4))`

For example, if you wanted to know what consignments of bananas and oranges are sold out, i.e. "In stock" number (column B) is equal to the "Sold" number (column C), the following OR/AND formula could quickly show this to you:

`=OR(AND(A2="bananas", B2=C2), AND(A2="oranges", B2=C2))`

OR function in Excel conditional formatting

`=OR(\$B2="", \$C2="")`

The rule with the above OR formula highlights rows that contain an empty cell either in column B or C, or in both.

## Using the XOR function in Excel

In Excel 2013, Microsoft introduced the XOR function, which is a logical Exclusive OR function. This term is definitely familiar to those of you who have some knowledge of any programming language or computer science in general.  For those who don't, the concept of 'Exclusive Or' may be a bit difficult to grasp at first, but hopefully the below explanation illustrated with formula examples will help.

The syntax of the XOR function is identical to OR's :

XOR(logical1, [logical2],…)

The first logical statement (Logical 1) is required, additional logical values are optional. You can test up to 254 conditions in one formula, and these can be logical values, arrays, or references that evaluate to either TRUE or FALSE.

In the simplest version, an XOR formula contains just 2 logical statements and returns:

• TRUE if either argument evaluates to TRUE.
• FALSE if both arguments are TRUE or neither is TRUE.

This might be easier to understand from the formula examples:

 Formula Result Description `=XOR(1>0, 2<1)` TRUE Returns TRUE because the 1st argument is TRUE and the 2nd argument is FALSE. `=XOR(1<0, 2<1)` FALSE Returns FALSE because both arguments are FALSE. `=XOR(1>0, 2>1)` FALSE Returns FALSE because both arguments are TRUE.

When more logical statements are added, the XOR function in Excel results in:

• TRUE if an odd number of the arguments evaluate to TRUE;
• FALSE if is the total number of TRUE statements is even, or if all statements are FALSE.

The screenshot below illustrates the point:

If you are not sure how the Excel XOR function can be applied to a real-life scenario, consider the following example. Suppose you have a table of contestants and their results for the first 2 games. You want to know which of the payers shall play the 3rd game based on the following conditions:

• Contestants who won Game 1 and Game 2 advance to the next round automatically and don't have to play Game 3.
• Contestants who lost both first games are knocked out and don't play Game 3 either.
• Contestants who won either Game 1 or Game 2 shall play Game 3 to determine who goes into the next round and who doesn't.

A simple XOR formula works exactly as we want:

`=XOR(B2="Won", C2="Won")`

And if you nest this XOR function into the logical test of the IF formula, you will get even more sensible results:

`=IF(XOR(B2="Won", C2="Won"), "Yes", "No")`

## Using the NOT function in Excel

The NOT function is one of the simplest Excel functions in terms of syntax:

NOT(logical)

You use the NOT function in Excel to reverse a value of its argument. In other words, if logical evaluates to FALSE, the NOT function returns TRUE and vice versa. For example, both of the below formulas return FALSE:

`=NOT(TRUE)`

`=NOT(2*2=4)`

Why would one want to get such ridiculous results? In some cases, you might be more interested to know when a certain condition isn't met than when it is. For example, when reviewing a list of attire, you may want to exclude some color that does not suit you.  I'm not particularly fond of black, so I go ahead with this formula:

`=NOT(C2="black")`

As usual, in Microsoft Excel there is more than one way to do something, and you can achieve the same result by using the Not equal to operator: =C2<>"black".

If you want to test several conditions in a single formula, you can use NOT in conjunctions with the AND or OR function. For example, if you wanted to exclude black and white colors, the formula would go like:

`=NOT(OR(C2="black", C2="white"))`

And if you'd rather not have a black coat, while a black jacket or a back fur coat may be considered, you should use NOT in combination with the Excel AND function:

`=NOT(AND(C2="black", B2="coat"))`

Another common use of the NOT function in Excel is to reverse the behavior of some other function.  For instance, you can combine NOT and ISBLANK functions to create the ISNOTBLANK formula that Microsoft Excel lacks.

As you know, the formula =ISBLANK(A2) returns TRUE of if the cell A2 is blank. The NOT function can reverse this result to FALSE: =NOT(ISBLANK(A2))

And then, you can take a step further and create a nested IF statement with the NOT / ISBLANK functions for a real-life task:

`=IF(NOT(ISBLANK(C2)), C2*0.15, "No bonus :(")`

Translated into plain English, the formula tells Excel to do the following. If the cell C2 is not empty, multiply the number in C2 by 0.15, which gives the 15% bonus to each salesman who has made any extra sales. If C2 is blank, the text "No bonus :(" appears.

In essence, this is how you use the logical functions in Excel. Of course, these examples have only scratched the surface of AND, OR, XOR and NOT capabilities. Knowing the basics, you can now extend your knowledge by tackling your real tasks and writing smart elaborate formulas for your worksheets.

### 406 Responses to "Using logical functions in Excel: AND, OR, XOR and NOT"

1. Molly says:

I'm tracking recommendations that have been given to my employees on a monthly basis. The data is the employee name and what type of recommendation they received (from colleague, stakeholder, or candidate). I've created pivot tables to track the number of recommendations for each employee and by which type of recommendation. I'm trying to create a chart that shows which employees have received recommendations from all 3 types and which have received them from at least two different types.

Thank you so much for any help/advice you can offer.

2. skye says:

=IF(AND(E12>0,"Closed","Open"), OR (B12>0,"Open".""))

What's wrong with my formula

I'm trying to say if E12 has no date(there is a reference formula in it so I used E12>0) then it equals blank being "", but if it has a date in it, it is open.

Then if B12 has no date it is open, but if B12 has a date, it is closed. B12 also has a reference formula in it.

• Patrick Charles says:

Are these two different formulas you need or one?

=if(e12=0," ","Open")
=if(b12=0,"Open","Closed")

3. Sky says:

Struggling with a formula:

=IF(AND(E12>0,"Closed","Open"), OR (B12>0,"Open".""))

What's wrong with my formula

I'm trying to say if E12 has no date(there is a reference formula in it so I used E12>0) then it equals blank being "", but if it has a date in it, it is open.

Then if B12 has no date it is open, but if B12 has a date, it is closed. B12 also has a reference formula in it.

4. Sky says:

=IF(AND(E12>0,"Closed","Open"), OR (B12>0,"Open".""))

5. Leslei says:

I have a worksheet that I would like to create a formula form based on 2 different conditions on 2 separate cells. Could someone help me?

I need to write the following:

IF b10=Rick Otero (names comes from a table list), then I want the return value to be \$0.00, otherwise, if not Rick Otero AND b13>499999, then value is \$350.00, otherwise \$250.00

Any help would be appreciate it. I am able to write both formulas separately, but unable to tie together. Thanks

6. JOLLY. says:

PLEASE HOW DO I CREATE THIS FORMULAR. FOR EXAMPLE, IF A3 > B2, SUBTRACT B2 FROM A3 AND IF IT IS REVERSE AS IN IF B2 >A3 , SUBTRACT A3 FROM B2.

7. Saurabh Chandani says:

I want to use If formula. Generally we do =if(A2>4, "YES", "No") and i want to use =If(A2>4, "B2", "") but is not working. Any idea for do this formula.

• Saurabh Chandani says:

8. Liz says:

Hi,

Here's one for you:
I've got a column containing the following values: "1", "2", "3", and "-".
I want to nested If/OR formula to return the follow.
If Column A contains a "1", return a 1
If Column A contains a "2", return a 0
If Column A contains a "-" OR "3", return a 'FALSE'

What's the best formula?

• Hi Liz,

Try this one:

=IF(A1=1, 1, IF(A1=2, 0, IF(OR(A1=3, A1="-"), FALSE, "")))

9. Mitul says:

A=1,2,3,4,5,6,7,8,9 so take value 0
B=0,2,3,4,5,6,7,8,9 so take value 1
so which formula use for this help me on this formula.

10. t srinivas says:

In Excel under the head "LOCATION" there are 3 cities namely 'X', 'Y' and 'Z'

The % age of HRA under the head "HOUSE RENT ALLOWANCE" is assigned to the cities are 'X'=30%, 'Y'=20% and 'Z'=10% respectively.

I wish to put a condition in the cell under the head "HOUSE RENT ALLOWANCE" that if the location is 'X', it should return the value 30%, If 'Y', it should return the value 20% and if 'Z' it should return 10%

11. Pramod_91529091443 says:

Hi Ma'am,
I am stuck in if formula as I want to multiple values in True & False both category but not able my formula is

=IF(BD22="Old",(IF(AND(I22=1,AP22="GBT"),33000,IF(AND(I22=2,AP22="GBT"),31000,IF(AND(I22=3,AP22="GBT"),29000,IF(AND(I22>=4,AP22="GBT"),27000,IF(AND(I22=1,AP22="RTT"),22000,IF(AND(I22=2,AP22="RTT"),21000,IF(AND(I22=3,AP22="RTT"),20000,IF(AND(I22>=4,AP22="RTT"),19000

False Value is

,IF(AND(I22=1,AP22="GBT"),33000-33000*7.5%,IF(AND(I22=2,AP22="GBT"),31000-31000*7.5%,IF(AND(I22=3,AP22="GBT"),29000-29000*7.5%,IF(AND(I22>=4,AP22="GBT"),27000-27000*7.5%,IF(AND(I22=1,AP22="RTT"),22000-20000*7.5%,IF(AND(I22=2,AP22="RTT"),21000-21000*7.5%,IF(AND(I22=3,AP22="RTT"),20000-20000*7.5%,IF(AND(I22>=4,AP22="RTT"),19000-19000*7.5%))))))))))))))))))

12. MOHD OWAIS says:

Hi Amanda,
I want to ask you,How we use AND or OR logical function with IF condition?

13. Ironweed says:

I have to make 3 calculations based on two factors:
1. If a variable 1 (AV) is “Y” and the second variable (AD) is less than or equal to X, then input the third variable (AI) or leave blank.
2. If a variable 1 (AV) is “Y” and the second variable is greater than X, then input the third variable (AI) or leave blank.
3. If a variable 1 (AV) is “Y” and the second variable is greater than X, then input the third variable (AI) or leave blank. I need to calculate for AD2 between 25 and 35 (25<AD2<=35)

For scenario 1, I have: =IF(AND(AV2="Y",AD235),AI2," ")

Thanks

14. Ironweed says:

I have to make 3 calculations based on two factors:
1. If a variable 1 (AV) is “Y” and the second variable (AD) is less than or equal to X, then input the third variable (AI) or leave blank.
2. If a variable 1 (AV) is “Y” and the second variable is greater than X, then input the third variable (AI) or leave blank.
3. If a variable 1 (AV) is “Y” and the second variable is greater than X, then input the third variable (AI) or leave blank. I need to calculate for AD2 between 25 and 35 (25<AD2<=35)

For scenario 1, I have: =IF(AND(AV2="Y",AD235),AI2," ")

Thanks

• Hi!

If you want to handle all 3 scenarios with a single formula, then you have to use nested IFs. To be able to suggest an exact formula, I need to know the actual values for all 3 scenarios because this determines the order of nested IF's.

15. Ironweed says:

For scenario 1, I have: =IF(AND(AV2="Y",AD235),AI2," ")
Thanks - and not sure why this isn't posting correctly

• Our blog engine often mangles "< " and ">" characters in formulas, sorry for this. You can specify all 3 conditions in the AND statement, like this:

16. Bernie says:

How can i combine these formulas MAX(0,180-D5) and MIN(40,180-D5)

17. rAHAT says:

I WANT TO CALCULATE HOUSE RENT BY FORMULA
GIVEN, BASIC SALARY:A2= 100000
NOW
A) HOUSE RENT(B2):( 50% OF BASIC SALARY OR TK 25,000/-WHICH IS HIGHER BETWEEN THEM)

18. Jessica says:

I need help to write an if statement. i have dates in column E, I need the spreadsheet to add 90 days if answer in column F is yes, and add 365 days if answer in F is NO.

• Hi Jessica,

Here's the formula:

=IF(F1="yes", E1+90, IF(F1="no", E1+365, ""))

If Cell M7 in below table is "Buy" then Column P has to return value of (Column O - Column N) and if Cell M7 has "Buy" then Column P has to return value of (Column N - Column O). Data Table for reference is given below
Column →L M N O P
Row ↓ Deal Type Deal price Expected price Profit
7 x Sell 184 169.34 -14.66

8 y 253 277.44 24.44

Formula Input in P7 & P8 is given below:

My Problem is as below:
I have input above formula in cell P7 and P8. Whatever I input in Column M 7 & 8 or leave it blank , it calculates the value by assuming that the condition is "Buy".

20. Geoffrey Saya says:

Thanks so muck my problem is solved

21. JAHANGIR says:

HI Svetlana Mame
I am using an sheet for router of security staff. I want that if an security guard works 7 days "day duty" then the next seven days the auto change the shift to "night duty".
(I use "P" for day duty and "B" for night duty)
Need Ur Help .......

22. sabrin says:

Hi,

There are two answer from two different cells in a row but i want either of one answer.

23. Janel says:

Hi what function can I use to get this result?

If A6 is less than 5 add 1, if not return 5?

24. brian says:

hi,

i am trying to write a formula where if cell b =x or y or z give me a blank any help is much appreciated

25. Gopala Krishna says:

Hiii Mem I Want Formula that is (I want 1 to 10 Or 10 to 20 rows values in column A by sequentially in column B From 1 to 10, Like wise , if I specify in A2 how many rows to take exp 10 row and also i will give the rage to take 1 to 10, or 10 to 20, like wise the same values should come in column c

Ex A1 to A10 in B1 to B10 is it possible to execute the same condition should work with multiple conditions please specify the formula

With Request

K.Gopala Krishna

26. jimmy luke says:

I don't really understand this!!???

27. Mukesh Gohel says:

Dear Ms. Seventha,
I am writing below mentioned formula i need a specific formula for counting for odd numbers i.e. 3,5,7,9 as well even figure. can you please help how to write this both condition in 1 formula for even and odd number for CEll No. L105)
IF(C107="lock bottom",IF(AND(L105=3),(I105*L105)+(G105*L105)+(F105*2)+(H105*2),IF(AND(L105=5),(I105*L105)+(G105*L105)+(F105*3)+(H105*3),IF(AND(L105=7),(I105*L105)+(G105*L105)+(F105*4)+(H105*4),IF(AND(L105=9),(I105*L105)+(G105*L105)+(F105*5)+(H105*5

Regards
Mukesh

28. HASSAN says:

hi trying formula A=B,C=D,E=F,G=H THEN 10000 PLZ HELP

• KITTU says:

hi hassan,

Just try like this if you need through number format keep numbers in another sheet like this(1,2,3,....10000) give formula, and past below sells it will take automatically up N numbers

=IF(1=2," TRUE "," FALS ")

29. pooja gupta says:

In Col I Write a formula to give rank to student based on below table (Without using IF Condition)

In Col I Write a formula to give rank to student based on below table (Without using IF Condition)

=33 but less than 60 Pass
>= 60 but less than 70 3rd Div
>=70 but less than 80 2nd Div
>=80 but less than 90 1st Div
>=90 Distinction

=33 but less than 60 Pass
>= 60 but less than 70 3rd Div
>=70 but less than 80 2nd Div
>=80 but less than 90 1st Div
>=90 Distinction

30. pooja gupta says:

In Col I Write a formula to give In Col I Write a formula to give rank to student based on below table (Without using IF Condition)

=33 but less than 60 Pass
>= 60 but less than 70 3rd Div
>=70 but less than 80 2nd Div
>=80 but less than 90 1st Div
>=90 Distinction

rank to student based on below table (Without using IF Condition)

=33 but less than 60 Pass
>= 60 but less than 70 3rd Div
>=70 but less than 80 2nd Div
>=80 but less than 90 1st Div
>=90 Distinction

Use V lookup function

31. pooja gupta says:

In Col I Write aIn Col I Write a formula to give rank to student based on below table (Without using IF Condition)

=33 but less than 60 Pass
>= 60 but less than 70 3rd Div
>=70 but less than 80 2nd Div
>=80 but less than 90 1st Div
>=90 Distinction

formula to give rank to student based on below table (Without using IF Condition)

=33 but less than 60 Pass
>= 60 but less than 70 3rd Div
>=70 but less than 80 2nd Div
>=80 but less than 90 1st Div
>=90 Distinction

32. Robin says:

Hello I am working tracker that tracks #of observations. In order to be 100% compliant there needs to be 3 observations done but one of those 3 observations need to be a 1 of 2 types of observations. So I need the tracker to show both the total and 100% goal met if one of the two specific observations were completed.

33. JOAN says:

Hi i need a formular that will look up name of employee name and return text "YES" or "NO" if a row contains a negative value. kindly assist. Thanks

34. Usha says:

I'm doing a mark sheet , now one of the teacher wants to have absent appear in marks. If they put '0' marks in the box and I want it to appear as'ABSENT'. But I have put a formula in it to calculate so how do I join together saying that If marks is 0 then grade is absent.

35. Priyank says:

Hi,

I am trying to work on a possibility where if condition on two cells are correct then the formulae should fill the relevant code dependent on it in a new cell.

for example if a15=value(a2:a33) & b15=value(b2:b33) then the C15 should be value(d2:d33); the values in d is unique for all cells where the first two match.

36. Jay says:

hi, i've a excel sheet with multiple column. what formula do i use to calculate the trip time if the criteria is A and B by subtracting D to C

A (Compy) B(veh) C (in) D (out) E (Trip Time)
XYZ XD 3787M 11:26:23 11:26:29 ?
XYZ XD 3658C 11:33:12 11:33:21
XYZ XD1900R 11:36:30 11:36:43
XYZ XD 6522R 12:17:29 12:17:38
XYZ XD 3787M 12:19:30 12:19:40
XYZ XD 3658C 12:21:33 12:21:40
XYZ XD1900R 12:23:04 12:23:14
XYZ XD 6522R 12:24:37 12:24:46
XYZ XD 3787M 12:26:22 12:26:30
XYZ XD1900R 12:28:30 12:28:37
XYZ XD 6522R 12:30:14 12:30:25

37. jim says:

If cell J1,M1,P1 are = a or p I would like cell B1 and cell C1 = 1

• Hi Jim,

Enter the following formula in cells B1 and C1:

=IF(AND(OR(\$J\$1="a",\$J\$1="p"), OR(\$M\$1="a",\$M\$1="p"), OR(\$P\$1 ="a",\$P\$1 ="p")), 1, "")

38. Kesar Jahan says:

A B C D Ans should be like this

3 Blank 3 3 TRUE
7 5 7 7 FALSE
4 3 4 3 FALSE
Balnk Blank 5 5 TRUE
2 Blank 2 Blank TRUE
3 3 1 1 FALSE

I wan to ans like this, please tell me which how can i please...

39. April Aubeeluck says:

hi need a help

logical function that returns 1 if the sales person has reached their target sales and 0 if not.

40. Gemnigirl63 says:

I couldn't get a formula to work in excel where I want to get a value
IF a cell is >100, return a value of 100, but
IF value is <.0032, return a value of 100,"100"),IF(C1<.0032,"<.0032")))
I tried many different combinations :(

41. Gemnigirl63 says:

I just noticed an error in my initial submission
It should be:
IF a cell is >100, return a value of 100, but
IF value is <.0032, then return a value of <.0032
The rest between 100 down to 0 should be the same.
Example:
IF Cell A2= return value of
120 100
100 100
.002 <.0032
0 0
10 10
Thank you.

42. GINA says:

I need a formula to compare B1 and C1 and take the higher amount and subtract from A1 PUT IN D1

43. Avatr says:

I Have 4 Tayp Of Vichel (Innova /Taveera/Ac/NonAc
Waht Type Formula I Use In Extra Km Charge Multipal With 18/14/11/10

Excel Sheet

A B C D E
2 Gen 50 Yes
3 SC 45 Yes
4 Obc 45 Yes

Logic

OBC or SC 45% Yes
Gen Need 50% Ys

45. satyapal says:

Hi Svetlana Cheusheva.
Few days before I am using excel formulas "and" "or" but I have few confution using this formulas I am not sure where is will work.

46. April Aubeeluck says:

Thanks Gemnigirl63

47. Atul sharma says:

I want to apply two conditions for example if transfer rate is 75% and score is between 65-70% then x should be given as 5%of x

48. saI says:

I want to apply thee conditions for example if value*600 or RW > "accept,"reject" and value Equal to Com plate , "TBA.

X=0.5 Y=Com plate z=TBA/Reject/Accept A= Given Value

49. Baz says:

Hi, I'm got this formula to work only if it's not blank, but leave the cell blank if there is no number in it,

=IF(T1<=249,"50",IF(T1<=499,"34",IF(T1<=999,"24",IF(T1=2500,"7")))))

I've tried the isblank but to no avail.

• Hi Baz,

You can add one more If function that checks for blanks, like this:

=IF(T1="","",IF(T1<=249,50,IF(T1<=499,34,IF(T1<=999,24,IF(T1=2500,7)))))

50. Baz says:

Perfect, thanks Svetlana.

51. Sufyan Jumani says:

=IF(C4="D-486","Rs.2,800","")& IF(C4="D-487","Rs.1,800","")& IF(C4="D-488","Rs.3,200","")& IF(C4="D-489","Rs.2,400","")
Using this formula for my deals but in last i can't get total ??
thank you

52. Natalie says:

I need to include a formula that shows if a cell displays 'yes' the cell will fail. It needs to be incorporated in the fomula below.

=VLOOKUP(\$B\$25,\$A\$71:\$D\$76,4,FALSE)

Any help would be appreciated.

Thank you,

Natalie

53. Quinn says:

Please help!!I am trying to do a IF THEN formula over multiple columns where the column is marked with an "x" or is blank. So IF A1="x", then value is E1*G1, or if B1 ="x", then the value is E1*G1, or if C1="x", then the value is E1*G1.
What would be the best formula to use. G1 is a percentage entry. So the value will either be 0%-100%.

I can do one, but don't know how to include all 3 columns .
So far I have: =IF(A1="x",E1*G1,0)

Thank you,
Quinn

54. Ram says:

I want to know the difference between numbers in two different cell, if it is lower than the previous cell it should retain the current value, if it is more than the previous cell it should show the difference.
For eg: If A67=14 and A60=2, the difference or change should show as 2 and not as -12 in A 61

If A67=14 and A60=16, the difference or change should show as 2 in A61

55. SrinivaS says:

Hi I've a requirement. If in a column A and for Row 1 and Row 10, if values are "paid", then the status of "C1" should get updated as "DONE".

Thanks,

SrinivaS

56. Tunde says:

Hi Sevetlana
I want to code this formal I.e

=if(c4*c8<c5"c4*c8","c5")
how will I code this that the answer itself will show in the quotation box not the cell.

57. Neetesh Shivhare says:

xyz A C D F

I Want a formula if A<B<C<D then value "1", if A<B<D<C then value "2",if A<C<B<D then value "3", if A<C<D<B then value "4" and if A<D<B<C then value will be return "5" in single formula ....

Regards,
Neetesh Shivhare , India

58. Teu Saroeun says:

I need help for "if" formula:
if A1="Sunday" equal "0"
if A1="Saturday" equal "4"
if A1="Monday" till "Friday" equal "8"

I don't know how to write a formula.

Best Regards,

Saroeun.

59. Nick says:

I am trying to create a spreadsheet to calculate hours payable from a daily entry of a starting time and a finishing time. From the date, day and time of work I need to extract various pieces of information. I have already managed to get most of the information but one or two problems still challenge me. I have made a list of holidays that are paid at different rate to normal hours) - I would like to test a date to see if it appears as one of the holidays, then to check if it was a Sunday and return the amount of hours worked. I have a few employees and I can do this manually but why if an excel formula can do it automatically for me. This spreadsheet will be an ongoing thing, please point me in the right direction.

Kind regards
Nick

60. KITTU says:

HI

I need a help, I will give small example apart from that, I have a A,B,C up to Z,columns when I had enter in some value(1,2,...) it will be displayed certain sheet as well as I will create like A to Z sheets also, may be that numbers will be repite please reslove

Warm regards
kittu

61. Nick says:

I sorted the holiday lookup problem out using the vlookup function. Now I face another problem, hopefully the last.
In my spreadsheet the information gives a final result of the hours worked. The company takes this figure and pays 195 hours as normal time and the remainder as overtime, i.e. 203:00 normal hours = 195:00 (threshold hours and 8:00 (overtime hours). So this 195 hours is like a threshold value - if 195:00 is reached then the remainder becomes overtime. But here is the thing that also throws problems my way, if the amount of hours worked is below 195:00 then that value needs to be displayed in the threshold hours cell as well as 0:00 in the overtime cell, i.e. Threshold hours 190:00 -- Overtime hours 0:00. I wish I could upload my spreadsheet.

Nick

62. Dan says:

Hi there,

I have a student that is doing research and is looking for a formula with the following conditions:

Context: Each patient may have 3 or more rows of data. He has color coordinated rows that have date of knee surgery as one color and date of knee injection as another color in separate rows.

Looking for a function that will look at each patient and if either of the injection dates were before the surgery date, then generate result of TRUE.

I know the or statement is simple. But need the function to know to compare within the same patient and to compare the Injection fields that are in the white rows to the surgery fields in the green rows.

63. AP says:

Hello!
I am trying to right a nested IF statement and no luck. Each of the below statements works individually, but when I try using them together I am getting the #Value error. Any help would be greatly appreciated!

=IF(AND(R4=Q4,T4=S4), "Match", "No Match"),IF(AND(T4=S4,R4Q4),"Campaign No Match",""),IF(AND(R4=Q4,T4S4),"Adgroup No Match","")

Context:
If values columns R and Q match as well as values in columns T and S, then the value should show "Match"

If values in columns T and S match, but values in columns R and Q do NOT match then the value should show "Campaign No Match"

If values in columns R and Q do match, but values in T and S do NOT match then the value should show "Adgroup No match"

64. Prasanna says:

Hi, want to check error in the formula: if(B5="High", if(C5="High",(5+5)/2,if(C5="Medium", (5+3)/2, (5+1)/2)), if(B5="Medium", if(C5="High", (3+5)/2, if(C5="Medium", (3+3)/2, (3+1)/2))), if (B5="Low", if(C5="High",(1+5)/2, if(C5="Medium", (1+3)/2, (1+1)/2)))

the purpose is to find out average of two values whose rating could be high, medium & low and weightage assigned in High = 5, Medium = 3 & Low = 1. Eg. if the combination of the two values if Medium & High, then the average should be (3+5) /2 = 4.

Kindly guide.

65. Mike Jones says:

As a Charity we have many clients. The spread sheet shows name and age in adjacent cells the next cells show catorgary of age spans ie 1-20, 21-40, 41-60, 61-80, 80-100. Our funder is asking us to show a score of 1 in appropriate catorgary cell corresponding to age. Do I use IF's & and's & or's and would be obliged if you can help...many thanks ...mike

66. Ravi says:

Hi Svetlana,

I have tried to find the many formula however i am not getting success, If you can help me out that is great.

I have a query I am an admin and i want to prepare an attendance however i am not able to figure out which formula to apply, If any one can help me it that would be great.

My company has a Rule that those who are coming before 10:00 we are giving levi, Those check in time between 10:01 to 10:30 are Late and after 10:30 it is halfday and same way those leaving in between 18:00 - 18:30 marked as early check out, Everyone has to check out after 18:31

Those who are meeting the criteria of coming before 10:00 and leaving after 18:30 will be marked present.
Those who are late by 10:00-10:30 and check out after 18:31 marked Late
Those who are coming after 10:31 and leaing after 18:31 marked as halfday.
Below is the biometric record from this i have to prepare attendance of more then 500 employee which is too tedious.
Help me out i will be greatful.

Att. Date InTime OutTime Shift S. InTime S. OutTime
01-Jan-2017 NS 00:00 00:00
02-Jan-2017 09:58 18:56 GS 09:30 18:30
03-Jan-2017 10:13 18:39 GS 09:30 18:30
04-Jan-2017 09:46 18:30 GS 09:30 18:30
05-Jan-2017 09:43 18:59 GS 09:30 18:30

67. mark says:

Hello all,

I have a requirement that I am not sure how to handle in Excel.

Here is my scenario

Total Price (this takes the # of Apartment Units and multiplies by the Per Unit Price) OR...I can manually enter in the Total Price and it will then automatically calculate the Per Unit Price.

I want to be able to enter in either field and have the other field calculated AND I don't want to lose the formulas I have in the cells. So, I could change one field and calculate the other field and then change the other field and have the other field calculated.

68. SAA says:

Hi
I want to use a formula like =if A2>01012017,A2<01022017,"Jan",...

69. SAA says:

Hello Sevelana

there is a formula uses for blan means if(isblank( A2,"",..........but it calculate the 0 is nothing it does not shown as blank formula cell.

thanks for feedback

70. SAA says:

I can not see my yesterday post about if formula!
thanks

create a table with 2 columns and 10 rows in excel and give the name to the first column "color" 2nd column "Green Or Red" .If the First Column Contains green or red value the second column should return true otherwise false.
[Hint:Use Logical Function]

Madam Plzzzzz Solve This One I just want know How Could You Will Do this

72. JMS says:

Dear All,

I want to combine the below formula in one formula, how do I do that?

IF(A1="Male", IF(B1>18, "Man", "Boy"), "Boy")

and

IF(A2="Female", IF(B2>18, "Woman", "Girl"), "Girl")

73. Jean-Paul Dailly says:

Column A includes customers who order in 2015, and column B customers who ordered in 2016. I want to have customers who ordered in 2015 but not in 2016. Is there a formula?

74. Rameshkumar says:

Hello,

I want to find out a solution for a simple problem. my query is I have to find out a solution with if statement. the condition is i have one rows and three columns, in that first row only numeric values will be there, in 2nd row it has only either yes or no only, 3rd one will be empty one.In the 3rd column i have display if 2nd columns has yes means it has print numeric value of 1st or else it has to display zero in 3rd. please provide a solutions as soon as possible.

Thanks and Regards,
D.Rameshkumar

75. ss says:

I am trying to get get a formula to work for the below can anyone help.
In column c I have either a yes or no answer I'm column d I want to have it that if any of these 5cells in column c have a no answer this cell shows 0% and only if all cells show a yes answer then it shows 25% for all cells together not individually.

76. Dave Richardson says:

I'm stuck. I have a sheet with a cell, D7, that can have "HM", "WH", "CM" or "HOTH" in it. Then there is a second cell, F7 that can be blank or have an "x" in it. What I need is a formula, lets say in AA7, that will return a "1" if D7 has "HM" and there is an "x" in F7. But it needs to return "2" if D7 has "WH" and there is an "x" in F7. So on and so forth with "CM" returning "3" and "HOTH" returning "4", but remaining blank if there is no "x" in F7. Please help.

Hi!
the formula is if(and(d2="mkt",e2="manager"),20000,18000),if(and(d2="mkt",e2="officer"),15000,13000)where is worng plz help me.
shukriya

78. Stefani Fischels says:

Hello,
I need help with using the IF function. In the example that my instructor uses, it looks like this:

=IF([@[Billable Hours]]>4, "highly billable", "low billable"}

when he clicks on cell D2 (the title is called billable hours)it actually shows up like this, however when i click on the cell D2 its just coming up as D2 not as ([@[Billable Hours]]

If i leave the formula as saying D2 verses [@[Billable Hours]] i notice it doesnt come out right. How to I get the formula to recognize the title in the spreadsheet?

thank you

79. Maz says:

I need help using the IF function, to work out the score range
High =9-13
Moderate =5-8
Low 0-4

Thank you

• Malc says:

=IF(AND(A1>=9,A1=5,A1<=8),"Moderate",IF(A1<=4,"Low","")))

80. Malc says:

This helped. I wanted to give scores from result based on percentage. Thanks

=IF(AND(J5>=100%,J5=121%,J5=141%,J5200%,1,IF(J5<100%,5,"")))))

81. Grace says:

82. Syed Suhaib Zia says:

Hi
I want to match a column in a table or array by using vlookup formula it give me just one column from the table can i match the column and pick the whole table with help of vlookup if yes then please give me some hints
Brief Explanation:
For example
=VLOOKUP(C17,A3:H7,4,FALSE)
the desire column index is "4" it will take just column 4 what if it take the whole table not just a column?? can i do this please help me

Thank you.

83. ANET says:

E4 shows years experience.
I need H4 to show the percentage equivalent from the table below.

10-15 years = 1.500
15-20 years = 2.250
20-25 years = 3.250
25-99 years = 4.500

I so appreciate any assistance.

84. Anam says:

i am trying to develop a syntax that will allow me to classify patients as having metabolic syndrome. The syndrome satisfies the condition meeting any 3 out of 5 criteria. My data is in excel. truly appreciate for any help.
Thanks, Anam

• It's almost impossible to give you a definite formula without any details on the data you use in your table. It would be helpful to know whether the results are numbers or text and which criteria they should meet… What I can do is to only show you a super simplified example of what your data and function may look like
=IF(COUNTIFS(B2:F2,"yes")>=3,"metabolic syndrome","")
COUNTIF counts the number of met criteria, and if it's more than or equal to 3, it returns the result with a diagnosis, otherwise the cell remains empty.

Sir
How can import a particular Column from PDF to Excel file how ever i have many of PDF files and I want Import a particular Column (which contains some value) from PDF to Excel Please Help for this formula or Micro Please and Please

86. Dinesh kumar says:

Using excel formulae, find out the following
1. How many have neither registered nor completed any of the 3 courses?
2. How many have registered or trained in atleast 2 of the 3 courses?
3. How many have not been trained in any of the 3 yet?
R - Registered for training (training not done yet)
T - Trained
Blank - Neither

Name SQL SAS Excel
Prakash R T
Rahul R
Priya R T
Amit T
.
.
.
...
.
.
..
.
.

87. Arun says:

Here is the data:

Total surveys 20 and the csat is 18 where I'm standing at 90%. Iam checking for the formula where, how many csat's required to hit 95%

88. Santosh Kumar says:

=IF(AND(AB67>100%,AC670),1%,0%)

Plz explain this formula

AB67 contains value 45000
AC67 contains 30000-150000

89. Muppet101 says:

Can you get an IF AND OR BUT IF formula

Example: =IF((AND(OR(D2="Peach",D2="Orange",D2="Apple"),OR(H2="Standard",H2="Close-Cut")),1,-1) I require a but if Cell D3=1 then the value id 1

• Hello,

What do you mean by "value id 1"? Where do you want to see this id number? Do you want to include D3 in this condition as well? Please specify so we could advise you with your formula.

90. Roshan says:

I want to solve simple 3 equation.
Ex:- 6inch> tall, 4 to 6 medium, 4inch<Short.

• Hello, Roshan,

try this formula:
=IF(A1>6, "Tall", IF(AND(A1>=4,A1<=6), "Medium", IF(A1<4, "Short", "")))

91. Peshiya_Boy says:

Hi Roshan,

Try This:-
=IF(A1<4,"short",IF(A1=6,"Long")))

92. Peshiya_Boy says:

This One:-

=IF(A1<4,"short",IF(A1=6,"Long")))

93. Zhannat says:

94. Anurag Patel says:

hi,
I want to solve below conditions.

9:00am to 10:00am ="A"
10:00am to 11:00am = "B"
11:00am to 12:00pm = "C"

95. Dave says:

Hi,

I have 2 formulas that both work however I need to have them work together as an "OR" situation and can't come up with the right formula for that. Can you help?

=IF(AND(D45=1,Q45="N"),(I45*0.0925))
=IF(AND(D45=1,Q45="Y"),P45)

• Amar G says:

HI Dave

do just like this

=IF(AND(D45=1,Q45="N"),(I45*0.0925),IF(AND(D45=1,Q45="Y"),P45),0)

96. Kay says:

Hi there, I am not sure how to phrase my question, so please be patient with me!
I have a spreadsheet, which identifies results (based on selections) and returns scores

i.e. E3 is the result column
C3 and D3 are Player 1 and 2 respectfully.

Now, Player 1 and player 2 choose a number between 1-6. the result (from the game) goes in column E and the formula in F returns "Player 1" or "Player 2" if their answer matches the result...I hope you follow! this all works great - until, Player 1 and player 2 both choose 3!!!
This gets even more complicated if 3 is the winning result!
I need a win, lose, draw, no result type scenario but NOTHING I have tried will resolve the issue :( PLEASE HELP!

97. Imran says:

Hi Svetlana Cheusheva!

98. SOM CHINWAR says:

hi
i have one err
exp
sheet1 cell A2 in a name SOM

sheet2 cell b2 in formula given =sheet1!A2

but he shown name a repeat in cell

exp - SOM-SOM-SOM

PLS HELP ME

99. Tony says:

Hi, In my spreadsheet I have the following information:
A2="Gold", B2="Silver" C2="Bronze, D2=a blank field. I'm trying to create a single formula in column E2 which captures the following:
If range A2 to D2 contains "Gold" then E2="Gold";
If range A2 to D2 does not contain "Gold" but it contains "Silver" then E2="Silver";
If range A2 to D2 does not contain "Gold" or "Silver" but contains "Bronze" then E2="Bronze";
If range A2 to D2 fields are all blank fields then E3="unrated"

Hope you can please assist. Thank you.

• vikash jha says:

=IF(OR(A2="Gold",B2="Gold",C2="Gold",D2="Gold"),"Gold",IF(OR(A2"Gold",B2"Gold",C2"Gold",D2"Gold")*OR(A2="Silver",B2="Silver",C2="Silver",D2="Silver"),"Silver",IF(OR(A2"Gold",B2"Gold",C2"Gold",D2"Gold")*OR(A2"Silver",B2"Silver",C2"Silver",D2"Silver")*OR(A2="Bronze",B2="Bronze",C2="Bronze",D2="Bronze"),"Bronze","Unrated")))

100. Mahesh says:

Hello all,

Distance 50 kg 100 kg 250 kg 500 kg 1000 kg
Upto 25 NIL NIL NIL NIL NIL
26-100 km 650 750 1000 1150 1500
101-150 km 1000 1250 1500 1750 2000
151-200 km 1250 1500 1750 2000 2250
201-300 km 1500 1750 2000 2500 3000

My query is
If distance 100 km & wg 50 so charge 650/-. if distance 100 km & wg 100 kg charging cost 750/-. if distance 100 km & wg 250 kg charging cost 1000/-. same applicable for 500 kg & 1000 kg.
same for 150 to 300 km & kg 50 to 1000 kg.
my query is condition one is same but condition 2 change so which formula I can use ?

• Amar G says:

hi Mahesh,

write down your distance on a row on top and quantity in left side colomn such as
100 150 200 250 300 (start this from "B" cell)

50
100
150
200
250

Then apply this in cells and change the value of only 100 in formula with your distance value.

=IF(OR(B18=100,A19=50),650,IF(OR(B18=100,A19=100),750,IF(OR(B18=100,A19=150),850,IF(OR(B18=100,A19=200),900,1000))))

Ultimate Suite 2018.5 for Excel