I totally agree. It really does depend on what game it is. E.g., take GoldenEye - I personally didn't think that the single player mode was that spectacular, but the multi player mode was just absolutely amazing and so much fun that I get really nostalgic just thinking about it. Games like Final Fantasy and Metal Gear Solid work undoubtedly best as single player games for obvious reasons, whereas fighting games like Tekken and Soul Calibur work great as both single and multi player games in my opinion.
But, anyway - it totally depends on what genre and game we're talking about.