IF CONDITION


In Excel, the If function returns one value if a specified condition evaluates to TRUE, or another value if it evaluates to FALSE.The syntax for the If function is:If( condition, value_if_true, value_if_false )condition is the value that you want to test.value_if_true is the value that is returned if condition evaluates to TRUE.value_if_false is the value that is return if condition evaluates to FALSE.

Applies To:

Excel 2007, Excel 2003, Excel XP, Excel 2000

For Example:

Let's take a look at an example:Based on the Excel spreadsheet above:


=If(A1>10, "Larger", "Smaller")would return "Larger"=If(A1=20, "Equal", "Not Equal")would return "Not Equal"=If(A2="Tech on the Net", 12, 0)would return 12
Learn how to nest multiple If Functions. (up to 7)Learn how to nest multiple If Functions. (more than 7)

Frequently Asked Questions

Question: In Excel, I'd like to use the If function to create the following logic:if C11>=620, and C10="F"or"S", and C4<=$1,000,000, and C4<=$500,000, and C7<=85%, and C8<=90%, and C12<=50, and C14<=2, and C15="OO", and C16="N", and C19<=48, and C21="Y", then reference cell A148 on Sheet2. Otherwise, return an empty string.Answer: The following formula would accomplish what you are trying to do:=IF(AND(C11>=620, OR(C10="F",C10="S"), C4<=1000000, C4<=500000, C7<=0.85, C8<=0.9, C12<=50, C14<=2, C15="OO", C16="N", C19<=48, C21="Y"), Sheet2!A148, "")Question: In Excel, I'm trying to use the If function to return 0 if cell A1 is either < 150,000 or > 250,000. Otherwise, it should return A1.Answer: You can use the OR function to perform an OR condition in the If function as follows:=IF(OR(A1<150000,A1>250000),0,A1)In this example, the formula will return 0 if cell A1 was either less than 150,000 or greater than 250,000. Otherwise, it will return the value in cell A1.Question: In Excel, I'm trying to use the If function to return 25 if cell A1 > 100 and cell B1 < 200. Otherwise, it should return 0.Answer: You can use the AND function to perform an AND condition in the If function as follows:=IF(AND(A1>100,B1<200),25,0)In this example, the formula will return 25 if cell A1 is greater than 100 and cell B1 is less than 200. Otherwise, it will return 0.Question: In Excel, I need to write a formula that works this way:If (cell A1) is less than 20, then times it by 1,
If it is greater than or equal to 20 but less than 50, then times it by 2
If its is greater than or equal to 50 and less than 100, then times it by 3
And if it is great or equal to than 100, then times it by 4Answer: You can write a nested IF statement to handle this. For example:=IF(A1<20, A1*1, IF(A1<50, A1*2, IF(A1<100, A1*3, A1*4)))Question: In Excel, I need a formula in cell C5 that does the following:IF A1+B1 <= 4, return $20
IF A1+B1 > 4 but <= 9, return $35
IF A1+B1 > 9 but <= 14, return $50
IF A1+B1 > 15, return $75Answer: In cell C5, you can write a nested IF statement that uses the AND function as follows:=IF((A1+B1)<=4,20,IF(AND((A1+B1)>4,(A1+B1)<=9),35,IF(AND((A1+B1)>9,(A1+B1)<=14),50,75)))Question: In Excel, I need a formula that does the following:IF the value in cell A1 is BLANK, then return "BLANK"
IF the value in cell A1 is TEXT, then return "TEXT"
IF the value in cell A1 is NUMERIC, then return "NUM"Answer: You can write a nested IF statement that uses the ISBLANK function, the ISTEXT function, and theISNUMBER function as follows:=IF(ISBLANK(A1)=TRUE,"BLANK",IF(ISTEXT(A1)=TRUE,"TEXT",IF(ISNUMBER(A1)=TRUE,"NUM","")))Question: In Excel, I want to write a formula for the following logic:If R1 AND R2 <0.3 AND R3<0.42 THEN "OK" OTHERWISE "NOT OK"Answer: You can write an IF statement that uses the AND function as follows:=IF(AND(R1<0.3,R2<0.3,R3<0.42),"OK","NOT OK")Question: In Excel, I need a formula for the following:IF cell A1= PRADIP then value will be 100
IF cell A1= PRAVIN then value will be 200
IF cell A1= PARTHA then value will be 300
IF cell A1= PAVAN then value will be 400Answer: You can write an IF statement as follows:=IF(A1="PRADIP",100,IF(A1="PRAVIN",200,IF(A1="PARTHA",300,IF(A1="PAVAN",400,""))))Question: In Excel, I want to calculate following using an "if" formula:if A1<100,000 then A1*.1% but minimum 25
and if A1>1,000,000 then A1*.01% but maximum 5000Answer: You can write a nested IF statement that uses the MAX function and the MIN function as follows:=IF(A1<100000,MAX(25,A1*0.1%),IF(A1>1000000,MIN(5000,A1*0.01%),""))Question: In Excel, I am trying to create an IF statement that will repopulate the data from a particular cell if the data from the formula in the current cell equals 0. Below is my attempt at creating an IF statement that would populate the data; however, I was unsuccessful.=IF(IF(ISERROR(M24+((L24-S24)/AA24)),"0",M24+((L24-S24)/AA24)))=0,L24)The initial part of the formula calculates the EAC (Estimate At completion = AC+(BAC-EV)/CPI); however if the current EV (Earned Value) is zero, the EAC will equal zero. If the outcome is zero, I would like the BAC (Budget At Completion), currently recorded in another cell (L24), to be repopulated in the current cell as the EAC.Answer: You can write an IF statement that uses the OR function and the ISERROR function as follows:=IF(OR(S24=0,ISERROR(M24+((L24-S24)/AA24))),L24,M24+((L24-S24)/AA24))Question: I have been looking at your Excel IF, AND and OR sections and found this very helpful, however I cannot find the right way to write a formula to express if C2 is either 1,2,3,4,5,6,7,8,9 and F2 is F and F3 is either D,F,B,L,R,C then give a value of 1 if not then 0. I have tried many formulas but just can't get it right, can you help please?Answer: You can write an IF statement that uses the AND function and the OR function as follows:=IF(AND(C2>=1,C2<=9, F2="F",OR(F3="D",F3="F",F3="B",F3="L",F3="R",F3="C")),1,0)Question:In Excel, I have a roadspeed of a car in m/s in cell A1 and a drop down menu of different units in C1 (which unclude mph and kmh). I have used the following IF function in B1 to convert the number to the unit selected from the dropdown box;=IF(C1="mph","=A1*2.23693629",IF(C1="kmh","A1*3.6"))However say if kmh was selected B1 literally just shows A1*3.6 and does not actually calculate it. Is there away to get it to calculate it instead of just showing the text message?Answer: You are very close with your formula. Because you are performing mathematical operations (such as A1*2.23693629 and A1*3.6), you do not need to surround the mathematical formulas in quotes. Quotes are necessary when you are evaluating strings, not performing math.Try the following:=IF(C1="mph",A1*2.23693629,IF(C1="kmh",A1*3.6))Question:For an IF statement in Excel, I want to combine text and a value.For example, I want to put an equation for work hours and pay. If I am paid more than I should be, I want it to read how many hours I owe my boss. But if I work more than I am paid for, I want it to read what my boss owes me (hours*Pay per Hour).I tried the following:=IF(A2<0,"I owe boss" abs(A2) "Hours","Boss owes me" abs(A2)*15 "dollars")Is it possible or do I have to do it in 2 separate cells? (one for text and one for the value)Answer: There are two ways that you can concatenate text and values. The first is by using the & character to concatenate:=IF(A2<0,"I owe boss " & ABS(A2) & " Hours","Boss owes me " & ABS(A2)*15 & " dollars")Or the second method is to use the CONCATENATE function:=IF(A2<0,CONCATENATE("I owe boss ", ABS(A2)," Hours"), CONCATENATE("Boss owes me ", ABS(A2)*15, " dollars"))