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 logical tests to perform more complex calculations and more powerful data analysis.

Microsoft Excel provides 4 logical functions to work with the logical values. 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.

In additions to the four logical functions outlined above, Microsoft Excel provides 3 conditional functions - IF , IFERROR and IFNA. 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. 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. 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.

For example, you can nest any of the AND functions above inside the IF function and get a result similar to this:. Excel IF function with multiple AND conditions. 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:. 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. 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. 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 Nov, B8 is Dec and C8 is 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. So, if you replace AND in the logical test of the IF function with MEDIAN, the formula will go like:.

And you will get the following results: As you see, the MEDIAN function works perfectly for numbers and dates, but returns the NUM! Alas, no one is perfect: 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 - Where logical is something you want to test that can be either TRUE or FALSE. The first logical is required, additional conditions up to 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.

As well as Excel AND function, OR is widely used to expand the usefulness of other Excel functions that perform logical tests, e. Here are just a couple of examples:. 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. There can be infinite variations of such formulas that boil down to the following basic patterns:. For example, if you wanted to know what consignments of bananas and oranges are sold out, i.

The rule with the above OR formula highlights rows that contain an empty cell either in column B or C, or in both. In Excel , 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.

EXCEL XOR multiple bits - Stack Overflow

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 with help. The first logical statement Logical 1 is required, additional logical values are optional. You can test up to conditions in one formula, and these can be logical values, arrays, or references that evaluate to either TRUE or FALSE.

In the simplest version of the XOR formula that contains just 2 logical statements, the Excel XOR function returns TRUE if either argument evaluates to TRUE. If both arguments are TRUE or neither is TRUE, XOR returns FALSE. This might be easier to understand from the formula examples:. 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 3 rd game based on the following conditions:.

And if you nest this XOR function into the logical test of the IF formula, you will get even more sensible results:. 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:. 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:.

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

Another common use of the NOT function in Excel is to reverse the behavior of some other function. The NOT function can reverse this result to FALSE: 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.

If C2 is blank, the text "No bonus: 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.

I'm a bit stuck on finding a formula that will suit my needs. My spreadsheet has column A: I need a formula that will look at column A, B, and C and if they match any other row in column A, B, and C I need it to select the most recent date. Also, I can certainly send you a copy of the workbook if that will help you understand, better, what I am looking for.

You can simply sort your table by column D most recent dates will be at the top , and then use the standard Excel Remove Duplicates tool to find duplicates in columns A, B, C and delete them. I have a spreadsheet for commissions. I am trying to take the information and put it into another sheet.

I need a formula that will take the value of possibly 3 cells. I would need it to the value of 2 cells if a amount in there. If not I need it to take the main value! I've come close but it does a False statement. Need it to the value of that cell over. I am trying to put a 3 way formula into a box. I want Colum A1 to say Open if text is missing, closed if text is present, or suspended is there is NA in the cell. Like a 3 way condition through the cell.

Yes, you can do this using the nested IF formula, like this: Sorry, I cannot understand what you are trying to achieve, maybe because the formals have been distorted by our blog engine. Can you describe the logic in words, please?

I am an aspiring Financial Analyst, do you have a link to formula I can use. Basically, popular formula that deal with logic, or shortcuts. Please email me the info.

I want a formula that can look in a cell if there is a value in it lets say A1 then dont change the vale c1 d1 e1 f1 g1 otherwise delete value in c1 d1 e1 f1 g1. Regrettably, this task cannot be solved by using Excel formulas because a formula can return a value only into a cell where it is entered. You need a macro for this. Sorry, I cannot help with this, VBA code is outside of my skills. You can try posting this question on targeted Excel forums like mrexcel. I have one time in cell A1 I want to know if the A1 falls within plus or minus 4 hours of time in B1.

Hey, i have to make a report that in G8 that i have to pay,i have balance in H8 and the I8 is the formula that showing that this amount is short or ok. I need that when its an empty cell, the formula cell gives blank message.

