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 138. Total comments: 4830
Hi,
I am hoping to create a formula to use in conditional formatting that would allow me to perform the following:
I have a list of employees in column A, date in column B, and number of sales in column C.
I am wanting to conditionally format the sales cell (in column C) as new sales data is entered. I want to compare the new sales figure against the most recent number of sales for that particular employee. With a reduction in sales of >3 formatted red.
Your assistance is greatly appreciated.
I have 3 cells (L3,M3, N3) with either true or false as responses to earlier coding but need to highlight where I get answers that are True,False, True in that order . How do I achieve this?
Please Help me :
=if(a1=2366,2355,2353 then b1 will be 120, if(a1=2377,2357,2354 then b1 will be 130)) how can i put the formula..
i need to make a formula in excel to put bonus marks, for example if kevin got 25 marks he will got another 25 bonus, if he got 50 then another 25, each 25+ mark got bonus of 25... i will put only the mark on one cell and i need in the bonus mark to calculated by excel in another cell... please help
=IF(F2:F152="Yes", "G3"),(F2:F152="No", "G3") returned #VALUE! What is the issue?The logic I am trying to achieveshould be if F2 thru F152 = Yes then return the name G3 and if f2 thru f152 = No return the name in column G3. Can you give me the correct formula to achieve this?
Hello,
I want to write an If then statement that takes cell contents and adds the same word to different cells. For example. suppose CELLS A3:A36 are words related to education (i.e. test, assignment homework etc...) Now suppose I want to add the word "calendar" to the words in cells A3:A36. Which would return "test calendar", "assignment calendar", "homework calendar" etc..
How would I go about doing that?
hi,
i need help please,
how can i add number if my result is less than 0
example;
=(P9-DAY(TODAY())) and i got the result -1 i dont need to see - so i wanna add 30 how can i do that
thx
Please help, I need a formula like when you subtract A1-B1 if the answer is 0.something the answer will be 0.
Like this one: =IF (A1-B1<1,"0")
Thank you...
asskm..
saya ingin membuat satu rumus if :
jika A1 berisi desimal maka B1 adalah 1, jika A1 berisikan teks maka B1 adalah 0,
mohon bantuannya untuk bisa sy membuat rumus excelnya. terimakasih..
i was making employee salary sheet , i am using three rows,1st row is total hours, 2nd row is basic overs & 3rd second row is over time , our company basic hours is eight.
Example :-
total overs = A1=10 B1=12 C1=A .....Ext.
Baic overs = A2=8 B2=8 C2=A ....Ext.
Over time = A3=2 B3=4 C3=X...Ext
iam using basic overs A2 =IF(A1=8,"8",IF(A1="A","A"))))
A3=(=IF(A1=8,X,IF(A1=8.5,.50,IF(A1=9,"1",IF(A1=9.5,1.50,IF(A1=10,2,IF(A1="A","X"))))))
iam using this function ,but Sundays also working overtime . i need help you Sundays hours u need over time row which function use .......... please give him the reply
I request to you i make a time , using if statement =if(A110,"A-10")
I request to you i make a time sheet , using if statement =if((A110,"sum(A1-10))
Hello I need to use a formula that will display YES if the amount is $100 or more and remain blank if it is under $100.
So far this is what I have =IF(B2>=100,"Yes")
Not sure if this can be done. Need to search a range in A1:A20 for a text string and then return the value in the same row but 2 columns right. So for example look in A1:A20 for "Find me" and if find me is in A4 return the value that's in C4. Any ideas if this can be done?
I need a formula to automatically display the current accrued PTO in a specific cell every time the excel sheet is open.
The accrued PTO is 6.15 hours every two weeks. I have created a table with the dates and the corresponding accrued hours for reference.
I tried IF and VLOOKUP statements but can't get the formula to work.
Can someone suggest a formula?
Any help will be greatly appreciated!
Thanks,
Raphael
Hi Svetlana,
Thanks for the great post, it helped me a lot. My case is a bit different. I want to use IF function in this way:
IF xxx has the word "thanks" inside it, then "welcome" or "ok".
I hope there is a way.
Thank you.
Need a formula to calculate following: if city =x zip =y. I keep getting a value error.Note I have approximately 10 cities and zips to include.
Also need a formula stating: state always =CA
Thanks for the help.
i need some help from you.
=IF(M9="yes", C9," ") How can i put this command in sheet 1 for the result of sheet2
i am working on aproject. I need help with
f2 contains both words and numbers. How do i display the text and numbers in 2 separate cells?
got it
Please help me check my error
=if((Q339,"D"),(Q3>49,"C"),(Q3>59,"B"),(Q3>69,"A"))
I am computing exam result
Sir if my output value is 12100, 12200,12300, 12400 all value change auto 12500.
And my out put is 12600, 12700, 12800, 12900. That value change 13000.
hello sir,
I want to know how to add a1 value.in a2, a3, a4.....
If i change value a1,all a2, a3, a4 ...value change auto.
=IF(I10-K10, IF(I10="",N10-K10, IF(ISBLANK(N10),"2")))
the first one will be formula then the other if blank of i10 IS BLANK USE FOR N10 THEN IF N10 IS BLANK IT SHOULD BE EQUAL TO "2" THANKS
hi how to comine this formula thanks
SORI ITS N10.. THANKS
Is it possible to create an IF calculation that asks a "greater than this number, but less than this number" question? i.e., if E17 is greater than 1800 or less than 2400, than perform E17-540*60%, otherwise place a zero. I get errors when I try this example...
=IF(E17>1800<=2400, (E17-540*60%), 0)
Thanks
Hello, I have a date in cell D4, let's say 1/2003 displaying as 2003 due to custom formatting to YYYY and a formula in E4(also set to YYYY) which is D4+4385 because I want E4 to display 12 years after the date in cell D4. This formula works great if I have data in cell D4. If I don't, it will display 1912 in E4. What formula can I add to E4 to make it blank if D4 is blank without deleting the formula =D4+4385? Losing hair as we speak :/
Hello sir, I'm Trying to enter Data in Sheet in form of numbers
for example:-
Roll-No:- 801
Maximum Marks
20
20
20
20
20
20
20
Sig. of Class Incharge
Roll-No:- 815
Maximum Marks
20
20
20
20
20
20
20
Sig. of Class Incharge
but the problem is when i try to drag or paste the same cells in another line.......Rollno has changed 814 instead of 802. My all cells are refrence cells of sheet1.
Now which formula is best during drag or paste the rows.If i paste the row after 14 Rows ..roll no show 815 instead of 802.
please guide me what should i do?
if u want i can send you the copy of Sheet.
Thanks & Regard
Gandhi
Assume that even numbered respondents (identified by RespondentID) are business travelers and odd numbered respondents are leisure travelers who travel infrequently. In column F on the surveyData tab, create a formula that identifies each respondent as either a Business traveler or Leisure traveler. You will need to use Excel’s If() function (see your Excel handbook or Excel help). Title this column travelerType. Ensure that cells F2:F501 are filled with the formula. Please help me create a formula.
Hi, I need some help with this formula. A1 = $25000 B1= if(A1>0,"Total (A1)","Total"). If A1 is greater than 0, I would like B1 to show [ Total ($25,000.00) ] and if A1 = 0, I would like it to show [ Total ]. Does anyone know if this is possible?
Hi Svetlana ,
I need help with this one. I have one column lets say A1 : A38 that have some values / text . Those values can repeat themselves through A1 : A38 many times . I would like to get in another row or even sheet only one of each of those values or records.
For Example : A1 have TB231 ; A2 TB236 ; A3 TB555 ; A4 TB555 ; A5 TB231 etc , now in separate sheet or row i should get only one of those , it dosent matter how much it has same records i just need that i get value of all different in separate sheet or row.
Results of example should be : D1 TB231 ; D2 TB236 ; D3 TB555 , i tried with IF function but for 38 or more rows would be huge so maybe there is other easier way.
Thank you
How to check if there are 10 characters in a cell or not
Is there a way to create an IF statement based on whether a number is a date? For example, If the value in A1 is a date, return the value of the date, otherwise, do nothing.
I want a cell showing - , when i use it for calculation it's value should be 1.
Please tell me how to do it?
Please some one help...
I need can enter the data if the nearest cell having only as "OK" otherwise should not able to enter any data.
EX:
E10=OK---> E11= Possible to enter data
E10=asfa-----> E11= Not possible to enter data
if row xx column xx = (todays date) display row xx in column xx (for example)
I need to display a cell from another tab row using today's date. How would I do this?
example: if cell a1 = "true" go to sheet 2
Hi. I'm trying to figure out how to make excel go to the next sheet if a particular cell value is equal to "true".
Hello dear friends
I need help I have
(column A is time in) (column B is time out) A1: 7:30 PM B1: 11:00 AM, I'm using the following formula to calculate the hours worked, less than 30 minutes lunch
=MOD(B1-A1,1)*24-"0.5"
BUT I only need to take the 30 minutes lunch ONLY if the employee worked more than 6 hours.
"IF EXPERTS "HELP!", (THANK YOU) "" :)
(employees are nurses so they work overnight and all kind of crazy hours). Can you help me to figure out an "IF" that works for something like that?
Column
A B
Company A
Rpt1
Rpt2
Rpt3
Company B
Rpt1
Rpt2
Rpt3
I want analyse the similar data above by puting the header name (company name) next to each sub item cells
example I want to see like this.
Column
A B
Campany A
Rpt1 Company A
Rpt2 Company A
Rpt3 Company A
Campany B
Rpt1 Company B
Rpt2 Company B
Rpt3 Company B
Please anyone to help on this formula?
Hello!
Thank you for this guide. It's very helpful. I do have one question though, I am trying to copy a date on a cell. Say, date is in A1 and a restriction is in B1. My formula goes like this - =IF(B1="0","",A1). C1 should have be a copy of the date in A1 right? Wrong. Mine shows 420417. It shows numbers T_T not the date. Please help. Thank you in advance.
Sorry about this one. After a few trial and error, I managed to get it :D. Here's how I did it...
Make sure that the cell you are copying is in a proper format. Don't let it automate to DATE, CURRENCY and etc. Then, before you create a statement, say =IF(X=A,"","B"), make sure you CLEAR FORMAT that cell. Good luck to you guys :D Thanks again.
Worker Type Emp # Days Worked Total hours Worked Over Time Over Time Charges Basic Pay Net Pay
A 121 20 190
B 122 19 200
C 123 24 231
A 124 21 210
B 125 25 215
B 126 28 180
C 127 15 140
1) Over time=Total hours worked-(days Worked*8)
2) Using If Function calculates over time charges
3) Over time charges=Overtime *(100 if worker type is “A”, 50 if worker type is “B” and 35 if worker type is “C”)
4) Basic Pay =Days worked *(400 if worker type is “A”, 300 if worker type is “B” and 200 if worker type is “C”)
5) Using SUM() function compute NetPay=Basic Pay+Overtime Charges
please solve part 2 and 3 , i m just got mad
18,19,43 in a column with test date at next column. 18's validity is 1 year,19's 5 year and 43's 6 month how can we show the due date in excel at a glance.
Hi,
I am using the =if(isnumber(search) to look for partial value. However, in my list I have 180 and 18. the end result will only pick up 18 instead of 180.
What other functions can I use to ensure that both 18 and 180 will be pick up?
=SUMIF(January!A5:A200,January!U4,January!B5:B200)
The January!B5:B200 has names in it but it is coming up 0. i am sure that i am using the incorrect function
Cell A1 is a hand entered date. Cell B1 is a total dollar value. Cell C1 is # of months. Starting with Cell D1 and going right are 12 more columns for the months of the year. Need a formula in D1 that says this: If the date in A1 is between January 1st and 31st, then take B1 and divide it by C1. Once I can get that, I can nest it with ifs to cover all of the months. My problem is how to do the date portion. Can you help??
1. i have a table with 2 columns (A,B)
2. column A has a list of control measures and
column B has 2 options(yes or no)
3. what i want is if the answer is "yes" to NOT extract the control measure in a new worksheet and if the answer is "no" to EXTRACT
e.g A1 (warning signs in place?) B1 (no)
A2 (workers are aware of local emergency procedures?) B2 (yes)
Hi Svetlana Cheusheva and Alexander,
could you please help in solving my problem
my excel column contains -5:55 -2:26 -2:19 -1:38 1:06 1:16 1:17 2:20 like wise nearly more than 100
my result column to have Neg Neg Neg Neg 1-2 HR 1-2 HR 1-2 HR 2-3 HR
could you please provide me an IF formula
am using 2007 excel
Remaining-TAT(h:m) TAT
-2:19 Neg
-1:38 Neg
1:06 1-2 HR
1:16 1-2 HR
1:17 1-2 HR
1:20 1-2 HR
2:02 2-3 HR
2:05 2-3 HR
I am trying to add 1.5 to values higher than 3. Values 1-3, I need to remain the same. How can I do this?
Ex. person enters 4 in a cell it is automatically calculates to 1.5
Person enters 2 in a cell it remains the sames.
Hi,
Building a transcation register. Currently using this formula =IF(D13>0,H12-D13,H12+F13).
But, when I copy this to the rows below I'll get the last result shown in the rows below. I would like to check the cells for values, before using the above mentioned formula. So when there are no numbers in cell D14 and F14, it will return an empty cell in H14. If there are numbers in either D14 or F14 use above if formula.
Thanks for your help!
Hi,
1) I need a formula in I16 that can check cell B12 for # of servings (if any).
example: B12="Sangría Rojo (2 glasses per guest). 10 servings."
2) If B12=x servings then x*h12
3) If B12 does not contain x servings then h8*h12
example no servings: B12="Sangría Rojo (2 glasses per guest)."
Thank You
Hi Svetlana,
I want to create the formula that select the cell P if the life time less than 10 years ,I put this formula =IF(S5<10,P5,"")but I also need to have same result if life time "1-5", (between 1 to 5) and I put this =IF(AND(S8<10,S8= 1 - 5),P8,"") didn't work, could you please help me.
Thank you,