Ok, I'll admit I use it when needed. As far as to how it works, there is a little learning curve, but once you get it, I find that this is how it works:
-Good singer, but little intonation fixes that might keep an otherwise great performance from being "there"-works well.
-Bad singer-Wont work well no matter what in my opnion. Even when it is only barely working, I can hear the autotune, it sorta changes the tone of the vocals to my ears.
Are the results worth it? Well, it is pretty much the industry standard vocal pitch "fixer" if that means anything. Is it worth it as in, it allows lesser and lesser talent to make "hit" records; no way in hell. If you work with talent that actually has talent, but just might need a little fix here and there, then I would say it is worth it.
Sorry for the rant, I tried to keep it semi-informative. Oh yeah, don't really bother with the automatic mode...it works very rarely to my satisfaction.
I've been able to get really great results with Auto-Tune 3 by leaving the tracking at it's default setting of 25 and then setting the retune to 48. At that point simply select the type of voice (usually alto/tenor for males and soprano for females) and set your key correctly. I've found the vibrato setting to make things sound terrible so I leave it set to none.
That's all in automatic mode btw and with a pretty good singer if can really straighten things up.