Processors are divided to the Intel and AMD offerings. I will comment on both of them for the reason I stated in the prologue. These are the basis of the system.
Concerning AMD processors, they are usually much cheaper than their Intel counterparts. Unfortunately AMD does not have very good high end offerings for the top of the market but in my opinion they rule the lower end of the market because of the very low prices their modest CPUs have and their very good overclocking abilities.
For a basic and budget AMD CPU, I would recommend an Athlon XP 2400+ for both cases. This is one of the cheapest CPUs and will do the job just fine for now and the near future.
For an overclocker which wants the best value for money, the Athlon XP 1700+/1800+ with Thoroughbred B core is the choice. Extremely cheap and with excellent potential, these CPUs can outrun even the highest end models that AMD offers when held by the right hands.
For a high end system, the Athlon XP (Barton) 3000+ is the choice. Sure, there is the 3200+ all over the market but it is severely overpriced in my opinion to worth the money. The 3000+ is fast enough to do everything just right.
For the top end overclockers, the Athlon XP (Barton) 2500+ is a good choice. The higher models are useless since they all have the same potential when it comes to overclocking. The 2500+ will overclock very well and perform very respectfully when held by skillful hands.
For workstation/office PCs, the Athlon XP (Barton) 2500+ is a good choice. The extra cache that they carry helps enough with applications commonly used on those systems. Depending on the load of work the system will have to do, higher speed Bartons may help and do better. For the average system the 2500+ is plenty in my opinion.