Here is my experience with a Mac Pro (completely stock config):
Under light load the Mac Pro's fans are loud compared to a Pentium 4 PC with optimized fans or a MacBook Pro. The good thing is that the noise did not increase under heavy load.
The fans make a constant hum which sounds like a propeller airplane.
With no ambient noise present it is annoying. However, it's no problem in a noisy office or while listening to music.
The power supply emits a buzzing noise, but it's not noticeable from normal distance.
Today, I went to an Apple shop and listened to some Mac Pros, as far as I was able to hear it in that rather loud environment they all make a humming noise.
I'd love to hear reports from people who have their Mac Pro in a quite room, because if some are "dead silent" I'll have to sent mine back.
In my case, the only noise I hear are the hard drives spinning and accessing. When the hard drives spin down, my MP is so quite that I really can't hear it. Mine sits below my desk but the face of the MP is exposed so if there was any fan noise I should hear it.
I do not have any power supply noise that I can hear; there is no buzz, rumble, hum or anything that resembles it.
Dave