I need assistance please. Hi, I need a formula to work out commission based on business name Many thanks in advance. I want to find the place of either one of two station names from a cell. In other words, A1 contains some text matter, and I want to find the place of "New Delhi" or "Mumbai" whichever exists in it.

Is there some formula to do this?

Sorry Gopal, your conditions are not clear. Could you try to explain diferently? Hi I need a help. I am making a format in excel for some company work where I want to track vehicle data. In example if on sheet 2 I copy whole report with defects from w,X,y,z lines and u want to make some format that when I copy data here on sheet 2 directly it will show how many defects from w,X,y,z.

Not in numbers, I want them to copy in words. Like sumifs does for counting. I am making some test reports ,I have given three grades for each parameter like deficient, optimum, excess.

You can use nested IF's like in the following example: If column A is not blank, return B for breakfast If column B is not blank, return L for lunch If column C is not blank, return A for Afternoon snack If column D is not blank, return S for supper. If multiple columns are not blank, I would need them to appear in column E with spaces between B L A S Thank you. I want to add formula if A2 is 1. Your formula is correct except for an extra closing parenthesis at the end: I have three conditions with five possible answers and cannot get the formula to work properly.

I don't know much about entering logical formulas in Excel. Please help me if you could give me formula for my situation. Test results "Pass" if it is in this range otherwise "Fail". I think you can use a formula similar to the one below, where A1 is the sample value and B1 is the value you are comparing to the sample:. TRIP Manaul Trip AIL Hi I am having trouble creating a formula to reflect: If preferred is True F4 , or the budget C4 amount is greater than the quote D4 , then display the quote D4 less the discount H4 , otherwise display nothing.

Here is th correct syntax if F4 is the Boolean value TRUE: Can you help me on table below: The correct syntax of a nested IF formula is as follows:. I'm looking for a formula that will count.

EG if both cells a1 and b1 are negative s, then enter cell b1 in cell c1. I am trying to determine how i can take the value of two different cells and return the difference in another cell.

I realize that if I subtract one cell from the other it will give me the difference. However this will not work correctly if the first cell's value is less then the 2nd cells value. I need the third cell to show either a positive or negative number based on the values entered into the first two cells.

I am not sure I understand the problem. For example, if A1 is 1 and B1 is 3, and you put the formula A1-B1 in C1, the answer will be Hello I am trying to create a bonus tool where 3 cells are T Target , A Actual and B Bonus The bonus will be calculated by achievement over target but due to the variable nature I need to input the numbers on a spreadsheet.

The logic is essentially as follows I can't figure out which formula to use for the following scenario. Really hoping you can help me.

So during the football game, the announcer can type in a school code, like R and a jersey number, like 2 and the name of the player will pop up.

I have a column with two forced choices i. There are ten rows in the column. I am trying to create a formula that returns a "Yes" or "No" if the text "individual" is entered in any one of the ten rows in the column. How do I get the formula to recognize all ten cells i. F12 to determine if the value is present in just one of the ten cells?

How can I make this formula work, IF Cell "O13 is Blank and the Data is available with Cell S13 or W Column A has values of I,II,III and IV Column B has values of A,B,C,D,E,F Column C would be either High, Medium or Low based on the IA,IB,IC, IIA, IIB etc. To further complicate things I also need High to fill the cell Red background, Medium to fill the cell Orange background and Low to fill the cell Green background.

I want a IF function to convert them all to numbers without letter M million. For example i want 1. I have one question to be answered. I'm a teacher in a high school and a home room teacher in particular for 50 students.

They are learning 10 subjects in a year. Thanks for your smart Excel Tutorials that enables me to manage the students mark using Excel like their total mark, average, Rank and their mark analyses.

Failed by any four or more subjects. Thanks in advance Maru from Ethiopia. I need a Function that is yes and no column are multiple as below, how use function.. HI i need help. IF A3 is greater than 1 but less than , " No repetition of duty till each out of 40 personnel performs duty. No two personnel from same section within a week. My question is related to And function.

Logical functions in Excel: AND, OR, XOR and NOT

