Calculations involving NULLs

I have a Calulated Member that is calculating an average.
[Measures].[Overall Satisfaction]/[Measures].[Count Of Surve
y]
The problem is that for some of the items there are no surveys, so there are
NULL values in the database for these.
When I view the cube as a pivot table in Excel I am getting #NUM! for any
dimentions that do not have actual numbers.
How can I change my calculation so it will just return NULL instead of the
dreaded #NUM! value that drives managers so crazy?
Thank you!
:-J
JeremyUse COALESCE or ISNULL function to change a NULL to a valid value or a
space.
Anith|||REGARDS MY EXAMPLE
--
[Measures].[Average] AS ' IIF(ISNULL([Measures].[Count Of Su
rvey]), NULL, [Measures].[Overall Satisfaction]/[Measures].[
Count Of Survey]) '
--
You colud use ISNULL() with a tuple, for example ISNULL(([Time].[200
4].CURRENTMEMBER, [Measures].[Overall Sastisfaction))
I hope it would help you
Agustin|||Thank you for the help it got me going on the right track.
What I ended up with was:
IIF([Measures].[Count Of Survey]=0, NULL, [Measures].[Overal
l
Satisfaction]/[Measures].[Count Of Survey])
This seems to work perfectly!
Thanks agian!
-Jeremy
"Agustin Brau" <anonymous@.discussions.microsoft.com> wrote in message
news:759562E4-29AE-4697-AEE4-4290AF5A524D@.microsoft.com...
> REGARDS MY EXAMPLE
> --
> [Measures].[Average] AS ' IIF(ISNULL([Measures].[Count Of Survey])
, NULL,
[Measures].[Overall Satisfaction]/[Measures].[Count Of Survey]) 'reen">
> --
> You colud use ISNULL() with a tuple, for example
ISNULL(([Time].[2004].CURRENTMEMBER, [Measures].[Overall Sastisfaction))[vbc
ol=seagreen]
> I hope it would help you
> Agustin[/vbcol]