History
Until Nightfall, the alternatives weren't much better: Tiger Stance ended if you failed to hit (mostly a non-issue in PvE, save for Afflicted Rangers) but had a 20 s recharge, Berserker Stance sucked and ended on skill use, and Flurry reduced your damage and was energy-hungry. Tiger's Fury pinned your secondary to /R and required a hefty spec into beastmastery for a good duration, and the 10e cost was relatively high. So Frenzy was really the only viable option for an IAS that allowed flexibility for a warrior's secondary.

Come Nightfall, we have Soldier's Stance and Flail, the latter of which is gaining popularity, but those are topics for another day. Just remember that the colored history of Frenzy - and its inclusion as a Core skill means that it is one of a warrior's staple skills, and all warrior players should learn how to use it correctly.

Why Frenzy?
The first thing to realize is that Frenzy is basically the "best" IAS in the game in terms of availability, IAS%, and recharge. The drawback is nontrivial, however, and players often shun this skill because of the double damage. It's amusing to ask the elementalists that fired a lightning orb on a frenzy-healsig warrior for some 400 damage, or a Dervish that landed a 160-damage reaper's sweep indeed. Still, on a such a short recharge requiring no investment, Frenzy is a great skill to boost your DPS by 50%.

In PvP, warriors are damage-dealing characters, often devoting most of their skillbars to killing things rather than tanking monsters. Since warriors have basically the highest, most consistent dps in the game, Frenzy makes that dps so much better. It is why if you are a caster and a warrior comes up to you and frenzies, you kite him or shut him down - because if you don't, you will be dead very soon. Frenzy is good here because you can virtually enter and end the stance on demand. Most of the other IAS stances do not have this luxury, and hence are not as popular. The drawback is controlled by *you*, the warrior, and not whether someone put up Aegis or you need to spike now and end up losing Berserker Stance.

Frenzy, like all IAS skills, also allows compression of damage into a spike. Whereas a standard Eviscerate*-Executioner's combo would take 2 seconds (the Executioner's Strike hits when the bar fills, not 1.33 seconds after Eviscerate - the bar fills halfway into the swing, but this is a minor detail) Frenzy compresses that time down to 1.33 seconds. Compression of damage is why skills like Protector's Strike and Eremite's Attack are popular, so as to bypass the healers and spike a target dead before the monks can react.

In PvE, Frenzy isn't bad at all if you are in an organized group. Don't bring it in pugs, don't do it with the AI unless you know you'll have some form of reliable damage mitigation on yourself, and don't use it when you're under heavy fire (like the initial aggroing of a mob). In such a group, you can count on your healers to clean you up and keep you healthy.

How to Frenzy?
Two important points.

Rule number one: don't spam the skill. Firstly, the opponent will capitalize on the double damage clause and spike you out. Note that you have 100-116 armor against physical damage and Frenzy makes you as squishy as a caster/ranger against such damage, but elemental damage (80 or 96 AL normally) will start to hurt. Some effects, like from Spiteful Spirit* or Ineptitude*, are armor-ignoring and will kill you very quickly if you do not pay attention to your effects bar.
Secondly, spamming frenzy empties out your energy rather quickly. Keeping it up permanently requires the warrior's 2 pips of Energy regen, sparing no regen for the other energy skills on your bar, which are often utility skills or on-demand spike skills.
Thirdly, Frenzy is a stance, so it'll cancel a speed boost or blocker if you have one of those stances active. Use your good judgment - if you're chasing a fleeing Kournan Priest or dom mesmer, you probably don't want to enter Frenzy and not be able to catch up. If you're taking heavy damage from attacks, don't hit Frenzy when you finally reach your target.

Rule number two: cancel Frenzy if you're taking damage! The old EW premade warrior builds always told you to cancel Frenzy if you started taking damage. Armor-ignoring damage stacks up quicky (e.g., if you're caught in a somewhat common eburn-esurge combo) so be especially wary of that if you have Frenzy on the bar. You cancel Frenzy with another stance, commonly Sprint, Rush, or Dash, so you do not take double damage. (You may only have one stance active at a time.) Watch observer mode for the use of cancel stances. Battlefield awareness is key to the smart use of frenzy, and it is skill that can't really be taught.

When you should Frenzy, of course, is when your team is going in for a kill, so you can compress your damage and be more effective at getting off a fatal spike. Also, you can Frenzy and increase your dps for a simple 5e when you are not under attack, which is surprisingly often in PvP and unorganized PvE.

Conclusion
I'm not going to give ratings on where the skill is good or where it is bad. I think that labelling skills based on popularity in certain regimes is really kind of limiting, because the scope of both sides of Guild Wars is pretty broad. One would probably not use Frenzy when farming, say, vermin, but the skill is used in one of the FoW spider farmer builds. A split/gank GvG warrior might not want to risk the double damage, and go for the safer Tiger Stance instead, whereas a flagstand warrior with his monks backing him up wouldn't really think twice before slotting Frenzy.

My general feeling is that Frenzy is a good - maybe even great skill, overall, and I'll leave it at that. Do not blame the skill if some wammo in your pug got pwned by mursaat because he ran into the fort at Abaddon's Mouth and got suckered by 4 Chain Lightnings. Blame the player, for Frenzy is one of those skills that gets better as player skill increases - and that is what makes it so good.