I have the below Duration in Dates Phase 1 Start: Phase 1 End Phase 2 Start: October 5 October 4: I am comparing each October date if it falls in Phase 1 then return "X" Value and if falls in Phase 2 return "Y" Value.. However if you see OCT 3 and 4th falls in both Phases how to generate "Z" Value for those dates. I have a column of cells in which "pass" or "fail" is entered.

I have a formula in the last cell D49 set to auto fill, as an overall "fail" if only one of the cells has "fail" in it. This is what I want. However, when the other cells are blank, it auto fills to "fail", as no "pass" is present. I would like D49 to be blank if there is no text in the Cells D45 to D What can I add to the formula to keep it blank if cells D45 to D48 are blank?

Do you want D49 to be blank when all the cells D45 to D48 are blank or when at least one cell is blank? Alternatively, you can use COUNTIF to make the formula more compact: I might be going past what Excel is capable of doing, but I'm trying to find a way for a function in excel to filter information from a cell ie 1.

Essentially I'm trying to find a way to have excel differentiate, or at the very least stop when it gets to the ". I filter through large sums of data a day, so I'm trying to find a way to simplify this instead of typing these things out hundreds of time. Any advice you can give would be appreciated. I need assistance calculating the following information in Excel , if possible.

If you can simplify, please do. In Progress Last Worked: Resolution Provided Last Worked: Completed -- Thank you, in advance! I have a 3 in one if statement to put value to certain conditions: How do I fix this? This formula isn't true since a value cannot be equal to 3 and 5. We suppose that you need a formula like this: I want to choose from a drop down list for the following 1 choose either ATM or CDM 2 choose either Shift1 or Shift2 3 choose the units available example , , etc 4 choosing from 1 and 2 and 3 will get the result.

You can read how to create a dropdown for a worksheet in Making a cascading dependent Excel drop down list. For us to be able to assist you better, please describe the condition for the expected result.

Please try this formula: I need a formula whereby, when the value ranges from Pls guide me solve the problem. Pls give the one formula for both tables. I want copy particular cell formula in google sheet and I want to paste special to other 3 column which macro program I have to use..

HOW I USE FUNCTION IN G2 CELL IN ABOUT CONSTANT B1 WHEN F1 IS GREATER THAN EQUAL TO E1 OTHER WISE MINUS E1 FROM B1 WHEN E1 IS GREATER THAN F1. If my understanding of your task is correct, you can use the following formula: Help in Excel formula that is If sheet 1 cell C2 are equal or greater than 21 then copy data sheet 1 cell A2 to C2 and paste in Sheet 2 A2 to C2 if not equal 21 then do not copy data. Every investment has end date.

Sir How can import a particular Colum from PDF to Excel file how ever i have many of PDF files and I want Import a particular Colum which contains some value from PDF to Excel Please Help for this formulla or Micro Please and Please. I have been able to get the first 3 conditions correct using if and and if not however I can't get the 4th condition to work.

Is there a formula for 2 not conditions? Here's the correct question. I have been trying to crack this for quite a while now. Your help will be much appreciated. Hello, What is this? Volume Product Type A B C A B. All the product type columns are selected via drop down list using data validation option in excel. My problem is that i need to sum up each product type separately. So can you help me out in this..

I need a sum, that checks different values, and sums them if all of them are true. Only the last part of the formula should check, if either one of the two conditions are true.

Currently I am working with Sumifs, that works pretty good, except for the last part, where it should only sum, if one of the two values is true. This is the formula:. I have a cell A1 that can contain the values of 6, 12 or If A1 is 6, I want cell B2 to show 0; if A1 is 12, I want cell B2 to show 12; if A1 is 18, I want cell B2 to show I've tried various combinations using IF and OR but can't arrive at the desired conclusion. I want to highlight members who saw different trainers on the same day..

