IF Function

[SOT]

I am using Excel 2000. Is there a way around the 7 limit on a nested IF?

Thanks

 

[Jerry W. Lewis]

The limit is hard-coded and cannot be changed (still there in 2003).
Often people can restructure what they are trying to do to use a lookup
table.

Jerry

 

[RagDyeR]

It's good practice to re-configure your formulas, to perhaps a datalist,
which can be accessed by Lookup functions, if you have that amount of
variables.

However, there are workarounds.

Check out this web page of Chip Pearson:

http://www.cpearson.com/excel/nested.htm

--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

I am using Excel 2000. Is there a way around the 7 limit on a nested IF?

Thanks

 

[Ola]

I often use CHOOSE:

Example 1:
IF A4 = 1 Then 11
Else If A4 = 2 Then 22
Else If A4 = 3 Then 33
Else If A4 = 4 Then 44
=CHOOSE(A4,11,22,33,44)

Example 2:
IF AND(A4 >= 1, A4 < 10) Then "1-9"
Else If AND(A4 >= 10, A4 < 20) Then "10-19"
Else...
=CHOOSE(1 + A4 >= 1 + A4 >= 10 + A4 >= 20), "Error", "1-9", "10-19", ">20")


Ola Sandstrom