Excel nested If trick

Tags: Excel nested If trick, Nesting more than 7 IF statements in an Excel function, Alternatives To Nested IF Functions, How can I nest more than 7 IF statements together?, Simplified nested If.

Assume the following simplified case: we want cell B1 to have certain value based of the value of cell A1.

The psuedo code is:

If A1=10 then 

B1=A1*2

Elseif A1=20 then

B1=A1^2

Elseif A1=30 then

B1=3*A1^3

Else

B1=0

EndIf

Excel formula code placed in cell B1:

=IF(A1=10,A1*2,IF(A1=20,A1^2,IF(A1=30,3*A1^3,0)))

The previous formula called "Nested If" which means If statement inside another If statement.

For sure this Excel formula seems to be complex and it will get complicated by increasing the number of cases. In addition, Excel can't accept more than 7 nested If statments.

The tricky solution (without using VBA) is to use separate If statements summed together instead of nested If.  See the formula below

=IF(A1=10,A1*2,0)+IF(A1=20,A1^2,0)+IF(A1=30,3*A1^3,0)

The above solution is easy to read, revise, and edit later.

Try this solution and you will get the same result.