Antivirus software quality is a controversial subject - if I were to ask 10 different PC technicians which antivirus program to use, I wouldn't be surprised to get 10 different answers. One factor is that the options are changing so quickly - if you don't look at all the alternatives often, it can be easy to lose track of which products are most competitive. Additionally, there are many subjective considerations that people may weigh differently - just a few of the questions that might be important for a given user are: How many current viruses does the software detect? How much will it slow down my system? How much does it cost? Is it going to annoy me with irrelevant pop-ups, or will it keep out of my way? How well does it guess about new virus threats that aren't documented in it's virus definitions database?
That said, the unfortunate truth behind antivirus applications is that none of them are perfect - new viruses are being developed every day and some won't be detected until they've been 'caught' and studied by the antivirus industry. Since you can never fully trust any antivirus program to catch recent (or sometimes even old) viruses, the first line of defense is watching carefully what you click in your web browser or email client. The main thing to watch out for is file downloads or program installation. If a window pops unexpectedly asking you to download or install a file or program, it's a good bet it's not something you want.
But still, you'll probably want antivirus software
Beyond that first line of defense, running an antivirus program is usually a prudent choice. Personally, I run Microsoft Security Essentials on my Windows PCs. I think many people have doubts about the competence of free alternative antivirus solutions such as AVG, Avast, and ClamAV just because the companies or people behind them aren't particularly well-known, and because the perceived value of a product can be lower when it's given away for free. Microsoft is definitely a big name behind Security Essentials and, if you think about it, who better to make a Windows antivirus solution than the people who made the Windows operating system and know exactly how it's internals work? Security Essentials doesn't nag me about upgrading to a paid version, like some of the other free solutions do, and generally uses only minimal system resources. Here at Puget Systems, we offer a free installation of Security Essentials on any compatible PC build, and we highly recommend you take us up on that offer if you don't have another antivirus program in mind.
Viruses aren't the only malware out there
It's also worth considering the other types of malware. Many antivirus programs, including MS Security Essentials, don't concentrate on detecting the 'semi-legitimate' ad-ware or spyware applications that might be installed on your PC. If you suspect you have something like this on your PC (maybe it's running slowly, or your web browser home page seems to always reset itself to something unexpected), you may want to download a separate malware removal tool such as Safer Networking's 'SpyBot Search and Destroy' or Lavasoft's Ad-Aware.
On a slight tangent, I have to admit that out of the other pay-for antivirus companies, I've been impressed with F-Secure ever since the Sony Rootkit fiasco. The noted IT security expert Bruce Schneier called out F-secure as being, "the first and the loudest critic of Sony's actions", which strikes me as a very customer-centric stance for them to take.
Av-Test's Home User Anti-Virus comparison for Feb 2014
Microsoft Security Essentials (official page)
Bruce Schneier's commentary on the Sony Rootkit Fiasco
SpyBot Search and Destroy
Edit 05/14/2014 (updated expired links)