B,B1 - COUNTIFS A: Hello, i am trying to create a formula similar to this but the result i am getting is " NAME?.. Can you please help me out.. Example Name Total Amount Ram 60 Sita Hari Gopal Like That i Need all Navigate Amount. Hi, I'm trying to find a way of achieving the following, if A1 has a text entry of "W" then add 1 to cell B5, if A2 has a text entry of "L" then add 0 to C5.

These have to be interchangeable so if A1 has a "L" entry enter 0 to B5, and if A2 is "W" add 1 to C5. Hi I have a cell W28 which has only 2 drop down "Yes" and "No". But this affects my formula in cell W I am not able to use the the formula based on my selection in Cell W I have a problem regarding a calculation of commission total in a month with a condition that if actual sales is greater than the weekly target sales in all four weeks in a month plus monthly sales is also greater than the target monthly sales, then commission will be paid as a certain percent of the monthly sales, if in any week actual sale is greater than target weekly sale but not in all 4 weeks then commission will be paid as a certain percent of weekly sales, it will be not paid as a certain percent of monthly sales, even though monthly sale is greater than target monthly sale.

Otherwise,if actual sale is not greater than target sale in any week, no commission will be paid at the end of the month. I would like to create formula in Excel i.

Just wondering whether you could help me on below? I have column D ON RFQ and column v Order Number , my requirement is: Hope you're doing well. I have an error in "IF AND " function. Where i am putting three condition to get the value but i got an NA reason being the first condition have the same number multiple times with the different amount second condition. Table A Table B Number Amount Text Number Amount Value Text Sun NA Moon NA.

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.

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. 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? Any help would be appreciate it. I am able to write both formulas separately, but unable to tie together. PLEASE HOW DO I CREATE THIS FORMULAR. I want to use If formula. Any idea for do this formula.

Here's one for you: I've got a column containing the following values: 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'.

I have to make 3 calculations based on two factors: For scenario 1, I have: 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. Thanks - and not sure why this isn't posting correctly. Our blog engine often mangles " " characters in formulas, sorry for this.

You can specify all 3 conditions in the AND statement, like this:. I WANT TO CALCULATE HOUSE RENT BY FORMULA GIVEN, BASIC SALARY: I need help to write an if statement. 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.

My Problem is as below: I have input above formula in cell P7 and P8. Cannot understand why this is happening and how to correct this. Am I missing something. 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 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. Seventha, I am writing below mentioned formula i need a specific formula for counting for odd numbers i. Just try like this if you need through number format keep numbers in another sheet like this 1,2,3, In Col I Write a formula to give rank to student based on below table Without using IF Condition. Marks Grades 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 In Col I Write a formula to give rank to student based on below table Without using IF Condition. In Col I Write aIn Col I Write a formula to give rank to student based on below table Without using IF Condition.

Hello I am working tracker that tracks of observations. 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. 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.

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. A Compy B veh C in D out E Trip Time XYZ XD M XYZ XD C I just noticed an error in my initial submission It should be: A B C D E 1 CAST PER ADMISSION 2 Gen 50 Yes 3 SC 45 Yes 4 Obc 45 Yes. 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.

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,. Would you please help me in this thank you. 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. I am trying to do a IF THEN formula over multiple columns where the column is marked with an "x" or is blank. What would be the best formula to use. G1 is a percentage entry. I can do one, but don't know how to include all 3 columns.

So far I have: 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. 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". I need help for "if" formula: 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. 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, 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.

How do I perform a XOR calucation of two binary numbers in excel - Stack Overflow

The company takes this figure and pays hours as normal time and the remainder as overtime, i. So this hours is like a threshold value - if But here is the thing that also throws problems my way, if the amount of hours worked is below I wish I could upload my spreadsheet. I have a student that is doing research and is looking for a formula with the following conditions:. 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.

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 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". Hi, want to check error in the formula: 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 , , , , Our funder is asking us to show a score of 1 in appropriate catorgary cell corresponding to age.

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 Those who are meeting the criteria of coming before Those who are late by Below is the biometric record from this i have to prepare attendance of more then employee which is too tedious.

Help me out i will be greatful. Date InTime OutTime Shift S. OutTime Jan NS 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.

