You can barely hear it when you're doing normal things (web browsing, playing iTunes, etc.) The only time noise is a problem is when you do anything processor intensive, like decoding 1080p movies, or ripping CDs. As an example, I took an admittedly crappy recording of the sound my new C2D Macbook makes when I run something CPU intensive (two instances of yes > /dev/null):
http://rapidshare.com/files/4071324/macbooknoise.mp3.html
(Sorry for the rapidshare link, but I don't have any other place to host it
). This was taken holding the mic about a foot from the machine, (maybe 20 inches from the rear vent). My voice was about a foot away from the mic as well, to give you a reference point. You can barely hear anything over the static of the mic up to about 20 seconds, which is where it starts to ramp up, and the fans are at nearly 6000 rpm by the end of the recording.