I actually have exactly that setup (shitty Gateway with Turion and 5400 rpm hdd, dirt cheap).
It works well enough for what I need it to do - surf the web, Office, Facebook, Youtube, Plants vs Zombies, movies, - heck it has an HDMI port. And I've been able to do some light programming on it without trouble.
Only thing it really doesn't do is non-casual games.
You say that as though Turions and the Core series were bad. I'm currently running a Phenom 2 x6 3.3GHz.. and guess what? For the majority of tasks, it is no better than my previous Core 2 Quad 2.5GHz. And the Turion processor is no slouch; I know this from experience.
Can these chips power a supercomputer? No. Do 99% of people need them to? Not at all.