If the First Column Contains green or red value the second column should return true otherwise false. Column A includes customers who order in , and column B customers who ordered in I want to have customers who ordered in but not in Is there a formula? I want to find out a solution for a simple problem. 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.

I am trying to get get a formula to work for the below can anyone help. 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.

Hello, I need help with using the IF function. In the example that my instructor uses, it looks like this:. 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? 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: 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??

E4 shows years experience. I need H4 to show the percentage equivalent from the table below. The syndrome satisfies the condition meeting any 3 out of 5 criteria.

My data is in excel. It's almost impossible to give you a definite formula without any details on the data you use in your table. 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.

Using excel formulae, find out the following 1. How many have neither registered nor completed any of the 3 courses?

How many have registered or trained in atleast 2 of the 3 courses? 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. I'm sorry but your task is not entirely clear. 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.

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. 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. Now, Player 1 and player 2 choose a number between I hope you follow! 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 me with complete list of excel logical function.

This comprehensive set of time-saving tools will help you accomplish any task impeccably without errors or delays. These 8 tools will boost your inbox productivity and simplify your emailing routine. Using logical functions in Excel: AND, OR, XOR and NOT by Svetlana Cheusheva Excel , Excel functions , Excel tips Comments.

Excel logical functions - overview Excel AND function OR function in Excel Excel XOR function NOT function in Excel Excel logical functions - overview Microsoft Excel provides 4 logical functions to work with the logical values. Function Description Formula Example Formula Description AND Returns TRUE if all of the arguments evaluate to TRUE. OR Returns TRUE if any argument evaluates to TRUE. If neither of the conditions it met, the formula returns FALSE.

XOR Returns a logical Exclusive Or of all arguments. 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. If the argument is FALSE, then TRUE is returned and vice versa. Excel logical functions - facts and figures 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. 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! 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 will return TRUE if none of the cells contains 0, FALSE otherwise. A logical function returns the VALUE!

A logical function returns the NAME? For example, the XOR function can be used in Excel only. In Excel , and , you can include up to arguments in a logical function, provided that the total length of the formula does not exceed 8, characters.

In Excel and lower, you can supply up to 30 arguments and the total length of your formula shall not exceed 1, characters. Using the AND function in Excel The AND function is the most popular member of the logic functions family. AND, OR, XOR and NOT" kamal says: December 22, at 8: January 20, at 5: Hi, Svetlana, I'm a bit stuck on finding a formula that will suit my needs.

Thank you in advance for your help. January 21, at 3: Hi Amanda, You can simply sort your table by column D most recent dates will be at the top , and then use the standard Excel Remove Duplicates tool to find duplicates in columns A, B, C and delete them.

January 31, at 6: February 9, at February 15, at 1: February 27, at Hi Sinan, I believe this is what you are looking for: February 16, at 3: February 27, at 4: March 23, at 2: Sorry some of my text was missing on my last post. July 17, at 1: Hi Svetlana, I am an aspiring Financial Analyst, do you have a link to formula I can use. March 25, at 4: March 26, at 2: Hi Nadeem, Regrettably, this task cannot be solved by using Excel formulas because a formula can return a value only into a cell where it is entered.

March 26, at 3: March 27, at April 15, at 5: April 24, at 6: Hi Gaurav, Try this one: October 30, at 5: April 27, at 1: May 4, at 1: Hi Farrukh, You can add another logical test that checks if H9 is not blank: May 3, at May 6, at 9: May 7, at 5: May 7, at 7: May 8, at 1: Hi Ben, Here you go: May 9, at May 10, at 1: EMMANUEL BOAKYE DANQUAH says: May 10, at 2: May 30, at 4: Hi EMMANUEL, You can use nested IF functions like in the following example: March 6, at 7: May 30, at 7: June 1, at 3: The cell selection is number then Accounting.

