I think the most important thing is how intuitive and natural feeling the game is. It should feel right
There are certain "realism" things that are actually counter-intuitive. In addition, many "realism" proponents that don't know what they're talking about often advocate certain "realistic" mechanics that are generally quite unrealistic.
However, a lot of realism aspects help make the game more intuitive and are therefore desired.
And honestly, it's going to be impossible to make everything 100% realistic.
And making things more realistic doesn't necessarily make things less balanced. If you make one sword very realistic and forget to give the same kind of treatment to, say, a shield, then that can throw off game balance. But then you could start making the shield more realistic until it was balanced with the sword.
As for the singleplayer/multiplayer thing... I personally want the same gameplay in both modes. I hate when the gameplay is different between singleplayer and multiplayer. A good example would be Star Wars: Republic Commando. Single player = awesome. Multiplayer used a different interface, lacked some important mechanics, and basically sucked in comparison.