It is possible to nest multiple IF functions within one Excel formula. You can nest up to 7 IF functions to create a complex IF THEN ELSE statement.
The syntax for the nesting the IF function is:
IF( condition1, value_if_true1, IF( condition2, value_if_true2, value_if_false2 ))
This would be equivalent to the following IF THEN ELSE statement:
IF condition1 THEN
value_if_true1
ELSEIF condition2 THEN
value_if_true2
ELSE
value_if_false2
END IF
This syntax example demonstrates how to nest two IF functions. You can nest up to 7 IF functions.
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="10X12",120,IF(A1="8x8",64,IF(A1="6x6",36))) would return 120 =IF(A2="10X12",120,IF(A2="8x8",64,IF(A2="6x6",36))) would return 64 =IF(A3="10X12",120,IF(A3="8x8",64,IF(A3="6x6",36))) would return 36
Frequently Asked Questions
Question: In Excel, I need to write a formula that works this way:
If (cell A1) is less than 20, then multiply by 1,
If it is greater than or equal to 20 but less than 50, then multiply by 2
If its is greater than or equal to 50 and less than 100, then multiply by 3
And if it is great or equal to than 100, then multiply 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 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%),""))
