Ideally, I think you would want to stay away from "mega formulas". I think the most common nested formula I see is a long string of IF statements, when it probably could have been done much easier using either a CHOOSE or LOOKUP function. That said, here's some basics:
pre-2007 XL you are limited to 7 nested functions. Note that this is 7 nested, not 7 total. Each "branch" your formula makes, most likely using an IF function, allows you to put in more functions.
Most functions allow you to give either a hard coded string, a cell reference, or another function that supplies the necessary information for an arguement. Possible exceptions to this are when the argument specifically asks for a "ref" like the RANK function.
Finally, the main idea behind nesting formulas is to build the individual pieces of information that you need to achieve your overall goal. It's sometimes easier to start off by writing a bunch of simple formulas in different cells, and then combining them later.
