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 4
Answer: 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 $75
Answer: 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 the ISNUMBER 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 400
Answer: 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 5000
Answer: 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))
