Good fiction should be internally consistent. That doesn't mean it has to be consistent with what we understand in the real world. That's a big difference some people seem to forget.
Science fiction is in an interesting place because it often relies on what one author called "black boxes": inventions that function mysteriously in order to enable the story's premise in the first place. In a lot of sci-fi stories, this is something like FTL or anti-gravity devices.
For some people, they want science fiction to be pure speculation based ONLY on what we know is scientifically plausible. They want true speculative fiction. It seems in recent years, the number of sci-fi fans who want only speculative fiction has increased. This may have something to do with us living in a world that is increasingly mature in the area of science: in other words, the more we learn, the more we also begin to think /isn't/ possible by deduction.
But, in my view, there is a significant difference between general science fiction and speculative fiction, and there's a reason why sci-fi that isn't "hard" sci-fi still isn't just fantasy fiction even if it is about "impossible" things.
General science fiction isn't necessarily about what is plausible within real-world rules. Rather, it's about the consequences of a particular device or concept, that generally speaking, comes from a scientific view of the universe - and it doesn't have to be /our/ universe with our rules. Typically, such sci-fi guesses at how the world or lives might be radically changed by this device or idea. It follows the consequences of this change ruthlessly, and not primarily for romantic purposes. (It's not about magic for the sake of being magical and mysterious.)
Now, the better science fiction stories have a nice hack for all this: they merely invent a /new/ principle that we have not /yet/ discovered in our world. These stories do not /violate/ what we already know directly. That's what poorly written sci-fi does. The clever stories say "yes, the expected rules are just like real life... except when Particle X is introduced, which radically changes the equation". And Particle X isn't treated as magic, able to do unexplainable things (unless solving the unexplainable mystery is part of the story premise!). An order of rational science is created in-universe around Particle X.
Most sci-fi is of the "invent a black box" type instead of the hard "NASA could really build it" variety.
Okay, okay, off the long-winded tangent, the one point about Avatar is that even if Cameron himself laughs and says the floaty mountains are an exaggeration, the story does create its own black box: the Unobtainium. Since its properties as a "hot" superconductor are not the same as an existing real-world material, it has a license to do whatever. It's pretty consistent in the film. Despite the calculations of Cameron's friend there is plenty of space inside the black box to hand-wave and say that the Unobtanium does bizarre things in mass quantities. (Honestly, if Cameron was clever he would retcon this posthaste, before writing the next film.)
And its not the first SciFi film to have something unrealistic of that nature...
You need to watch less Star Trek. 
