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
Table of contents
Comments page 53. Total comments: 4813
I need help with this formula: =if(B5:E11>100,"Yes","")
I wanted to put the values if any from the test in the G column. I tried the formula in cell G5; selecting the G5:G11 in the G column and typing the formula in formula bar; and selecting B5:G11 with If from Autosum list.
I get an #Value! message in each cell selected of the G column every attempt.
What am I doing wrong and how can I fix it please?
HI
If TEXT IN A1=B1=C1 THEN THE SAME RESULT IF TRUE ELSE FALES
PLEASE HELP HOW TO USE IF CONDITION
I am trying to put a formula for multiple spreadsheets, where on spreadsheet A the date indicates something as completed and a blank spot indicates something is incomplete using - spreadsheet B contains the date/blank spot. So essentially spreadsheet A is meant to be a summary of spreadsheets B-G (B-G have the data).
Trying to come up with a formula that says, if the number in cell A1 is greater than 1, but less than 500,000 the result is 120.
Similarly, if the cell in A2 is greater than 500,001, but less than 1,000,000 the result is 200.
Thanks in advance.
I am trying to put a formula where the date of one column is more than or equals to todays date then it should display as "not expired" and if the date of one column is less than todays date then it should display as "expired".
Sir
I want formula for
100g 36pkt
Open stock 10cb
Production 5cb+45pkts
So for total stock 45pkts-36pkts difference to be converted into cab(10+1=11) and at pkts 45-36=1 pkt for doing this how to put formul
Regards
Ramachendir
Hi
I'm trying to type a if statement for text with the following criteria:
if a cell begins with ="GA" then "General Assortment"
if a cell begins with "7" or "9" and is 10 characters long then equals "Planogram"
If cell is between "1" TO "9999" then store layout module
Q: Please help me translate this into formula. I'm really having hard for the then statements that are equal to computations.
Statement: << If Hours >
Letter Assignments: Hours is B4 | Rate is C4 | Gross Pay I4
my quay for 1 person 28 days present so we have give to extra bonus amount 10 Sapphose he is not 28 days so how can give me bonus amount
Hi, I'm trying to figure out a formula to list the date in the text. If B25 is a date value, then if it has passed to display a comment that lists the date. When I use this text, it shows Date B25, but I want it to be entered as an actual date value or to come out black if it's not expired. With the below formula it displays Contract expired on, DATE(25). Instead of listing the date.
=IF(B25<DATEVALUE("03/18/2018"), "Contract expired, DATE(B25)", "")
I am trying to count the number of days between two dates but want the return value to be 0 is there is no date in one or both of the cells. So far I have;
=IF(OR([@[Date Required]]"",[@[Date Passed to Ops]]""),NETWORKDAYS([@[Date Required]],[@[Date Passed to Ops]],-1),0)
But I am getting a #NUM! error, what have I done wrong?
need a formula for , any hours over 8 in cells D thru H would be added to the overtime cell (K)
D E F G H total reg ot
8 8.75 8.00 9 8 41.75 40 1.75
=SUM(IF(E197>8,E197-8,),IF(F197>8,F197-8,),IF(G197>8,G197-8),IF(H197>8,H197-8,),IF(I197>8,I197-8,))
Hi,
I want to use 'if' formula, I have 5 slabs 500000 to 999999 = 1%, 1000000 to 1499999 = 2%, 1500000 to 1999999 = 3%, 2000000 to 2490000 =4% & 2500000 to above = 5%
so how to use IF formula, pls help me...Thanks
=IF(AND(D193>=5,D193=10,D193=15,D193=20,D193=25,"5%","")))))
please change the values accordingly.
Hi,
I am new to IF Function. I am trying to create formula to find the Turn around time (tat).
A COLUMN = DATE START
B COLUMN = DATE END
IF DATE END IS BLANK THEN TAT FORMULA IS TODAY()-DATE START
THEN IF DATE END IS WITH DATE IT SHOULD BE DATE END MINUS DATE START
WHAT WILL BE THE FORMULA FOR THIS USING THE IF FUNCTION?
Hello,
Please try to use the following formula:
=IF(ISBLANK(B1),TODAY()-A1,B1-A1)
Where B1 is the cell with the end date, A1 is the cell with the start date.
Hi,
I'm new to IF function, I'm trying to write a formula but there's a problem on it, I need help.
A = Date 1
B = Date 2
C = Status
D = No otherwise overdue
=IF(AND(A<TODAY(),"Overdue","No"),IF(AND(B<TODAY(),C"Approved & Closed","No","Overdue")))
Hi Team,
I need a formula for automatic calculation of age cycle..! Below mentioned example for your reference..!
I have to convert data from below mentioned age format to cycle (below 20, 21-25, 26-30,30 above.)
25yrs7m8days
31yrs11m3days
18yrs4m21days
Hi,
Suppose your data are in column D, please try to enter the following formula in cell E1 and then copy it down along the column:
=IF(VALUE(LEFT(D1,SEARCH("yrs",D1)-1))<=20, "20 and below", IF(VALUE(LEFT(D1,SEARCH("yrs",D1)-1))<=25, "21-25", IF(VALUE(LEFT(D1,SEARCH("yrs",D1)-1))<=30, "26-30", "30 and above")))
Hope this is what you need.
Hi,
I have 3 columns and I have to create another column by concatenating :
Last Name First Name DOB Alt
Addison Ashley 10/12/2012 AddAO12
Aguilar Jayden 7/2/2013 AguJ702
Adkins Skyhe 12/28/2012 AdkSD28
So in the Alt column I know how to get LastName and firstName but need to display month in letters as O for 10(october) , D for 12 (December) if birthday month is 2 digits and if single digit month the single digit has to be returned.
Thank you
Hi, Reena,
I'm afraid there is no easy way to get the output you need using a formula. You'd better use a macro. I'm sorry, we can't help you with this, please try to find the solution in VBA sections on mrexcel.com or excelforum.com.
I track mileage and other data on an Excel spreadsheet. I know the round trip mileage for each city I go to. I simply want it to autofill the round trip mileage of the city I type in. For example:
If, in row 10, I type W.Roxbury in column A, I want Excel to autofill 58 in column B. If, in row 11, I type Sturbridge in column A, I want Excel to type 116 in column B, and so forth for each individual row.
Coll A Coll B Coll C
Y Y
N N
N Y
I am trying to write an IFs formula for Coll C, that returns a Y if Coll A & B are Y, a blank if Coll A & B are N, and a N if Coll A is N and Coll B is Y.
Hello, Joe,
Please try the following formula:
=IF(AND(A1="Y",B1="Y"),"Y",IF(AND(A1="N",B1="Y"),"N",""))
You haven' t mentioned what the formula should return if Coll A is Y and Coll B is N. Currently the formula returns nothing in this case. If you want to see N or something else in column C when this condition is met, you can just add IF(AND(A1="Y",B1="N"),"N" before the double quotes at the end of the formula above.
Hope this will help.
I'm trying to populate a manning layout.
what formula would work across multiple tabs?
Example:
cell 1 = 1 for shift
cell 2 = 4 for position
cell 3 = 6 for area
cell 1 & 3 moves name to spreadsheet in correct tab and cell 2 is what cell in that tab it populates.
Please explain how can i have three answer using two columns as follows,
Ex: in 1st column = column A and B if >0 = "yes", if not "NO"
if it is a figure only in Column B the answer should be " Loading",
Hi,
in cell A1 value is 1000. In cell A2 value is 12. now I want formula in Cell A3. Formula would be like that.
IF CELL VALUE OF A2 12 BUT 18 BUT <=24, THEN A1*3%.
Greetings,
please guide me on how to create the following formula: if the value of the cell is in (minus) then multiply by the value of a different cell. Let me know if it is possible.
Thanks alot
=IF(D177<0,D177*F177,"")
Hi
Kindly explane what is the meaning of this formula =if(P12<=0,R12,P12*R12)
Greeting sir
Kindly explane what is the meaning of this formula =if(A12=false,C12,A12*C12)
Thank you
Can I return a text statement and a cell value in the return value of an If statement? For example if the logical test is false can my false value be a text with a cell value? i.e value_if_false is "Please change parameter to D19" where D19 is a cell value.
syntax =if(D18=D19,No change necessary, Please change value to D19)
Where D19 is the actual cell value that gets displayed and not the text D19
What is the functionality of Marcor in excel, how macro creation help us in sorting data?
Hi,
I have a set of four cells that return either text or a number. I have a warning that arises if ALL of the cells are text, otherwise no warning (simple if statement - =IF(AND(ISTEXT(B5),ISTEXT(C5),ISTEXT(D5),ISTEXT(E5)),"WARNING:",...). For the false argument, I need something very specific. I need to produce another warning if ONLY ONE of the cells listed is a number, but as soon as two or more are numbers, then I want to do something else. How do I say, "Do this when ONLY EXACTLY ONE of the cells is a number"?
I want a formula where
Date in A column if equal to or less than B column, it should say Correct. If B column is greater than 2 then it should return "NO"
A B C
29-Jan-18 30-Jan-18 Yes
29-Jan-18 1-Feb-18 No as the difference is more than 2 days
29-Jan-18 2-Feb-18 No
29-Jan-18 31-Jan-18 Yes
Hello,
Please try to enter the following formula in cell C1 to get the result you need:
=IF((B1-A1)>2,"No","Yes")
Then just copy the formula down along column C.
Hope this will help.
I need to calculate the amount of drivers
Duration is 8 hrs if he done more than 8hrs we pay 100/hr must with in the 8 we pay 68.75/hr
Example if he done 10hr a day we pay 8*68.5 +2*100
=IF(A1>8,(A1-8)*100+8*68.75,A1*68.75)
How to do formula for below cases.
I have planned and Actual Dates (both have start & end date).
(i) IF there is a value at the (H37) end date (actual) = COMPLETED
(ii) IF (E37) end date planned = (H37) end date actual = ON TRACK
(iii)IF only have(G37) start date (actual) = WORK IN PROGRESS
(iv)IF (H37) end date (actual) is later than (E37) end date planned = DELAYED
ELSE put as NOT STARTED
=IF(H37,"COMPLETED",IF(H37=E37,"ON TRACK",IF((H37-E37)>1,"DELAYED",IF(G37,"WIP","NOT STARTED"))))
Item (i) to (iii) works but not for item (iv). Can someone help please...
How can I do the first cell to be written the sequence of the letter and second cell to become the letter with a formula?
ie.
cell1=1 cell2=A
cell1=5 cell2=E
cell1=8 cell2=H
Such a formula would be ok:
A. B
1. =if(A1=1, B1=A)
If necessary I can send you our data.
A B
JANUARY - 1830
JANUARY - 1430
FEBRUARY - 300
MARCH - 200
FEBRUARY 500
I NEED A SUMMARY TABLE THROUGH IF CONDITIONS LIKE
JANUARY = 3260
FEBRUARY = 800
MARCH = 500
Hello, Suresh,
You can fulfill you task using our Consolidate tool which is a part of Consolidate Worksheets Wizard. You just need to select a sheet with your data table on step 1, choose the Sum function and specify to use the left column label for consolidation on step 2 of the Wizard, and finally choose where you'd like to place the results.
how to creat if formula i am slect month and auto fill date in slect cell
I want to use an =IF(AND(TODAY() function to highlight rows that represent current week data.
Each row has A date for beginning and ending of the week so =IF(TODAY() falls between x and y date, I would like it to highlight that row but my formula isn't work.
B26 is the beginning of the week date
C26 is the ending of the week date
=IF(AND(TODAY()>=B26,TODAY()<=C26),$A26:$Q26)
I need help with a formula. Checking a TEXT field for one of 3 conditions:
If cell = "Joe", display 3000
If cell = "any other name" display 1500
if cell is blank leave blank
Something like this: =IF(F12="Joe",3000), If (F12 IsBlank 0, 1500)
cant get it to work
Hi, I want to apply formula for below chart.
such as, if full test then will add 180 with report out date, if psr then will add 90 with report out date.pls help to share formula.
REPORT OUT DATE VALID DATE COMMENTS
16-Nov-17 14-Feb-18 PSR
11-Aug-18 7-Feb-19 full gpt
i would like to see the date when the number shows 0 in another worksheet. please help
How can i use if to return the words for the range of figures as below
From 1.00 To 2.40 Excellent
Over 2.40 To 3.00 Very Good
Over 3.00 To 3.60 Good
Over 3.60 To 4.00 Fair
Over 4.00 To 5.00 Poor
i need a function that can return text for me for a range of figures
to From 1.00 To 2.40 Excellent
Over 2.40 To 3.00 Very Good
Over 3.00 To 3.60 Good
Over 3.60 To 4.00 Fair
Over 4.00 To 5.00 Poor
Owen, I was able to use the following function:
=IF(A2<=2.4,"Excellent",IF(A2<=3,"Very Good",IF(A2<=3.6,"Good",IF(A2<=4,"Fair",IF(A2<=5,"Poor")))))
where "A2" is the cell being evaluated.
Can I get IF THEN to work for multiple values? e.g. If A1 5, <10, then B3 = 1 etc. Its more than just A1/10, theres a range. If this function doesn't work, is there any other function I can use?
Hello - i need help with a function.. will try and explain it below;
i have 3 columns on my table and I would like the value in the 'Coat 2' column to equal the value (a paint name in this case) in the 'Coat 1' column - IF the value in the 'No/Coats' column is 2
i.e.
No/Coats Coat 1 Coat 2 Coat 3
2 Acrylic Acrylic
1
I think you're looking for this:
A1 is Start Date
B1 is Finish Date
C1 is where formula entered to display Started, Completed, or Blank
=IF(A1&B1="","",IF(B1"","Completed",IF(A1"","Started")))
Hi,
If I need to make a calculation for a start date and a finish date to display completed when both cells have the dates entered, a blank cell if neither start/finish has dates entered and open if only start date is entered but finish is not.
How would I do this, I've scoured the internet and I cannot resolve the issue.
much appreciated
do you know the formula that i can use if example, 121 is negative and i wrote (40 - 2 = 38) then how can i write (-121 - 2 = -123) the subtract sign to addition sign?
Hi!
I am not sure I understand the question. In Excel, you enter negative numbers as usual by typing the minus sign in front of them. For example, you you type =-121-2 in a cell (with no spaces), and press the Enter key, you will get -123 as the result.
MY IF CONDITION IS BASED ON TEXT...HOW CAN I PUT A FORMULA
Hi!
Here are a few examples of IF formulas with text-based conditions:
Excel IF function examples for text values
Hey Svetlana
so I am using a if statement for the task i am doing and it is not working. basically i have over 100 villas and some of the villas have multiple units in them. I have assets within those units so what i want to be able to do is i have a table that says villa 1 has units 1 and 2 and villa 2 has units 3 and 4 etc. If I have 10 assets in unit 1 and 5 assets in unit 3 what formula should i use to automatically choose the villa those assets are in
Hi,
On IF formula, I need to enter a range say 1% to 24%, I need "Won", then 25% to 50%, I need "Entered"
I have typed =IF(N3>1%,<24%,"Won"....but it is not working..
Please help..
I am learning too. But try this:
=If(N3<=24,"WON",IF(N3<=50,"ENTERED"))
OR
=If(N3="","",if(N3<=24,"WON",IF(N3<=50,"ENTERED","")))
This means, if N3 is blank, return blank in cell, if N3 is less than or equal to 24%, return "WON". If cell N3 is less than 50% return "ENTERED" and blank if above 50%
Thank you.
Thank you David. I was able to adapt your formula for my sheet. =IF(E5="","",IF(E5>97,"Pass","Fail"))
I was looking everywhere online for a formula that would indicate a pass or fail for a > or < Value and that could recognise blank cells. Couldn't find anything that would work until reading your post in this forum. You are a life saver!!!!
I'm trying to get a formula that will calculate results for annual, monthly, fortnightly and weekly expenses. If A2 is Annual divide A1 by 12, but if Fortnightly multiply A1 by 26 then divide by 12 etc.
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,
Been searching but am unable to find a solution to my particular problem and would like to seek help as it's driving me nuts!
Three conditions need to be met, but I'm unsure about the sequence and correct syntax:
1. =IF(A1>A2,"PAYMENT LATE") - A1 & A2 contain dates. PAYMENT LATE displays in A3.
Conditions 2 and 3 are nested together. I'm trying to determine that IF cell A1 is BLANK AND the date in A2 is > than today, then "PAYMENT LATE", otherwise, "PAYMENT ON TIME" to be displayed in A3.
Hope you can help.
Hello,
If I understand your task correctly, please try the following formula:
=IF(OR(AND(ISBLANK(A1),A2>TODAY()),A1>A2),"PAYMENT LATE","PAYMENT ON TIME")
Hope this will help.
from a spreadsheet data i want to set if condition for setting a range if the data is > 5.3 than the value will be 5.3 if less then 5.3 than that would be as it was. pls let me know.
regards
kamal
example
7.2 5.3
3.2 3.2
A B C B
5.3
Solution
5.3
Do this:
=If(A1>5.3,5.3,A1). This means that if the value in cell A1 is greater than 5.3, return the value 5.3 or else (if below), return the value in cell A1.
Iam learning too.
Thanks