Macs are good for somethings. If someone would sell me a dual G4 or G5 for really cheap(almost free) Id gladly throw it in a closet and use it for a F@H box. The PowerPC is a very powerful platform for a lot of apps, does much more work per cycle than most platforms.
Macs in general arent really the most versatile PCs in general. I'd never be able to use one for my main workstation because I do software dev stuff for my job. Im sure there are tons of visual compilers for all types of languages for Macs so dont respond and list them all. But face it, not many large business's use Macs, atleast Ive never seen any.
With games, sure there are tons of games you CAN play. But Im getting really F'ing addicted to HL2 and I want to try Halo2, so in that respect Mac's are out also.
In terms of upgrades and OCing, sure it can be done but it seems like a waste to me. To quote from the same links above:
While a G4 is a pretty fast CPU Im sure its not faster than a 3200+ Winchester thats listed on pricewatch for 200$ at the time that xlr8yourmac article was posted...
As for OCing this is the first page I found from the same site:
http://www.ethernetworks.de/imac_g4_usb2_overclocking.html
And no soldering a PCB to OC isnt that nuts. Go over to forums.2cpu.com and you'll find tons of people doing it with PC-DLs, with much better success I might add. Getting a 1ghz proc to 1.25 is ok. But at the price you are paying for that thing I wouldnt be hooting and hollering.
If you got a mac for free then sure why not, OC it and mod ti all you want. It can be done and its not a waste. But if you are going to actually put money into a system that you want to use the computer for a wide scope of stuff, not just music or video editing, or not just a folding box, there are much much better values out there, even if someone is selling the mac for cheap.