Nested IF statement

[kwvh]

Is there a maximum number of 'IF" in a nested formula?

The reason I ask is that the following works until I get to 35.

Code: Select all

IF(AF2=44,"Friday", IF(AF2=43, "Saturday", IF(AF2=42, "Sunday", IF(AF2=41,"Monday", IF(AF2=40, "Tuesday", IF(AF2=39, "Wednesday", IF(AF2=38, "Thursday", IF(AF2=37, "Fri", IF(AF2=36, "Sat", IF(AF2=35, “Sun”, IF(AF2=34, “Mon”, IF(AF2=33, “Tue”, IF(AF2=32, “Wed”, IF(AF2=31, “Thurs”, IF(AF2=30, “Fr”)))))))))))))))

Is there an error in the formula, or have I maxed out?

Thanks!

Ken

[kwvh]

Oops, found the answer via Google. I guess I get too dependent on this excellent group.

FWIW - from http://www.mrexcel.com/forum/showthread.php?t=10228" onclick="window.open(this.href);return false;
The maximum number of nested IFs for all versions of Excel is 7. This is stated explicitly in the Excel Help topic for "IF worksheet function"...

"Up to seven IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests."

The outermost IF in the example above would not be counted as a nested IF because it's not contained within another IF function. An explanation of nesting can be found in the Excel Help topic for "About multiple functions within functions, or nesting".

[HansV]

What are you trying to accomplish? There may be an easier formula.

[kwvh]

Hans,
Thanks for reply. I used a vlookup to accomplish the goal.
There may be a better solution, but worked.
Thanks for followup.

[HansV]

I was thinking about VLOOKUP! Much more efficient than endless nested IFs.

[kwvh]

That's kind of scary! Me using a solution that you would use! Maybe I am learning some things after all.

Thanks, Hans.