Depends what you want mods for. If you like improved visuals/different GUI, weather, night that's actually very dark, new items/clothes, new Perks/Skills to choose from, new quests or increased difficulty (there are many ways to do that, YMMV), mods can be awesome. If none of those things appeal to you...well, probably not a whole lot there in terms of pre-made. However with the PC you can get the GECK yourself and make small changes on your own, to suit what you want. Like if you want more realistic gun damage (and only the gun damage...many "difficulty mods" change way, way too much stuff for my liking so I don't use), you could do that.
I don't run many mods and most of them I make myself. But the visual mods, bugfixes (broken Boone, sigh...) and IWS (to increase enemies on the map, I like difficult chaos) I like quite a bit. And a few clothing mods.
And for laughs/silly I like the dance/pose mods and multiple-companions...for immersion I tend to like mods that give companions more interactive or idle animations so they aren't as statue like all the time. But if you're a purist or not into that stuff, they won't add anything to the game.