tech on the net

MS Excel: Nested IF Functions (WS)

Learn how to nest the Excel IF function with syntax and examples.

Description

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.

Syntax

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

Parameters or Arguments

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.

Note

  • This Nested IF function syntax demonstrates how to nest two IF functions. You can nest up to 7 IF functions.

Applies To

The Nested IF function can be used in the following versions of Microsoft Excel:

  • Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Excel Function

The Nested IF function can be used in Microsoft Excel as the following type of function:

  • Worksheet function (WS)

Example (as Worksheet Function)

Let's look at an example to see how you would use a Nested IF examples and explore how you would use the Nested IF function as a worksheet function in Microsoft Excel:

Microsoft Excel

Based on the spreadsheet above, the following Excel Nested IF examples would return:

=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 Microsoft 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 Microsoft 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 Microsoft 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:I have Excel 2000. If cell A2 is greater than or equal to 0 then add to C1. If cell B2 is greater than or equal to 0 then subtract from C1. If both A2 and B2 are blank then equals C1. Can you help me with the IF function on this one?

Answer: You can write a nested IF statement that uses the AND function and the ISBLANK function as follows:

=IF(AND(ISBLANK(A2)=FALSE,A2>=0),C1+A2, IF(AND(ISBLANK(B2)=FALSE,B2>=0),C1-B2, IF(AND(ISBLANK(A2)=TRUE, ISBLANK(B2)=TRUE),C1,"")))

Question:How would I write this equation in Excel? If D12<=0 then D12*L12, If D12 is > 0 but <=600 then D12*F12, If D12 is >600 then ((600*F12)+((D12-600)*E12))

Answer: You can write a nested IF statement as follows:

=IF(D12<=0,D12*L12,IF(D12>600,((600*F12)+((D12-600)*E12)),D12*F12))

Question:I have read your piece on nested IFs in Excel, but I still cannot work out what is wrong with my formula please could you help? Here is what I have:

=IF(63<=A2<80,1,IF(80<=A2<95,2,IF(A2=>95,3,0)))

Answer: The simplest way to write your nested IF statement based on the logic you describe above is:

=IF(A2>=95,3,IF(A2>=80,2,IF(A2>=63,1,0)))

This formula will do the following:

  • If A2 >= 95, the formula will return 3 (first IF function)
  • If A2 < 95 and A2 >= 80, the formula will return 2 (second IF function)
  • If A2 < 80 and A2 >= 63, the formula will return 1 (third IF function)
  • If A2 < 63, the formula will return 0

Question:I'm very new to the Excel world, and I'm trying to figure out how to set up the proper formula for an If/then cell.

What I'm trying for is:

  • If B2's value is 1 to 5, then multiply E2 by .77
  • If B2's value is 6 to 10, then multiply E2 by .735
  • If B2's value is 11 to 19, then multiply E2 by .7
  • If B2's value is 20 to 29, then multiply E2 by .675
  • If B2's value is 30 to 39, then multiply E2 by .65

I've tried a few different things thinking I was on the right track based on the IF, and AND function tutorials here, but I can't seem to get it right.

Answer:To write your IF formula, you need to nest multiple IF functions together in combination with the AND function.

The following formula should work for what you are trying to do:

=IF(AND(B2>=1, B2<=5), E2*0.77, IF(AND(B2>=6, B2<=10), E2*0.735, IF(AND(B2>=11, B2<=19), E2*0.7, IF(AND(B2>=20, B2<=29), E2*0.675, IF(AND(B2>=30, B2<=39), E2*0.65,"")))))

As one final component of your formula, you need to decide what to do when none of the conditions are met. In this example, we have returned "" when the value in B2 does not meet any of the IF conditions above.