Essential Madness

"The moment their arms spun freely in our air, they were doomed -- for Man has earned his right to hold this planet against all comers, by virtue of occasionally producing someone totally batshit insane."

- Rollover Text, "Alternative Energy Solution," Xkcd

This is when the clinically insane person turns out to be The Only One who has the right stuff to stop the Omnicidal Aliens, Cosmic Horror or Big Bad. It could be his bent perceptions allow him to see things "normal" people can't. Or maybe the bad thing that happened was so completely out of the realm of possibility that only the total nut job prepared for it. Keep in mind, what separates this from Sociopathic Hero or other crazies is that this character's madness is essential to the task. If he was not crazy, he could not do it.

This guy put the Crazy in front of Prepared and Awesome, but don't confuse him with an Ignored Expert or The Worm Guy, who are not actually insane, just misunderstood. Not to be mistaken for his padded-cellmate the Sociopathic Hero, who is a hero in spite of his anti-social tendencies, not because of them.

When the whole human race is composed of Heroic Madmen, it's Humanity Is Insane. Compare The Cuckoolander Was Right and Power Born of Madness, although this trope doesn't necessarily involve superpowers. See also Blessed With Suck.

Anime

 * "Radar Man" in Paranoia Agent has been driven mad by the revelation, but his craziness also feeds him the information he needs to fight the menace to reality. Sort of.
 * Neon Genesis Evangelion's Ikari Shinji certainly qualifies, since it's implied Unit 01's berserk mode is tied to his manic state.

Comics

 * Crazyman from Continuity Comics, whose power is being crazy. "Crazyman" is recruited by a secret service as the "Plan B" to dangerous "unwinnable" situations. Danny is to the secret service as Monk is to the San Francisco Police Department, and comes up with solutions no sane man would come up with. If James Bond were insane, he'd be Crazyman.

Film

 * Romulus in The Caveman's Valentine. "I have brain typhoons." But he was the only one willing to believe (and try to prove) that the murdered guy was murdered.

Literature

 * Redwall - In High Rhulain, The hare Major Cuthbert went insane after his daughter was killed by vermin. He ended up killing a sea monster single handedly.
 * In Larry Niven's Madness Has Its Place, Jack Strather's megalomania and paranoid schizophrenia make him one of the few men left on Earth with the cojones to actually resist the invading Kzinti.
 * Blindsight: The main character's severe autism means he's the only one able to objectively synthesize all of the information and determine just what the deal is with the Starfish Aliens they discover. Of course he's also unable to bond with other people or empathize with them, so...downer.
 * In C.S. Friedman's This Alien Shore, the Guerans (a mutant race descended from humanity) are all insane, which makes them the only ones who can pilot ships through hyperspace. In addition, the heroine's multiple personality disorder grants her a secret power that could alter the balance of power throughout known space.
 * Jack Vance: One of his short stories (The Men Return to be exact) featured a world where causality had basically gone out the window. One character was barely surviving, trying to find patterns where there were none. The ones who were crazy before The Event on the other hand were basically gods.

Live Action TV

 * Seven Days: the only person able to pilot the Chronosphere is Frank Parker, inmate at a military insane asylum.

Web Comics

 * Xkcd: Don Quixote to the rescue!

Video Games

 * Portal: Schizophrenic Doug Rattman was completely right in suspecting the operating system was out to kill everyone and so was the only survivor of the lab incident.