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.