Heya,
It depends greatly on your display and the resolution you want to game at. A lot of people have LCD's, 720p HDTV's, 1080p HDTV's that they're gaming on. These have native resolutions. These devices look their best when using their native resolution. For some 22" LCD's, that resolution is 1680x1050. For 1080p HDTV's for example, that native resolution is 1920x1080. Now, consider what resolution people game at. Unless you have a powerful GPU, you're not going to play many games smoothly at those higher resolutions. There are a lot of games that can play at those resolutions on a good modern card, just fine. But there are a good number of games that simply don't let you turn on all the goodies while playing at that resolution. This is why SLI and CF come in handy. You don't have to be an enthusiast. An enthusiast is someone who buys three GTX280's and goes TRI SLI for $1,000. Where it's overkill for everything out there, and it's essentially just bragging rights at that point. You can comfortably SLI and CF though for the sake of being able to play your games at the wonderful native resolutions of your display without spending enough to buy a used car.
For example, the HD4870 is now only $199 ($179) at NewEgg. The GTX260 is only $199 as well at NewEgg. Both of these are fantastic single card solutions that will play most games at those top resolutions perfectly well (just not all of them perfectly well with things like AA enabled). On the other hand, one could get two 9800GT's for about $99 each ($198) or two HD4850's for about $120 each ($240). These two SLI and CF solutions not only out perform the above single card solutions, but they do so more importantly at those top resolution settings and with all the candy enabled.
So which do you buy? A single card? Or dual? Again, it totally is up to your display in my mind. If you're playing games at resolutions below 1600x1200, you probably shouldn't bother with SLI or CF. Dual cards really only benefits you when using them at very high resolutions to maintain performance. At lower resolutions, they do next to nothing for their cost, so it's better to use a good powerful single videocard.
Very best,