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. Excel logical functions such as AND, OR, XOR and NOT will help you in doing this. Continue reading
Comments page 13. Total comments: 567
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
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.
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.
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
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.
In Col I Write aIn Col I Write a formula to give rank to student based on below table (Without using IF Condition)
Marks Grades
=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)
Marks Grades
=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
plz reply urgent
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)
Marks Grades
=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)
Marks Grades
=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
plz reply urgent
Use V lookup function
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)
Marks Grades
=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
hi trying formula A=B,C=D,E=F,G=H THEN 10000 PLZ HELP
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 ")
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
I don't really understand this!!???
Please help me out first. :-)
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
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
Hi what function can I use to get this result?
If A6 is less than 5 add 1, if not return 5?
Hi,
There are two answer from two different cells in a row but i want either of one answer.
Please reply me quickly
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 .......
Thanks so muck my problem is solved
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:
For P7 : =IF(AND($M7="Sell",$M7="Buy"),SUM($N7-$O7),SUM($O7-$N7))
For P8 : =IF(AND($M8="Sell",$M8="Buy"),SUM($N8-$O8),SUM($O8-$N8))
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".
Cannot understand why this is happening and how to correct this.Am I missing something. Please help.
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, ""))
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)
How can i combine these formulas MAX(0,180-D5) and MIN(40,180-D5)
For scenario 1, I have: =IF(AND(AV2="Y",AD235),AI2," ")
Please help me determine the formula for scenario 3, for AD2 greater than 25 but less than or equal to 35.
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:
=IF(AND(AV2="Y", AD2>25, AD2<=35), AI2,"")
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," ")
Please help me determine the formula for scenario 3.
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.
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," ")
Please help me determine the formula for scenario 3.
Thanks
Hi Amanda,
I want to ask you,How we use AND or OR logical function with IF condition?
Please Help with atleast two example.
Hello!
Check out the following examples:
Excel IF function with multiple AND/OR conditions
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%))))))))))))))))))
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%
Please help me to insert appropriate function in the cell, to result the desired value
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.
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, "")))
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.
I done it from help by your above comments. Thank You.
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.
Hi Jolly,
Here you go:
=IF(A3>B2, A3-B2, B2-A3)
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
=IF(AND(E12>0,"Closed","Open"), OR (B12>0,"Open".""))
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.
=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.
Are these two different formulas you need or one?
=if(e12=0," ","Open")
=if(b12=0,"Open","Closed")
Your 2nd formula has a period instead of a comma.
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.
Hi Svetlana,
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). Example below:
Table A Table B
Number Amount Text Number Amount Value (Text)
1026 550 Sun 1026 123 NA
1026 123 Moon 1026 550 NA
Hope i made my self clear. Please email me the solution.
Thanks
Sushanta Lenka
Hi there,
Just wondering whether you could help me on below?
I have column D (ON RFQ) and column v (Order Number),
my requirement is:-
1)if D2 is blank and V2 contains order number, then return value as "PO"
2)if D2 is * and V2 contains order number, then return value as "PO"
3)if D2 is * and V2 is blank, then return value as "ON RFQ"
4)if D2 is blank and V2 is also blank, then return vlaue as "OPEN REQ"
Any help on above will be greatly appreciated.
I would like to create formula in Excel i.e.
if i typed "B" in cell 1 then value should be add in bottom 0.050 and in the same cell if typed V then value should add in bottom 0.035
dear sir i want to get a formula in which i want to get the due date formula from the bill
like:
bill date is 10.06.2016
due date is after 40 day from the bill date
so tell me formula like that
as due date-40 DAY = DUE DATE LIKE THAT 20.07.2016
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.
Hello
I have a problem in a scenario of 1q1
if cell value in minus than "Extra" and if 0 than "OK" and greater than 0 than Short
suggest some formula
Excel A1 cell 6Doz+ 6pics so how formula use and create answer 78 pls help
Hi
I have a cell(W28) which has only 2 drop down "Yes" and "No". But this affects my formula in cell (W42).
If (W28="Yes") - I have to use formula as (A+B^0.5)/C
If (W28 ="No")- I have to use formula as (A+(B/2)^0.25)/C
I am not able to use the the formula based on my selection in Cell (W28).It gives only one value based on the formula i put
Here is my formula -
=IF(W38="Yes",(((W39*(W31/2)^0.5)/W40)^1.33),(((W39*W31^0.5)/W40)^1.33))
Can anyone please help
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
Hello Graham,
You can use the following formulas.
For B5:=IF(A1="W", 1, IF(A1="L", 0, ""))
For C5:=IF(A2="W", 1, IF(A2="L", 0, ""))
hi
I Have 1 Sheet How i Can See On Sheet All negative value In One Sheet
Example
Name Total Amount
Ram 60
Sita -50
Hari 150
Gopal -140
Like That
i Need all Navigate Amount
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..
IF(C2=ic/Freelancer, "5", IF(C2=Temp/Contractor, "1"))
Thank you
I have 3 columns
A: has member ID
B: Date
C: Trainer ID
I want to highlight members who saw different trainers on the same day..
Will this work?
=IF(XOR(AND((A3=A2),(B3=B2)),(C3=C2)),0,1)
Appreciate your help
Hi Asiya,
Try the following formula:
=IF(COUNTIFS(A:A,A1,B:B,B1,C:C,C1)>1, IF(COUNTIFS(A:A,A1,B:B,B1) - COUNTIFS(A:A,A1,B:B,B1,C:C,C1) = 0,"not different", "different"),IF(COUNTIFS(A:A,A1,B:B,B1)>1,"different","not different"))
I have a cell (A1) that can contain the values of 6, 12 or 18.
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 18.
I've tried various combinations using IF and OR but can't arrive at the desired conclusion. Can you help?
Hello Uncle Dave,
Try the following formula:
=IF(A1=6, 0, A1)