I quite like the auto fan profiles. I had a toy around with custom profiles, but didn't find anything that I prefer (using dual-SLI 480s, but with no real gap between the first two cards).
The auto fan control ramps up only very gradually, so for the most part I get fairly quiet cards in-game, at around 80C with the fan at around 60-65%. In furmark the fan on the top board can reach 90%, with the GPU temp stabilising at around 91C. The auto-fan profile seems designed to stop the temps getting much above 90C, but aims for minimum fan speed below that.
Remember, radiative heat-loss scales with the fourth-power of temperature differential, so leaving the cards to become hotter before you ramp up the fan speed is the optimal way to minimise noise (the larger the delta, the more energy is extracted from the heatsink for a given airflow). Of course, this means your everyday operating temperature is likely to be higher than using a fan profile such as the one raven is using, but to my mind if the card is designed to operate at high temperatures, then I see no reason to kill myself to reduce them.
Also, remember that a given fan speed and a given GPU load will rapidly lead to a stabilised GPU temperature. For a given GPU load, ramping up the fan earlier does not alter this stabilised temperature - it simply increases the time it takes for this stabilised temperature to be reached. Basic thermodynamics...
...In short: A good approach would be to consider what is a 'comfortable' fan volume while gaming, and have the fans run at around this level from base gaming temps (say ~70C) until the GPU temperature gets unacceptably high (which is around 90C, based on nvidia's stock profile). Once you reach this level, have the fans ramp up fairly rapidly (say within 5C they should be at maximum), so that you can maintain a roughly constant temperature without using too much fan unnecessarily.
[just checked in furmark, putting my ear right to the PSU - seems to still be fairly quiet...]