A Core2Duo E6420
[email protected] paired with 4 gigs of DDR2-800 and a RAID array powers my big desktop. It's good when I have a big job to do. That thing can chew through just about anything without a second thought.
My wife has a E6550 w/ 3 gigs of 533. It's a little slower but I think that's because it runs Vista and it's a Dell. She got it before I married her or we wouldn't own it.
Our file server has a 2.8 GHz P4 (1M L2/400FSB without HyperThreading) with a gig of 2700. It gets the job done and then some. I don't know about benchmarks, but when I compiled a Linux kernel a few weeks back it did it in less time that I anticipated.
The newest addition to the network is a little Mini-ITX box equipped with a dual core Atom at 1.6 GHz and a gig of DDR2-800. This little jewel takes EM64T instruction set and is Hyperthreaded. It's the heart of the entertainment system and with a TV/Radio tuner, it does a fine job. It plays and records audio and video in all formats up to 720p.
Finally, my little EeePC 900 has a Celeron M in it that runs at 630Mhz on battery, 900Mhz on AC, and 990 Overclocked. It has two gigs of 533. Thanks to a carefully tuned and pruned loadset based on XP Pro it almost never slows down. I remote into it to surf during the day at work and to keep an eye on things via it's webcam. I can watch video and surf around all day long on that thing without it missing a beat. It's just a question of choosing your applications very carefully for size and speed.
So, to answer the question in the original post, I think a P4 is a great chip and unless you have a hardware problem, I'd just make sure you've got your system regularly maintained and pruned of software that leeches power Maybe upgrade your RAM if you can get a hold of some cheap chips. There's nothing that computer won't do for the average home/office user.
However, if you want to go all out with a new box, I'd wait a couple months and get an i7 system, several gigs of DDR3, a solid motherboard and videocard and a couple 10k rpm drives in a raid setup. Drop a grand and build it yourself.