In the upper bar you only see the numeric value. June 3, at 5: June 4, at 7: Hi I Need Help I am making some test reports ,I have given three grades for each parameter like deficient, optimum, excess. June 4, at Hi Prasad, You can use nested IF's like in the following example: June 6, at 7: June 11, at 9: Here is the formula I need to enter in column E.

Can this be done? If column A is not blank, return B for breakfast If column B is not blank, return L for lunch If column C is not blank, return A for Afternoon snack If column D is not blank, return S for supper If multiple columns are not blank, I would need them to appear in column E with spaces between B L A S Thank you.

June 16, at 9: June 16, at Hi Deepa, Your formula is correct except for an extra closing parenthesis at the end: June 17, at June 24, at 5: June 25, at Hi Abdul, I think you can use a formula similar to the one below, where A1 is the sample value and B1 is the value you are comparing to the sample: June 26, at 7: June 27, at 7: June 27, at 1: June 29, at 2: Hi, Wanted to check the next immediate number how can I do.

July 3, at 6: Would really appreciate the help. July 3, at 1: Hi Kelly, Here is th correct syntax if F4 is the Boolean value TRUE: July 20, at 9: July 25, at 9: July 25, at July 27, at Hi Ken, The correct syntax of a nested IF formula is as follows: July 31, at 9: July 31, at Hello KRISHNA, You can use a nested IF formula like this: September 10, at 8: August 4, at 1: August 6, at 9: August 11, at 4: Hi, I'm looking for a formula that will count.

August 12, at Hi Crystal, If s means negative numbers, you can enter the following formula in C1: August 13, at Hi, I am trying to determine how i can take the value of two different cells and return the difference in another cell. August 14, at Hi Ron, I am not sure I understand the problem. If you want the formula to do something different, please clarify. August 15, at 2: August 19, at 5: August 19, at Hello PANKAJ, If you want to perform all the calculations with a single formula, here you go: September 2, at 6: August 19, at 3: August 30, at 5: August 31, at September 1, at 8: September 11, at 2: Hi, I have a spreadsheet that has three columns of lines Column A has values of I,II,III and IV Column B has values of A,B,C,D,E,F Column C would be either High, Medium or Low based on the IA,IB,IC, IIA, IIB etc What is the calculation for this.

Its driving me mad. September 24, at 6: September 25, at October 1, at 8: Hi, I need a Function that is yes and no column are multiple as below, how use function.. October 2, at 1: October 2, at Hi Francisco, You can use an AND statement, like this: October 6, at 1: October 7, at 6: October 10 I am comparing each October date if it falls in Phase 1 then return "X" Value and if falls in Phase 2 return "Y" Value..

October 11, at 4: Sir, Thanks for Ablebits. It helped me lot about how to make combined use of IF, OR, AND logical functions. October 13, at 1: Hi I have a column of cells in which "pass" or "fail" is entered. Hi Phil, Do you want D49 to be blank when all the cells D45 to D48 are blank or when at least one cell is blank?

October 13, at 2: Hi Svetlana When all the cells are blank. Hi Phil , Then you can add one more AND function that checks for blank cells: October 18, at October 20, at 1: Hey Svetlana, I might be going past what Excel is capable of doing, but I'm trying to find a way for a function in excel to filter information from a cell ie 1.

November 3, at 4: November 9, at 8: Hi I have a 3 in one if statement to put value to certain conditions: January 27, at 1: Hello, Shirleen, This formula isn't true since a value cannot be equal to 3 and 5.

November 15, at 2: Hi, I am lost. Hello, Michelle, You can read how to create a dropdown for a worksheet in Making a cascading dependent Excel drop down list. November 16, at 7: November 16, at November 17, at 9: Can you help me with this? B1 match any A: B then add matching C cells?

January 27, at Hello, TJ, Please try this formula: December 1, at December 18, at 3: Hi, I need a formula whereby, when the value ranges from January 5, at From To Total 1 54? Hello, Prem, For us to be able to assist you better, please give us the result you need in C1 and C2.

