IF is one of the most popular and useful functions in Excel. Generally, you use an IF statement to test a condition and to return one value if the condition is met, and another value if the condition is not met. Continue reading
Comments on: How to use IF function in Excel: examples for text, numbers, dates, blanks
by Svetlana Cheusheva, updated on
Comments page 67. Total comments: 4830
Hi Sir/mam
my query is
if A1 divisible of 28(like 280) then system show in B1 (280/28*30=300)else system show b1=A1
A1=280 B1=300
A1=30 B1=30
plz help me
Hi,
I am working in a company and I am using ms excel 2013. I want to know that how can i use date formula that if any date less than or equal to 31-1-2016 than comment This is old.
Please clear me date formula. i am very confused.
Thanks & Regards
Dushyant Kumar
Hi
Please can you help.
I have a holiday formula see below, that H = 1 days, but I also need it to show if the member of staff decides to take a half day. I can't get the formula to work?????
COUNTIF(C12:AG12,"H")
Thanks in advance.
Hi, Lynsey,
if half day is 'HD', then the formula will look like:
=COUNTIF(C12:AG12,"H")+COUNTIF(C12:AG12,"HD")
But it will return the sum of both: complete and half days taken. If you want them to return two results separately, put each part of the formula in different cells.
To learn more about how this formula works, read this article.
I need help creating a formula in cell E120.
If E1190,0
I would like the formula to return the percentage of X.
Hello, DebbieH,
you can read the article about percentage calculations or specify the conditions and give us more details. Should 'E1190' be equal to '0' or you meant something else? Where is your 'X' placed?
Not sure, why my information keeps getting cut off. I'll try again....
If E1190,0. I would like the formula to return the value of X%
I need help creating a formula in cell E120.
If E1190 then 0
Thank you.
Hi
I require If furmula, in which emmotiation can be place with colour (smiling- green, angry-Red)
Hi guys,
I have recieving checklist for kitchen which I using as a refence for traceability. By the way I have columns A1 Date , B1 Delivered , C1 ID# , D1 Time Stamp.
A1 Date , B1 Delivered , C1 ID# , D1 Time Stamp
Ctrl + ; YES/NO I+A1TEXT =IF(B7="YES",IF(F7="",NOW(), F7), "")
Row D I filling by this formula (for example) =IF(B7="YES",IF(D7="",NOW(), D7), "") only IF YES at B7, But I want to add next to that what OR IF B7 NO (if B7 NO than I will keep from previous D6). Because when I type in my B7 no than my D7 is empty, I dont want empty, I want record from previous (D6).
I was recieving Salmon, and in recieving checklist I adding date of recieving (A1), than did I recieve that day or no (B1), than ID Number (C1) with I for ingredient and 6 digits date of recieving and time stamp (D1) same I for ingredient and 6 digits date of recieving.
If I did not recieve today than row D I want to keep date of last recieving.
Many thanks,
Danilo
pls. advice how to arrive 0 in the same cell if amount is -1, with along with below formula.
=(AF9-300000)*10%
A1 Cell Value 21/03/2017
in Cell B i would like the Valve to be M31721
Where M is for March
3 is from Cell A1 (03 MM)
17 is from Cell A1 (17 YY)
21 is from Cell A1 (21 DD)
hi i want to check where there term is more or less than one year or five year. i know to claculate the term but i dont formula now how to check the term with conditions
I want to create YES for a number of columns when there is a nonblank in any of them. I tried the following, but it does not produce the correct result. Advice?
=IF(ISBLANK(D33:O33)="","Yes ","No")
Hello, RoseG,
since ISBLANK is used for one cell only, try using this:
=IF(COUNTIF($A2:$H2,"")=0,"No","Yes")
For more info on the COUNTIF go here.
Hope this helps.
if A2 is between 501 to 1000 return value 20 and if A2 is between 1001 to 2000 return value 30.
Example :
Order value Charges
0-500 10
501-1000 20
1001-2000 30
20001< 40
How to write formula to get this results ?
Example :
Order value -- Charges
0-500 --------- 10
501-1000 ------ 20
1001-2000 ----- 30
20001< -------- 40
Hello I need help with a formula. For example if the Cell A1 reads a number less then 20 I would like that cell A2 to read what ever the number on A1 + 1. But if it reads more then 20 I would like to come back with A1+2. Did I explain my self correctly.
Hello, Jonathan,
I believe, this will help:
=IF(A1<20,A1+1,IF(A1>20,A1+2,""))
Note, that you didn't mention what should happen if the number is equal to 20, so in that case A2 will remain empty.
I have a question, i have to check Year of Birth from 1 cell from another cell which has only Year of birth.
EXample
Cell A1 is 01/01/1979
Cell B2 is 999-1979-9999
I am trying to use the formula =if(mid(b2,5,4)=year(A1),"True","false")
However all my values return as FALSE?
Any advice?
Hello, Kristoffer,
as it is stated in the article, the MID function always returns a text string, even if the extracted substring contains only digits. To convert an output into a number, use MID in combination with the VALUE function. Your formula then will look like this:
=IF(VALUE(MID(B1,5,4))=YEAR(A1),"True","False")
Hello
I have a question...
I have 5 dates in different cells (A1, A2, A3, A4, A5) I need a formula that tells me if any one of these 5 dates is in the past 6 years from 21 March 2017 then return "YES" could someone help me please?
Very Helpful!
Can anyone interpret this formula. It works but I'd like to understand what it means. It's in a document that I use.
=IF(ISBLANK(F27),"",SUM(G26-F27))
Thanks
If cell F27 is blank, then return a blank cell. If F27 is not blank/has a value, then return the value of cells G26-F27.
hi,
good day.
here's the flow. and cell a1 if i put their "negative" and cell a2 should put there the current date.
I want to use the following in excel:
If 0 - 25% then low
If 26% - 50% then medium
If 51% - 75% then high
if 76% - 100% then potential
Hi
I would like to take an average of the below example, A1(values for month of Sep)followed by B2(Oct) etc.
Sales Group(1)-has sales figures for all months below and have the averages
Sep Oct Nov Dec Jan
10
20
30
40
Group (2) has only Jan and Feb completed, Mar, Apr, May is yet to be filled in as those months have no sales figures as yet.
How do I get an average of groups that completed all the months excluding the DIV/0 errors of uncompleted months for the other batches. The formula I am looking for is when the uncompleted months are filled(Mar,Apr,May) then the formula should take the 2nd Batch into consideration, if not then this batch should not be counted in the total average.
=IF((I2-H2=10);10;(SUM(I2-H2)))
If (I2-H2) = display 10
If I2-H2 10 I want the have 10 in that column. I think it needs to be something like this?...
=IF((I2-H2=10);10;(SUM(I2-H2)))
Could you please help me?
I want to keep the formulae changing for a chemical based on the value of n.
Can you tell me how I can use excel for this
CnHn+2O8P
If value is high and low then result should be Amber
Hi, Goodmorning,
I want to use IF function to compare "the sum of of a range of cells, with a figure in a cell, and to return "Complete" if the sum equals to the figure in the cell, and if it is not equal then to return "Not complete, yet to receive:" and display the remaining quantity by doing a subtraction of the total quantity and received quantity.
Please suggest me how to do this.
Hi Svetlana,
Your help on this forum is truly invaluable and helped me many times but now I run into a problem that I cannot solve.
Column A contains a list of computer names
Column B contains the list of IP addresses detected on the computer.
Computer Name IP Address
COMP1 192.168.10.1
COMP2 192.168.3.225
COMP3 192.168.3.224
COMP4 192.168.3.226
COMP5 192.168.3.178
COMP6 "192.168.146.147,192.168.3.172"
COMP7 192.168.3.170
Issue:
When two IP addresses detected on the computer, the reporting software puts these into parenthesis and separates them by a comma.
Task:
If the computer only have one IP address I'd like to show this in column D
If the computer have two IP addresses then I'd like to show the first address in column D and the second IP on column E, like this.
Computer Name IP Address First IP Address Second IP Address
COMP1 10.136.10.1 192.168.10.1
COMP2 10.136.3.225 192.168.3.225
COMP3 10.136.3.224 192.168.3.224
COMP4 10.136.3.226 192.168.3.226
COMP5 10.136.3.178 192.168.3.178
COMP6 "10.136.146.147,10.136.3.172" 192.168.146.147 10.136.3.172
COMP7 10.136.3.170 192.168.3.170
COMP8 10.136.3.165 192.168.3.165
COMP9 10.136.3.175 192.168.3.175
COMP10 "10.136.144.137,10.136.3.33" 192.168.144.137 10.136.3.33
COMP11 10.136.3.105 192.168.3.105
So far I have been able to display the first IP address by using a helper column C where I detect the presence of the comma using:
=COUNTIF($B2,"*,*")
It returns 0 when a single IP is resent and 1 when there are two.
Next in column D I use this formula to parse out the first IP address:
=IF($C2=0,$B2,LEFT($B2,(FIND(",",$B2,1)-1)))
Next in column E I use this formula to parse out the second IP address:
=IF($C2=0,"",MID($B2,FIND(",",$B2)+1,256))
Problems:
How do I rewrite the formula to eliminate the helper column C?
How do I get rid of the preceding and trailing parentheses?
So far this is how my sheet looks like.
Computer Name IP Address Dual IP First IP Address Second IP Address
COMP1 10.136.10.1 0 10.136.10.1
COMP2 10.136.3.225 0 10.136.3.225
COMP3 10.136.3.224 0 10.136.3.224
COMP4 10.136.3.226 0 10.136.3.226
COMP5 10.136.3.178 0 10.136.3.178
COMP6 "10.136.146.147,10.136.3.172" 1 "10.136.146.147 10.136.3.172"
COMP7 10.136.3.170 0 10.136.3.170
COMP8 10.136.3.165 0 10.136.3.165
COMP9 10.136.3.175 0 10.136.3.175
COMP10 "10.136.144.137,10.136.3.33" 1 "10.136.144.137 10.136.3.33"
COMP11 10.136.3.105 0 10.136.3.105
Your help is greatly appreciated!
Cheers,
Attila
Somebody please help with data validation, Like i need to extract data from one worksheet to different worksheet for more THAN 4 columns at a time .. don't know to deal with it...
Thanks
Somebody please help with data validation, Like i need to extract data from one worksheet to different worksheet for more 4 columns at a time .. don't know to deal with it...
Thanks
Hi,
I have three colums and four rows with different values.I have a column more with a date for each row.As example:
Date: C1 C2 C3
12.12.2013 205 201 100
11.12.2013 32 10 150
9.12.2012 99 52 87
What command i must use to see in a tabel the most recent date appearence of any value from colums C1,C2,C3?
Sorry i have 4 colums with 3 rows and the table is this:
Date: C1 C2 C3
12.12.2013 / 205 /201 /100
11.12.2013 / 32 /10 /150
9.12.2012 / 99 /52 /87
What command i must use to see in a tabel the most recent date appearence of any value from colums C1,C2,C3?
Sir,
I need to solve the below:
if(a1="exe",2999), if(a1="dlx",3999), if(a1="std"=1750)
Please help me.
Thanks a lot.
Hello i have this formula in my cells =IF(AND(M$2>=$D5, M$2<=$E5),"1")
then at the bottom of the sheet, I tried to sum all the 1 in the column but that "1" must not be a number because I always get a 0 result.
I am really trying to put a 1 in if between a certain date and then sum them all up.
can anybody help?
Hi What do I put if the customer’s age is less than 25, the Daily Insurance is £4; if not, it is £3.
Thank you
Please help!
I want to put a formula in so that D4 = A4, But if A4 is blank I want D4 to = C4 instead.
Can you please help me to make these formula;
if I enter :
yes = 0
no = -2
n/a = -1
Thank you very much
Marks
10 to 20 = good
21 to 30 = very good
31 to 40 = excellent
formula please ?
=VLOOKUP(C2,$E$2:$G$5,3,TRUE)
In this example, your data would be in column C. The formula, which will return the value of Good, etc., will be in Column D.
You will need to create a VLookUp table with 3 Columns. In the formula above, the VLookUp table is in Columns E, F and G.
Cat Min Cat Max Value
10 20 Good
21 30 Very Good
31 40 Excellent
Plz Suggest function about the following criteria
If A1 have integer/numeric value then pass 1 and if A1 have string/alphabetical value then pass 2
I WANT THE FORMULA IF A1>0 THEN ONLY FORMULA APPLICABLE IN B2
I am trying to enter a formula based on the following scenario: If I have 3 cells selected in excel and want to automatically have the last cell populate in another cell how is that done?
Lets say I have cells A, B, C and I have column D for the final estimate. Lets say there is a estimate in cell A and cell B.I do not need the two cells to add up, but I need cell D to only give the total for cell B since that is the last cell with an estimate.
Basically, I only want the last estimate out of the 3 cells.
lets say there is a estimate in cell A and cell B
I do not need the two cells to add up, but I need cell D to only give the total for cell B since that is the last cell with an estimate
>10 lac upto 50 lac Rs 2500/- flat
Dear Sir,
If calculating late coming
TIME PERIOD IS MORNING “7AM TO 13 PM, EVENING 16PM TO 21 PM “
Condition for late coming
1: If attendance is marked before 7Am and 16 Pm as like after 13 PM and 21Pm is not affected
2: If attendance marked “after 7Am and 16 Pm” as like “before 13 PM and 21Pm” need to calculated
3: If employee is absent, there is no attendance, same time need to show the result column as “Blank”
Just attach the Sheet details
TIMINGS 1-Feb 1-Feb 1-Feb 1-Feb 1-Feb
AM PM PM PM IN OUT IN OUT late
7:00 13:00 16:00 21:00 7:00 14:00 14:00 0:00 0:00
6:00 13:00 16:00 20:00 0:00 0:00 0:00 0:00 9:00
Equation : .=IF(G5<C5, C5-G5, )+IF(I5B5,F5-B5)+IF(H5>D5,H5-D5,)
Please check the second column, there is no date, the late coming column shown the Amount, Need to blank the column
Hi - I'm trying to create a formula to do the following.
If original date of hire equals last date of hire leave blank if not display the last date of hire.
thank you!
J4=N3 the result should be not pass because of J4. how can I get this formula to work regardless if C4 is a pass? sorry posted wrong before. thanks guys. much appreciated.
If "C" column Budgeted figure is 40.83 & Actual in "D" column is 37.69 then the VAR in "E" column is -3.15 with 92% Achievement in "F" column and the weightage defined for 100% achievement is 35 Marks which is in "G" column.
I am looking out the IF formula help that if "F" column is less then 79.99% then "0" marks, and if same is between 80% to 94.99% then half of the 100% weightage defined 35 marks that is 17.5, and if the scoring is between 95% to 100% then the Actual % of weightage defined like if 97% achieved then 97% of 35 marks that is 33.95 marks.
Dear,
I have an excel file in which I have mentioned digit from 0194 to 0300 (cash invoice) and 0070 to 0125 (credit invoice), in a single column.
On the top of the excel file, I want to mention a cell with latest cash and credit invoice entered.
I am needing a formula that will calculate match on percentages. Like if a person is contributing 4.5% to a fund, the company would match 100% on the first 3% and then .5% on the next 2%. So in the end if a person contributed 5%, then the company would match 4%. So, if a person was paid $1000.00, $50.00 would be their 5% deferral, the company would contribute 100% on the first 3% which would be $30.00 and then .5% on the next 2% which would be an additional $10.00. But if I could have that all in one operand and also to return what the percentage should be if it is an awkward number too.
OK SO I HAVE THIS PART RIGHT =IMSUB(720,D4+J4)BUT I ALSO WANT IT TO DO IF D4 FOR IS EMPTY THEN SO IS I4
I need to do a study for value between 100 and 110. I tried =if(100<A1<110,"Bad","Good"), but it doesn't work. Please help.
Hi
I would like to copy cell A1 to cell B1 (in another worksheet). What formula should I use so that if cell A1 is blank cell B1 stays blank as well without returning "0", "#REF, etc..
Thanks!
Dear All,
Request formula for the following condition.
A1 = TRUE , A2 = TRUE & A3 = FALSE
in B1 if i choose AND (from List box)
1.C1 should check All "A1, A2 & A3" cells contains TRUE value and return TRUE otherwise FALSE
in B1 if i choose OR (from List box)
2.C1 should check Either of "A1, A2 & A3" cells contains TRUE value and return TRUE otherwise FALSE
Thanks in advance for your help.
Hi,
pl. help me by sharing the formula.
in a cell there is a figure,i.e. Rs. 25,000,000, now I want to make it Rs. 250. Another example Rs. 2,500,000 would be Rs .25.
can it be possible by FORMAT CELL option, as in cell, where I want to change the figure, already having different formula.
thanks,
S Khan