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
- 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! error.
- 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.
- A logical function returns the #VALUE! error if none of the arguments evaluate to logical values.
- 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.
- 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:
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:
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.
For more information about conditional formatting formulas, please see the following articles:
- Excel conditional formatting formulas
- Changing the row color based on a cell's value
- Changing a cell's color based on another cell value
- How to highlight every other row in Excel
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 :
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:
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.
422 Responses to "Using logical functions in Excel: AND, OR, XOR and NOT"
Hi Svetlana
I need a formula for comparison of numbers with text
E.g.
If x is less than or equal to 1600 them yes or if x is not available then "not avl"
But if X is more than 1600 then wow but if x is not avl then not avl
Hi 201, please use below Formula
=IFERROR(IF(VLOOKUP(D1,A1:B13,2,0)>1600,"Wow",IF(VLOOKUP(D1,$A$1:$B$13,2,0)<=1600,"Yes","0")),"Not Avl")
'=IF(+C6=0,"not avl",IF(+C61600,"wow")))
=IF(C18>1600,"Wow",IF(C18>0,"yes",Not Available))
=IF(C18>1600,"Wow",IF(C18>0,"yes","Not Available"))
Hi Please use this.
=IF(OR(C24="",),"Not Available",IF(C24>=1600,"Yes","Wow"))
=IF(OR(B6="",B6<=1),"Not Available",IF(B6<=1600,"Yes","Wow"))
hi
i need a formula for. .
if found 0152 then get 18
&
if found 6305 then get 5
Hi
Thank you for contacting us.
Please try the following formula:
=IF(A1 = 152, 18, IF(A1=6305, 5, " "))
I hope this helps. Please let me know if you have any other questions or difficulties.
Hi Mary ,
can you help me in macro how to record macro ?
Hi Amit,
First you have to save as your Excel sheet to .xlsm. after this step you have to go view ribbon and choose Macro option. There you can find macro recording.
Start recording and work in the same sheet without using mouse, it works perfectly. Because if you use mouse Macro may not write program as per your clicks.
Once you stop recording you just give an non-calculated report which you did earlier and run macro from same macro option.
Regards,
Mohan
Hi,
I need to make a formula to count 3 different columns but counting just one time if they have more than one value for the others 2 columns.
How I can do that??
I try different ways but I don't know do it!
Thanks!!
Hi
I have 9 sheets in the same workbook variously populated with 0 and 1. In a separate sheet I want to fill a cell with "1" if 1 occurs in the same cell in any of the 9 sheets, or "0" if it doesn't occur at all. I have tried using the following:
=IF(OR('Training NetworkB'!C5="1",KnowledgeSharingBtxt!C5="1",'Encouragement NetworkB'!C5="1",'Organisation NetworkB'!C5="1",'Monitoring NetworkB'!C5="1",'Networking NetB'!C5="1",'Labour sharing NetworkB'!C5="1",'Conflict NetworkB'!C5="1",'Phys_Finan Capital NetB'!C5="1"),"1","0")
It returns a 0 in all cells, even if a 1 occurs. Can you help at all?
Many thanks in advance
Hi,
It looks to me like you have a space after all your 1's that are in your speech marks. If the cells on the other spreadsheets just contain a "1" and not a "1 " then the formula won't recognise the 1 because it will be looking for a 1 and a space.
Hi,
It looks to me like you have a space after all your 1's that are in your speech marks. If the cells on the other spreadsheets just contain a "1" and not a "1 " then the formula won't recognise the 1 because it will be looking for a 1 and a space.
=IF(AND(V17="B",V18<="470"),"127","0"),IF(AND(V17="BC",V18<="700"),"112","127"),IF(AND(V17="C",V18<="420"),"0","112")
I AM USING THIS FORMULA BUT THE RESULT IS " #VALUE! "
PLEASE GIVE ME SALUTATION.
Muhammad, Possible Solution
1) To make this easier, separate the formulas and evaluate each one separately. I noticed that there is overlapping logic. If formula #1 is False you get a -0- and if formula #3 is True you get a -0-, and the same thing with 127 and 112 across all 3 formulas. How would you know which values gave you the answer? Also, what if the input values do not match your expected answers, do you evaluate them? And your attempt to nest the IF statements is wrong.
Nesting as below will work but you will still only get 112, 127 or -0-.
=IF(AND(B1="B",B2<="470"),"127",IF(AND(B1="BC",B2<="700"),"112","0"))
Hope this gives you some insight.
Hi,
please solve this question.
If Mr.A has 125 coins then he will get "1 Apple" and if Mr.A has 170 coins then he will get "1 Apple" for 125 coins and on balance 45 coins, he will get "1 Lemon" on every 25 coins.
Please convert the same in to logical formula in excel.
=IF(G14="Mr.A",(IFS(H14=125,"1 Apple",H14=170,"1 Apple, 1 Lemon"))," ")
Hope this will help you
LET CELL A1 have the coins MR. A HAS, THEN copy THE following EXPRESSION
=CONCATENATE("IF MR.A HAS 125 COINS, THE HE WILL GET 1 APPLE, AND IF MR. A HAS"," ",+A1," ","COINS"," ","THEN HE WILL GET"," ",ROUNDDOWN(+A1/125,0)," ","APPLE(S)"," ","AND ON BALANCE"," ",A1-(ROUNDDOWN(+A1/125,0)*125)," ","COINS, HE WILL GET"," ",ROUNDDOWN(+(A1-(+ROUNDDOWN(+A1/125,0)*125))/45,0)," ","LEMON ON EVERY 25 COINS")
Hello, I need help. I need to verify whether my column C's (numeric) information and column B's (description) information are appropriate and accurate for my column A's (code). Thanks in advance.
I have an excel sheet named "ALL".Just trying to apply formula for counting open points related to Business Development.I applied below formula but its not working can you please check & confirm
=COUNT(AND(All!K4:K1000="Business Development",All!N4:N1000="Open"))
=IF(NOT(isblank (M6)), "unaccomplished", if (isblank N6))' unaccomplsihed
can somebody help me?
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
Rajiv
Priya R T
Amit T
Wanted to extend my thanks. This is exactly what I was looking for!
I have data like this
123415678
432128765
001218090
These numbers represents ID numbers If the 5th number is 1 then the ID belongs to a male if its 2 then the ID belongs to a female
Hello ,
Go the next column, and use this formula (=Left(A1,5))
in Column C , put this formula ( =Right(C1,1)
Codes Left Coding Right Coding
123415678 """=LEFT(A2,5) """=RIGHT(B2,1)
432128765 43212 2
301218090 30121 1
214510025 21451 1
301228090 30122 2
301228090 30122 2
301218090 30121 1
Hello ,
Go the next column, and use this formula (=Left(A1,5))
in Column C , put this formula ( =Right(C1,1)
Codes Left Coding Right Coding Sex
123415678 """=LEFT(A2,5) """=RIGHT(B2,1) ''''=IF(C2="2","Female","Male")
432128765 43212 2 Female
301218090 30121 1 Male
214510025 21451 1 Male
301228090 30122 2 Female
301228090 30122 2 Female
301218090 30121 1 Male
hell can you help me. i sen message to facebook and come friend.
IF(AND(F47<=22500000,F4750000000,F47<=100000000),0,E43*0.1))
I AM USING THIS FORMULA BUT THE RESULT IS NOT GIVEN WHERE IS WRONG
PLEASE GIVE ME SALUTATION
IF(AND(F47<=22500000,F4750000000,F47<=100000000),0,E43*0.1))
I AM USING THIS FORMULA BUT THE RESULT IS NOT GIVEN WHERE IS WRONG
PLEASE GIVE ME SALUTATION
some plz help me to use this
400,001 to 500,000 Slab 02 (AZ163-400000)*2%
500,001 to 750,000 Slab 03 2000+(AZ164-500000)*5%
750,001 to 1,400,000 Slab 04 14500+((AZ165-750000)*10%)
1,400,001 to 1,500,000 Slab 05 79500+((AZ166-1400000)*12.5%)
1,500,001 to 1,800,000 Slab 06 92000+((AZ167-1500000)*15%)
1,800,001 to 2,500,000 Slab 07 137000+((AZ168-1800000)*17.5%)
2,500,001 to 3,000,000 Slab 08 259500+((AZ169-2500000)*20%)
3,000,001 to 3,500,000 Slab 09 359500+((AZ170-3000000)*22.5%)
3,500,001 to 4,000,000 Slab 10 472000+((AZ171-3500000)*25%)
4,000,001 to 7,000,000 Slab 11 597000+((AZ172-4000000)*27.5%)
Exceeds from 7,000,000 Slab 12 1422000+((AZ173-7000000)*30%)
hi...can i ask for help..how can i solve this logic delta column:
if stock > max >>> stock - max (the value must be highlighted in red)
if min <=stock >> stock quantity (the value must be highlighted in green)
if stock>> stock-min (the value must be highlighted in yellow)
Hello, jelmer,
For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
Please also don't forget to include the link to this comment into your email.
I'll look into your task and try to help.
Hi,
I need a formula to calculate a variable sales commision. if I sell 8 or less, I get $x. if I sell 9-15 units, ill get $y. with a variability of 5 different pay rates for commission.
Thanks
I need a formula to calculate a variable sales commision. if I sell 8 or less, I get $x. if I sell 9-15 units, ill get $y. with a variability of 5 different pay rates for commission.
Thanks
Hello, Patrick,
Please try the following formula:
=IFS(A1<=8,"$x",A1<=15,"$y",A1<=20,"$z",A1<=25,"$v",A1<=30,"$w")
Hope it will help you.
I have three colms as
A1 (City), B1 (Quantity), C1 (Amount)
If Colm A1 is Karachi, I want then quantity (B1) should multiply with 15 in Amount colm (C1)
If Colm A1 is Lahore, I want then quantity (B1) should multiply with 15 in Amount colm (C1)
Hello, Azam,
Please try the following formula:
=IF(A1="Karachi", B1*15,IF(A1="Lahore", B1*15,""))
Hope it will help you.
Hi, can you help with how do i use 'one of' constraint: like if (A1 is one of ARRAY;Y;N). Haven't found anything that would match and having like 600 lines in the array would be complicated to set OR(A1=value1;A1=value2.....)
Thank you!
Hello,
For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
Please also don't forget to include the link to this comment into your email.
I'll look into your task and try to help.
Hi, i need a formula to calculate a range table of age like 15-34, 35-44, 45-49 and they have to match with 2 different class, with 2 different amounts link to each class, if the amount in column A is <=34 and column B is "2" the amount should be the amount in cell G2 or if Column B is "1" it should be the amount in cell G3. hope you can assist.
Hello,
If I understand your task correctly, you need 2 different formulas for cells G2 and G3:
For cell G2:
=IF(B1=2,IF(AND(A1>=15,A1<=34),"15-34",IF(AND(A1>34,A1<=44),"35-44",IF(AND(A1>44,A1<=49),"45-49",""))),"")
For cell G3:
=IF(B1=1,IF(AND(A1>=15,A1<=34),"15-34",IF(AND(A1>34,A1<=44),"35-44",IF(AND(A1>44,A1<=49),"45-49",""))),"")
Hope this will help you!
Hi,
i'm working on a daily tracker which is updated on a daily basis value "0" or more than 0 . like 1 or 2. in left to right order.
i want something which should indicate me if last 3 days value is "0".
please help me
Hello,
For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
Please also don't forget to include the link to this comment into your email.
I'll look into your task and try to help.
Guy's please help to fixed the formula in below condition. I have applied formula =(IF(O2<10,"<10",IF(O2<20,"10-20 Min",IF(O2<30,"20-30 Min",IF(O2<60,"30-60 Min",IF(O2=120,">120 Min"," "))))))) but result is not proper.
Time Condition
2:11:59 120 Mins
Guy's please help to fixed the formula in below condition. I have applied formula =(IF(O2<10,"<10",IF(O2<20,"10-20 Min",IF(O2<30,"20-30 Min",IF(O2<60,"30-60 Min",IF(O2=120,">120 Min"," "))))))) but result is not proper.
Time Condition
2:11:59 120 Mins
Hello,
If I understand your task correctly, please try the following formula:
=IF((VALUE(O2)*24*60)<10,"<10",IF((VALUE(O2)*24*60)<20,"10-20 Min",IF((VALUE(O2)*24*60)<30,"20-30 Min",IF((VALUE(O2)*24*60)<60,"30-60 Min",IF((VALUE(O2)*24*60)<120,"60-120 Min",IF((VALUE(O2)*24*60)>=120,">120 Min"," "))))))
Hope it will help you.
Hi Frnds
I need help to solve the below condition
=IF(OR((M10+N10+O10)=0),"",(M10+N10)/(M10+N10+O10)*100),IF(OR((M10+N10+O10)=""),"",(M10+N10)/(M10+N10+O10)*100))
If anyone help me to solve this plz
Hi Frnds
I need help to solve the below condition
=IF(OR((M10+N10+O10)=0),"",(M10+N10)/(M10+N10+O10)*100),IF(OR((M10+N10+O10)=""),"",(M10+N10)/(M10+N10+O10)*100))
If anyone help me to solve this plz
Need to implement urgently
Hello,
For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
Please also don't forget to include the link to this comment into your email.
I'll look into your task and try to help.
I want formula for date. I have to find month of increment from his joining date.
Such as if date of joining is up to 15th the date of increment will be same month and if date of joining is after 15 then date of increment will be next month.
Example, Date of joining of any employee is 12/03/2017 then increment month will be March, whereas date of joining is 16/03/2017 then increment month will be April.
I want formula for date.
I have to find out the month of increment from joining date of an employee on excel sheet.
Such as if date of joining is up to 15th the date of increment will be same month and if date of joining is after 15th then date of increment will be next month.
Example, Date of joining of any employee is 12/03/2017 then increment month will be March, whereas date of joining is 16/03/2017 then increment month will be April.
Hello,
If I understand your task correctly, please try the following formula:
=TEXT(IF(DAY(A1)>15,DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)),A1),"mmmm")
Hope this will help.
Please need a formula for the following:
If the following cell (g2) contains any of these: early 5, mid 5, late 5 and the other cell (h2) has a value >=38 then I need cell (I2) to say "YES" but if cell (h2) has a value<=12 then I need cell (I2) to say "NO" otherwise cell (I2) needs to say "NA"
please i need formula for adding and subtracting of date, eg: add 35 days to 23/2/2014 that will give me 30/3/2014
I want a cell to check another cell for the list below and add the appropriate number if it is found. Here is list of items:
TS=2.50
SL=2.50
W SERV=3.00
G SERV=2.50
S SERV=5.00
Please help and thank you
I figured it out:
=IF(O2="TS","2.5", IF(O2="SL","2.5",IF(O2="W SERV","3","?")))
Thanks
Need a formulas to create an A,B & C analysis
A >8000 Irs, B <8000 Irs and C as <1000 Irs.
Thanks
Hi
i am trying to use these logical function OR, AND, NOT in farmula with offset function i dont know how to write this farmula,
i want make a sheet for Patient Origin and want to calculate that patient origin by week number to select week number 1 from the week list and all patient origin of week 1 apear, if you want to look my database/spread sheet let me know i can send that to you..,
Amjad khan
Looking for a formula as follows:
If Cell A1 contains DELETE then return nothing, else return Cell contents of A1 to C1.
And another formula: If Cell C1 contains nothing, do nothing. Else return contents of Cell B1 to D1.
Basically, if this example uses a period to indicate a new cell it should look like this.
Sample 1: Apple.27.Apple.27
Sample 2: DELETE.31. .
Key: (value1.value2.formula1.formula2)
PLS I NEED YOUR HELP, BEEN THINKING ABOUT THIS IN THE PAST 2 MONTHS BUT UNTIL NOW I DONT HAVE ANY SOLUTION TO THINK OF.
RIGHT NOW IM USING 2 WORKBOOKS TO WORK ON THIS. BUT I WANT TO USE ONLY ONW WORKBOOK . THIS WORKBOOK HAS TO BE USE EVERY YEAR WITHOUT CHANGING THE EXCHANGE RATE OF 2017 TO 2018. IF YOU WANTED TO KNOW MORE ABOUT MY WORKBOOK I CAN EMAIL YOU THE TEMPLATE.
How do I use an AND function To return a value other than True or False,For example: If cell A2 is greater than 20 and B2 is greater than 10 it should return Pass not true. How can I do that?
Hello, Omar,
Please try the following formula:
=IF(AND(A2>20,B2>10),"Pass","")
Hope this will help you.
I have already create this sheet ,but I'm creating my own sheet specific total amount.
Exp: motor,Marine,hull(col1)
Exp: Total Taka(col1)
="motor taka"
(how to solve this problem)
I need to nest the following formula so that it goes horizontally across my spreadsheet. I either get FALSE or #VALUE. The numerical value in row 4 is a score dependent on the result content of row 6. Just using the formula below I get the answer 5 which is correct but I need to nest 19 columns worth!
=IF(XOR(G6="Yes",G6="NA"),G4,"")
=IF(AND(D63>=4000,D63<=7000),"1400","2200")
I Have Used This Formula In excel Sheet, But In This If The Valuve Is Less Then 4000, So Then Then the Folmula Value Show Is "0"
Can U Guide What The Formula.
Hello, Farooq,
If I understand your task correctly, you should use the nested IF functions and your formula should look as follows:
=IF(D63>7000,2200,IF(D63>=4000,1400,0))
Hope this is what you need.
How to make a formula for time and minute for example i have to make a table for my employer when they came to work and when they finish work.cell A 7:30 and cell B 15:30 how much time they work.
In Cell A1 I simply have the number 1.
What I need is the following:
In cell B1 I need an if statement that returns the letter D if A1 is less than 5, C if less than 10, B if less than 15 and A if less than 20.
Thanks
I would like to insert a value in E4 if d4 is = to a certain value,but there are 7 possible values:
if d4 = 8.0 then 5
d4 = 8.1 then 7
d4 = 8.2 then 9
d4 = 8.3 then 11
d4 = 8.4 then 13
d4 = 8.5 then 15
Thanks!
Hi,
I am new to excel. I need a formula for following situation:-
A5=886.7, B5=1.5%
A6=900, B6=1.5% , C1=0.0%, C2=0.1%, C3=0.2% , C4=0.3%
Now Condition when,
0<A6<=999, B6=B6+C1
1000<=A6<=1499 , B6= B6+C2
1500<=A6<=1999 , B6= B6+C3
20002000
Thanks for the help!!
I got it
Good job you are doing.
HOW TO MERGE BOTH FORMULA IN A CELL
=IF(E53=12,"BENF-CPI",IF(E53=1,IF(OR(F53="DN12",F53="DB12"),"CPI-NSDL POA",IF(OR(F53="DN22",F53="DB21"),"CPI-CDSL POA"))))
=IF(E53=11,IF(AND(J53="02-12047200-00100398",AG53="02-12047200-00100383"),"IST",IF(J53=AG53,"IST","P2P")))
Create a whats app group for Excel where we can ask any Questions and reply to all that will be great.
hi
i need a formula for. .
if year 2015 then get interest 8%
&
if year 2016 then get interest 7.9% if year 2017 then get interest 7.8%
Your question is unclear, but I'm thinking you should somehow separate the data by year and then apply the interest. Otherwise, how will Excel know what year it is?
Hello, can I ask for help?
I have to count how many times each person went to some places in a certain range of date from different sheet.
For example
(Sheet 1)
PLACES DATE PERSON
Sydney 2018/4/3 A
Perth 2018/3/7 A
(Sheet 2)
DATE RANGE TIMES Of VISIT
2018/3/10~2018/4/5 ???
I have tried use 'COUNTIFS' but I got '0' for the result.
Can you tell me problem that might happen in this case?
Thank you!
Hi All,
I need a formula to do the following;
if it starts with a Letter remove all the dash, add space after the last letter,and add 1st 2 digits from the column next to it
QP_-00091948-00-0 01.3
The data is in 2 columns A and B.
thank you
1 TO 15000 500
15001 TO 100000 1000
100001 TO 250000 2500
250001 TO 1000000 5000
10000001 AND MORE -0.60%
please help me for below I need formula for below things
1 TO 15000 500
15001 TO 100000 1000
100001 TO 250000 2500
250001 TO 1000000 5000
10000001 AND MORE -0.60%
Ramanan:
I think this is what you want.
Where the value you want to check is in cell H16
=IF(H16>1000000,(H16*-0.06),IF(H16>250000,5000,IF(H16>100000,2500,IF(H16>15000,1000,IF(H16>1,500)))))
PLEASE CONVERT THE FOLLOWING FORMULA
IF "A1">TODAY DATE AND EQUAL TO DATE (2018,10,1) THEN "YES" AND IF "A1">TODAY DATE AND > DATE(2018,10,1) THEN "NO" AND IF "A1" < TODAY DATE THEN "EXPIRE".
IMT 23 clause
If IMT 23 'Yes', 50 % depreciation charges on assessed amount, if No 0 % depreciation excel formula & function
Is there some form of if statement that has more than 2 logical parts.
For instance, instead of outputting TRUE or FAlSE, or the equivalent. IT could output any number of different responses.
To copy your demonstration system;
IF([logical1], [logical2], … [logical{n}], …, [Response1], [Response2], Response{n})
Sam:
There are several techniques that work the way you're asking about. Their use depends on the required logic of the situation.
Nested IF statements are one. They look like this:
=IF(Q2,"Paid",IF(S2>=TODAY(),"Not due","Overdue"))
IF AND statements look like this:
=IF((AND(D1="eng", B11="bank1")), IBAN1
IF OR statements look like this:
=IF( OR( A120), "Less than 10 or more than 20")
Then there are COUNTIF, SUMIF, IFS and others most of which are explained here in the various ABLEBITS articles. Just enter one of the above titles in the Search box and begin to learn about IF statements.
I sell a product that has different names ( up to 700) is there a formula that I can use that can count how many times I have sold a product with one name by comparing the sales data against the list of names ?
Maria:
Enter SUMPRODUCT in the search field here on AbleBits. I think you'll find the answer to your question.
Hi all
I need a forumla/macro for the following argument.
The results should be returned in cell C1 by way of a changed cell colour only.
There are a bunch of arguments applied to each cell based on the values given in cells A1 and B1.
If any of the conditions are met in the below table then the resulting colour should be shown in cell C1
Cell A1 AND Cell B1 then
VL VL Green
VL L Green
VL M Green
VL H Amber
VL VH Amber
L VL Green
L L Green
L M Amber
L H Amber
L VH Amber
M VL Amber
M L Amber
M M Amber
M H Red
M VH Red
H VL Amber
H L Red
H M Red
H H Red
H VH Black
VH VL Red
VH L Red
VH M Red
VH H Black
VH VH Black
I'm assuming I'm going to need a macro for this?
Many thanks
Jo:
Before you look over my solution, let me say to you and anyone else seeing it that I know this formula contains about 20 too many IF/AND statements. It should be in a VLOOKUP, INDEX/MATCH or ideally in VBA code. However, the VLOOKUP and INDEX/MATCH solutions are more difficult for me to explain and the VBA is outside the scope of this blog. So, with that said I will caution you that something like this could be very difficult for anyone coming after you to follow and if you need to modify it, will be a pain. Anyway, these were the conditions you outlined so here it is.
=IF(AND(A1="VL",B1="H"),"Amber",IF(AND(A1="VL",B1="VH"),"Amber",IF(AND(A1="M",B1="M"),"Amber",
IF(AND(A1="M",B1="VL"),"Amber",IF(AND(A1="M",B1="L"),"Amber",IF(AND(A1="L",B1="VH"),"Amber",IF(AND(A1="L",B1="H"),"Amber",
IF(AND(A1="L",B1="M"),"Amber",IF(AND(A1="H",B1="VL"),"Amber",IF(AND(A1="H",B1="VH"),"Black",IF(AND(A1="VH",B1="H"),"Black",
IF(AND(A1="VH",B1="VH"),"Black",IF(AND(A1="VL",B1="VL"),"Green",IF(AND(A1="L",B1="VL"),"Green",IF(AND(A1="L",B1="L"),"Green",
IF(AND(A1="VL",B1="L"),"Green",IF(AND(A1="VL",B1="M"),"Green",IF(AND(A1="H",B1="L"),"Red",IF(AND(A1="H",B1="L"),"Red",
IF(AND(A1="H",B1="M"),"Red",IF(AND(A1="H",B1="H"),"Red",IF(AND(A1="M",B1="H"),"Red",IF(AND(A1="M",B1="VH"),"Red",IF(AND(A1="VH",B1="VL"),"Red",
IF(AND(A1="VH",B1="L"),"Red",IF(AND(A1="VH",B1="M"),"Red"))))))))))))))))))))))))))
This will put the color's words in the C cells because I needed something easy to follow during the testing. Also, you can now conditionally format the column using a IF cell text is "Amber" or whatever approach to format the cell's color. Otherwise, the Conditional Formatting formula would be a mess.
As I say, I know there are other ways to accomplish this, but at the end of the day, I think this is the easiest, in this forum.
Hello. how do I write for formula for:
if K70>=33 A, if less than 33 but more than equal 28 B, if less than 28 C
?
thank you
Macaduta:
The logic in your statement needs to be clearer.
It should read like "If K70 is greater than or equal to 30 then A, etc.
What is "A", "B" and or "C". Values held in other cells? Partial cell addresses?
The second condition needs to be clarified. Is this an AND or an OR relation to the first statement?
After Excel checks these conditions what is it supposed to do? What if none of the conditions are true?
macaduta,
what i did understand was
if there is a value >= to 33 is "A"
then if there is a value between 28-32 is "B"
and if there is a value 27),AND(K7032,"A",K70<28,"C")
IFS(AND(AND(K70>27),AND(K7032,"A",K70<28,"C")
I'm looking for the correct and/or statement. When it was just AND statement, it worked. Now that I need to incorporate OR, it's not working.
Current: =IF(AND(A46="Pending", TODAY()>E46), TODAY()-E46,"Awarded")
Need to turn it into something that will give me the following responses.
If A46="Closed", "Closed"
Pretty much A46 will either be Pending, Awarded or Closed.
Pending = Today -A46 (to give me outstanding number of days)
Awarded = Awarded
Closed = Closed
HELP!
Hi :). I have two possible outcomes for my formula - either cell 'J' (if cell E is greater or equal to cell G and less than or equal to cell H) or cell 'K' (if cell E is less than cell G or greater than cell H). here is my formula: =IF(E3>=G3*(AND(E3<=H3)),J3),IF(OR(E3H3))),K3) which returns #VALUE
Please help :(.
Carl:
Text and numbers don't mix very well in Excel formulas, so make sure your numbers are actually formatted as numbers.
If have to add the print the the value in C1.
I need add the student marks for the following condition A means if student Absent in subject.A1=Theory Marks,B1=Practical Marks,C1=Final marks Means A1+B1=C1
case-1
A1=5 B1=10 C1=15
Case-2
A1=5 B1="A" C1=05
Case-2
A1="A" B1=10 C1=10
Case-2
A1="A" B1="A" C1=0
any body please help to write formula for that.
Kindly give me formula for that
Anurag:
If you can simply put a "0" in A or B when student is absent then the formula is very simple. In C1 just enter =A1+B1.
If you must enter an "A" in a cell when student is absent then put this formula in C1: =IFERROR(IF(A1="A",(0+B1),IF(B1="A",(0+A1),(A1+B1))),"Student Was Absent for Both Subjects")
If A1 and B1 both hold "A" the IFERROR returns the message, "Student Was Absent for Both Subjects".
You can change the cell addresses and the message to suit your needs.
Hello, I need some help with formula related to day of the week. If day = Tuesday or Thursday, a formula needs to be incremented by 1. I can get it to return True if the day is Tuesday or Thursday and have that in a column.
I tried the formula below but it increments by 1 no matter what h3 is.
=IF(h3="FALSE",(1+$k$3-j3),($k$3-j3))
I also tried this but it also increments by 1 no matter what h3 is.
=IF(h3="FALSE",($k$3-j3),(1+$k$3-j3))
This might be one of the best excel tutorials I've ever read! Very well written, and the examples really help show how the functions work.
Thank you!
I need help with an "IF" "AND" formula.
I am looking for the logic when I have a test score in box B1 and want to create a rule:
IF B1 > 0.5, but B1 0.6 and smaller than 0.7 make C1 = Level 2
AND IF B1 > 0.7 make C1 = Level 3
IF FALSE = Unfit
i want to have logical comparision or any suitable formula for folowing condition pls guide.
if column one value is pipe ,column 2 value is carbon steel,column 3 value is
2Inch,column 4 value is -40 schedule,column 5 value(heat no) will be "abcd"
also in same formula column 4 may varry like it may40,80,160 many numbers then my column 5 will be identified value to add in column 5.i.efor 40 sch colm5 value is abcd if column 4 value is 80 then column5 value is efgh,if column 4 value is 160 then column5 value is ijkl.
Hi,
Please Help.
For testing some lower percentages requires logic test result in the list.
READING NOW PREVIOUS RESULT ACTION
-10% -8%
-25% -12% REJECT
10%
25% POSITIVE
FOR - VE UP TO 10% IF PREVIOUS TEST IS POSITIVE (ABOVE 0%) RE-TEST
FOR - VE UP TO 10% IF PREVIOUS TEST IS NEGATIVE (ABOVE 0%) DOSE CHANGE
FOR + VE UP TO 10% IF PREVIOUS TEST IS NEGATIVE (BELOW 0%) FOR CURING
FOR + VE UP TO 10% IF PREVIOUS TEST IS POSITIVE(BELOW 0%) NEXT TEST
READING NOW PREVIOUS RESULT ACTON
-8.0% 5.0% RE-TEST
-12.0% -8.0% DOSE CHANGE
8.0% -12.0% FOR CURING
12.0% 8.0% NEXT TEST
22.0% 10.0% IF(A1825%,"POSITIVE,IF(AND(A18=0%,"RE-TEST"),IF(AND(A18>=0%,B18<=0%,"FOR CURING")))
17.5% 4.0%
2.0% -8.0%
-6.0% -12.0%
8.0% -1.0%
-5.0% 8.0%
I am trying to find a formula where if a LEFT 2 is 01, 02, 03 etc (for date 01/01/18 style), and it enters:
- Jan if it is 01 of a 01/01/18 date format
- Feb if it is 02 of a 02/01/18 format
I have tried the following, but it did not work:
=IF(LEFT(D21,2)="01","Jan",IF(LEFT(D21,2)="02","Feb",""))
Kindly assist.
Sunny
SUCCESS:
I was able to achieve and find the conversion of the date format to a number with "=LEFT(MONTH(D13),2)"
THEN, I expanded the formula to:
=IF(LEFT(MONTH(D12),2)="1","Jan", IF(LEFT(MONTH(D12),2)="2","Feb", IF(LEFT(MONTH(D12),2)="3","Mar",IF(LEFT(MONTH(D12),2)="4","Apr",IF(LEFT(MONTH(D12),2)="5","May",IF(LEFT(MONTH(D12),2)="6","Jun",IF(LEFT(MONTH(D12),2)="7","Jul",IF(LEFT(MONTH(D12),2)="8","Aug",IF(LEFT(MONTH(D12),2)="9","Sep",IF(LEFT(MONTH(D12),2)="10","Oct",IF(LEFT(MONTH(D12),2)="11","Nov",IF(LEFT(MONTH(D12),2)="12","Dec",""))))))))))))
BUT
The blank cells are coming up with "Jan" where it should be a blank "" as in the formula above.
Any suggestions or input?
COMPLETE SUCCESS
I used the following formulas to test and see if the date format translates to a number, which it did
=LEFT(MONTH(D13),2)
Then expanded it to check for output for two months - it worked
=IF(LEFT(MONTH(D11),2)="5","May",IF(LEFT(MONTH(D11),2)="6","Jun",""))
FORMULA WHICH WORKED (expanded it to 12 months, put Jan as 01)
------------------------------
=IF(LEFT(MONTH(D10),2)="01","Jan",IF(LEFT(MONTH(D10),2)="2","Feb",IF(LEFT(MONTH(D10),2)="3","Mar",IF(LEFT(MONTH(D10),2)="4","Apr",IF(LEFT(MONTH(D10),2)="5","May",IF(LEFT(MONTH(D10),2)="6","Jun",IF(LEFT(MONTH(D10),2)="7","Jul",IF(LEFT(MONTH(D10),2)="8","Aug",IF(LEFT(MONTH(D10),2)="9","Sep",IF(LEFT(MONTH(D10),2)="10","Oct",IF(LEFT(MONTH(D10),2)="11","Nov",IF(LEFT(MONTH(D10),2)="12","Dec",""))))))))))))
Used the LEFT(MONTH(CELL_REF),2) to verify the translation of the date to number, THEN expanded it to the following, it worked.
=IF(LEFT(MONTH(D10),2)="01","Jan",IF(LEFT(MONTH(D10),2)="2","Feb",IF(LEFT(MONTH(D10),2)="3","Mar",IF(LEFT(MONTH(D10),2)="4","Apr",IF(LEFT(MONTH(D10),2)="5","May",IF(LEFT(MONTH(D10),2)="6","Jun",IF(LEFT(MONTH(D10),2)="7","Jul",IF(LEFT(MONTH(D10),2)="8","Aug",IF(LEFT(MONTH(D10),2)="9","Sep",IF(LEFT(MONTH(D10),2)="10","Oct",IF(LEFT(MONTH(D10),2)="11","Nov",IF(LEFT(MONTH(D10),2)="12","Dec",""))))))))))))
Sunny:
Alright! That's a lot of nested IF's but you figured it out. Congratulations on a good use of the MONTH function.
Thank you for sharing. I'm sure others will benefit from your work.
You have eliminated my sleepless night for weeks now.
Thanks
I have A2=28, B2=45 to get a smaller value i applied the formula =IF(A2>B2,A2-B2,A2). So i got an answer has 28.
My question is if i change a value in A2=50 now i should get a value has B2 in C2 cell.
So i have entered has =IF(A2>B2,A2-B2,A2)*OR(IF(A2<B2,B2,B2))
Still it is not working, can someone assist me how to put a formula with a proper condition?
Hi all
How can i insert any function in the criteria of countif
For example i have a marks sheet i want to count that how many student get 80% or more then 80% marks in examination
One way is i have to calculate the 80% and write in the criteria
What is another way??
How to write a formula for this
10 - 64 Reading below grade level
65- 81 On Level
82 - 100 Reading above grade level
Thank you so much
Hello, Arcita,
Please try the following formula:
=IF(A1<=64, "Reading below grade level", IF(A1<=81, "On Level", IF(A1<=100, "Reading above grade level")))
You can learn more about Excel Nested IF in Excel in this article on our blog.
Hope you'll find this information helpful.
hi,
What formula could i use if i don't want a number to go over 7.5...If a number is lower, is displays the actual number but if it's higher it shows only 7.5
THank you,
HI..
=IF(H19<7.5,H19,7.5)
SL P A = Error
- P - = P
SL P - = SL
- P A = A
I need formular for 2 criterias,
Increase base salary offer (X)
if GPA is
>3.5 by 2000
>3.0 by 1000
>2.5 by 500
and additional bonus
if experiential activity
>4.5 $3,500
>4.0 $3,000
>3.0 $2,500
I wrote the following code. The code provides partial result correctly, but not all! I don't know what is the wrong with my code? =IF((Distance/C4)<1, E_saved,(IF((Distance/C4)<2, (E_saved-(1.8*((Distance/C4)-1))),(IF((Distance/C4)<4, (E_saved-(1.8+(3.35*((Distance/C4)-2)))),(IF((Distance/C4)<11,(E_saved-(1.8 +(3.35*2)+(2.375*(Distance/C4)-4))),0)))))))
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
Rajiv
Priya R T
Amit T
Hi, I need to calculate the tax for the incomes. If the income is 5000 that is tax free. If the income is greater than 5000 and less than 12500 then, it is 12500-5000 and the remaining is taxed 2% and if the income is more than 12500 and less than 100000 then first 5000 is deducted then 12500 is deducted to be taxed 2% and then the remaining is taxed 10% and if the income is more than 100000 then the 87500 is to be taxed at 10% and then the remaining is taxed 20%. Is it possible to bring them in only one function? Thanks for your kind support.
I've been trying to figure this out for two days using various resources and I'm stuck. I feel like the solution is easy and I'm just overthinking it at this point.
Let's say I have a table in Excel with headers and many rows of varying text and numbers, with some cells containing both (random ID numbers generated by an outside source). The table and headers have names for use in other formulas (no trouble there).
Col_With_Hdr_1
A Specific Name In This Cell
Another Specific Name In This Cell
Col_With_Hdr_8
(123abc678ruw9257xyz)
this is literally a blank cell
(2756imv47zqp115mv2)
this is literally a blank cell
Col_With_Hdr_11
formula result displayed as text goes here
formula result displayed as text goes here
this would be blank if formula result is 0 or No
I can't figure out how to write a formula that shows
IF Col_With_Hdr_1="A Specific Name in This Cell" then put specific text in Col_With_Hdr_11
OR
IF Col_With_Hdr_8 IS NOT BLANK then put same specific text in Col_With_Hdr_11
I hope this makes sense, and I appreciate any ideas anyone may have. Thank you!
Hi!
I have 2 worksheets containing an identical layout - one is data ran last week called ‘old”containing 6000 rows of customer data and then data ran this week ‘new’ containing 7000 rows of data. Both have one header row. I need to find any changes occurred on each customer record - column A contains a unique ID for each customer and column P is the data range I need to look for changes in). NB; the data will be held on different numbered rows . So to match and highlight unique id’s/customers from column A on the old sheet to column A on the new sheet ( I can do this by using conditional formatting =COUNTIF(old!$A;$A,A2)=1 with a range of $A$:$A$9999. Then I format fill in green to highlight but then of these matched green cases I need to look in column P for any changes to compare old and new values and it’s this part of the formula I need help with.... thanks in advance for any help anyone can offer.
I have an excel sheet with many statements giving results as "True" or "False"
1) Now I need to introduce another column which will say if Cell A1, Cell A2, Cell A3 and Cell A4 are "True" say "Yes" if not say "No"
2) I also need another formula which says if Cell A1 and Cell A4 are "True" and Cell A2 or Cell A3 are "True" say "Yes" if not say "No"
Can Someone please help me
Hi, i want to know the formula in Excel if i need to identify the negative figures and make it zero automatically then what is the formula.
rollno name eng hindi sci maths Total
1 ram 56 100 57 76 289
2 sham 76 68 14 20 178
3 sita 24 91 66 59 240
4 gita 99 83 13 77 272
5 radha 60 63 40 40 203
6 mohan 32 77 65 52 226
Enter roll or name
ram /1 56 100 57 76 289
if enter roll name or name then all sub marks dispaly is it possible using vlookup??
I am applying double OR function inside IF but getting results in both digits as well as words
Please suggest me
Hey so im trying to pick up numbers in negative in the tabel
Cell1 Cell2 (this is in a tabel)
A 2
D -1
A -3
A 1
D 3
E 2
A -1
If i want to only pick up the sum of all "A" that is negative numbers ignoring the positives and the other letters, anyone know how to do that? (the result should be -4 in this case)
I have tried this one but dosent work.
=SUM.IF(Cell1,"A",IF(Cell2<0,Cell2;0))
one day i'll understand
same bro same ajwndoiwejfie
Hello,
Could you please help me why my function does not work:
=IF(OR(CO2="4 - High Professional",CO2="7 - Versatile Performer",CO2="8 - Future Star",CO2="9 - Star Performer","Top Talent",IF(CO2="0","Not Reviewed","Rest")))
Thank you for your suggestions
sir,
please help how to coding in Ms Excel for the give table in ONE cell.
=IF(D10-E10=1,"1",IF(D10-E10=1, AND C10=2,"2",IF(C10-D10=2, AND E10=1,"3",IF(C10=1, ANDE10=2,"4",IF(C10-E10=2, AND D10=1,"5",IF(C10-E10-D10=2,"6"))))))
PLZ CORRECT THE ABOVE FORMULA
JSC_GPA SSC_GPA HSC_GPA Out_Come
Best Best Best ?
Best Best Good ?
Good Good Medium ?
Best Good Best ?
Best Best Good ?
Best Best Best ?
There are 4 possible categories
* Best
* Good
* Medium
* Low
if 2 of then Best then Out_Come Best
if 2 of then Good then Out_Come Good
if 2 of then Medium then Out_Come Medium
if 2 of then Low then Out_Come Low
Please Help Me....
Hi
Could you please help me
For example
A1. 100
B1. 50
I need 10%,more than B1 not greater than A1 if the value less than 10% I need A1 valvu
I have seven cells (week days),
Monday 15
Tuesday 20
Wednesday 16
Thursday 0
Friday 0
Saturday 0
Sunday 0
and, i update everyday respective day of week. So, I need an "If" formula, that calculate average only for days that their cell is different from 0? (So, if it is Thursday, when i update that cell of Thursday, I want that the average formula to calculate average of days Monday-Thursday only that their value is more than 0, not rest days).
Thank you :)
Hi All,
I need a formula for the following:
If the sale date is more than a year from the purchase date then give me half the gain, if not give me the full gain.
So...
Purchase Date Sale Date Gain Net gain
11/03/2018 12/03/2019 5,000 2,500
13/03/2018 12/03/2019 5,000 5,000
Hi!
I’m doing some investment cost analysis on a power plant project and could need some help.
So if I have a plant from 400 MW higher use this formula to estimate cost, if below 400 MW then use this other formula with a coefficient. Do you have any ideas how I could write this?
BR
Marco
Please can someone help me solving the following problem.
In my case
cell values are as follows:
D67=0,5
D68=0,7
D69=0,9
When I select the value 0,5 (D67) i want it to move to D21, D22 and D23 into my calculation sheet
and when I select 0,7 (D68) I want that this value moves to D21, D22 and D23 of my calculation sheet instead and replacing the forgoing value 0,5 ...and so on.
Is there a solution to this problem?
Kindest thanks for your help
Peter
I had been using lotus 123 a lot.
In lotus for doing data querry which i filtering data in excel, i could use criterion as logical conditions like age>5#and#age5, age<9)
does not seem to work
Any solutions
Hi,
Can you help me with the formula with 3 conditions to find/count >1hr login & >0 Calls = "Considered for Billing", <1hr login <0 Calls = "Not Considered for Billing", <8hrs login & <0 Calls = "Considered for Billing".
SL User ID Total No of Calls
1 1001 8:30:00 80
2 1002 8:00:00 54
3 1008 8:00:00 0
4 1007 3:00:00 34
5 1006 2:00:00 15
6 1003 1:00:00 1
7 1004 1:00:00 0
Can you help me write the formula for the following situation: Family Reunion Fees for
different age groups. I have 2 columns: Column #1 Age; Column #2 Reunion Fee.
Reunion Fee has these categories:
If Age is between 13 and 100 then the fee is $125
If Age is between 4 and 12 then the fee is $85
If Age is 3, then the fee is $45
If Age is between 0 and 2 the fee is $0
I have a data form and will be type in the Age in column #1. I want Excel to select the fees. (I do have a table with the ages and the fees) I don't know how to write a formula that will look up fee for the different ages put that fee in Column #2.
Information in a Table:
Ages Reunion Fee
13 to 100 $125
4 to 12 $85
3 $40
0-2 $0
HI
Can you help me
How to merge the following formula Merged In Excel please suggest
1.(SUMIF(C1:C8,C5,L1:N7))
2.IF(I5,"<0",0)
Hi Team,
I need assistance, currently the formula below, works Ok for Text "2019" value in particular column, We converting mmm,dd, yyyy to MM/dd/yyyy. Since the file now contains some "2020" value in text the formula does not convert. I need the Formula to work for Text "2019" or "2020" as well. Please shed some light!!!
=(TEXT(SUBSTITUTE(report!B2,"2019,","2019"),"mm/dd/YYYY"))