January 7, at January 20, at 3: February 5, at 3: You can use the OR function instead of nested If's: Hello LAXMIDHAR, If my understanding of your task is correct, you can use the following formula: Nisar Ahmad Wani says: February 6, at 3: February 8, at Hello Nisar, Enter the following formula in Sheet2 A2, and then drag it rightwards up to cell C2: February 8, at 1: February 9, at 2: February 10, at 4: February 20, at March 12, at 2: Hello Svetlana, I have been trying to crack this for quite a while now.

I look forward to hearing from you soon. March 23, at Perfect Site to learn excel. Hi Svetlana, I need help to solve the following problem. Hope you can help with a formula. March 28, at Hi Henry, Try the following formula: April 7, at 1: Hi Svetlana, Need your help in calculating the following problem Volume Product Type A B C A B All the product type columns are selected via drop down list using data validation option in excel.

May 19, at 1: April 8, at 1: April 21, at 1: April 27, at 8: Hi, working on an excel with a lot of tabs. This is the formula: Thank you very much for your help. Hi Jean-Luc, Try the following formula: May 10, at 7: May 19, at Hello Uncle Dave, Try the following formula: May 18, at 1: I have 3 columns A: Trainer ID I want to highlight members who saw different trainers on the same day.. Hi Asiya, Try the following formula: May 19, at 8: May 24, at 5: May 26, at 3: May 26, at Hello Graham, You can use the following formulas.

May 29, at 3: June 4, at 6: June 10, at KARAN SINGH LOHIYA says: June 14, at June 15, at June 19, at 8: Hi there, Just wondering whether you could help me on below?

June 20, at 3: Hi Svetlana, Hope you're doing well. Table A Table B Number Amount Text Number Amount Value Text Sun NA Moon NA Hope i made my self clear. Please email me the solution. June 20, at 4: June 28, at 4: Struggling with a formula: July 1, at 4: I need to write the following: July 13, at 8: July 21, at 6: July 21, at 3: Hi, Here's one for you: 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?

July 22, at 8: Hi Liz, Try this one: July 22, at 4: July 24, at July 25, at 7: Hi Amanda, I want to ask you,How we use AND or OR logical function with IF condition? Please Help with atleast two example. Check out the following examples: July 27, at 2: You can specify all 3 conditions in the AND statement, like this: August 8, at 9: August 21, at 7: August 22, at 8: Hi Jessica, Here's the formula: August 24, at August 25, at 4: August 28, at 7: Hi, There are two answer from two different cells in a row but i want either of one answer.

Please reply me quickly. September 3, at 5: Hi what function can I use to get this result? If A6 is less than 5 add 1, if not return 5? September 7, at September 14, at 8: 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.

September 17, at I don't really understand this!!??? Please help me out first. September 17, at 4: September 25, at 9: September 30, at February 14, at 7: October 3, at 9: October 5, at 9: October 14, at 1: 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.

October 15, at 5: October 17, at 3: October 19, at Hi Jim, Enter the following formula in cells B1 and C1: October 27, at 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 October 28, at 6: October 29, at 7: November 2, at 9: I need a formula to compare B1 and C1 and take the higher amount and subtract from A1 PUT IN D1.

November 5, at 9: November 18, at 7: November 23, at 1: November 28, at November 28, at 5: November 29, at 3: November 29, at 1: Hi Baz, You can add one more If function that checks for blanks, like this: November 30, at December 1, at 1: December 3, at December 8, at 1: December 13, at Please help in this.

Your inputs are highly appreciated. December 15, at 1: Hi Sevetlana I want to code this formal I. January 2, at Thanks for your valuable time. Regards, Neetesh Shivhare , India. January 3, at 9: January 6, at 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, January 8, at 9: Please help me Nick.

January 11, at 4: Hi there, I have a student that is doing research and is looking for a formula with the following conditions: January 13, at 4: 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" thanks in advance.

January 17, at 9: January 18, at 2: January 20, at 1: 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. January 24, at 4: 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 January 24, at 7: Hello Sevelana there is a formula uses for blan means if isblank A2,"", January 25, at 7: January 27, at 6: