NaN and the number format

nulad
nulad used Ask the Experts™
on
To get the average for records that contain a value greater than 0, the below expression was created.  If the average produces a “NaN” it displays “---“ instead on the report.

=IIf((Sum(IIf(Fields!DX_001.Value>0,Fields!DX_001.Value,Nothing))
/ Sum(IIf(Fields!DX_001 > 0, 1, Nothing)))="NaN", "---", (Sum(IIf(Fields!DX_001.Value>0,Fields!DX_001.Value,Nothing))
/ Sum(IIf(Fields!DX_001.Value > 0, 1, Nothing))))
I do not know how to format the result to display just two decimal places.  I have placed “0.00” in the Number Format property, but this does not work.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
sammySeltzer
Top Expert 2011

Commented:
Just out of curiosity, why not put "0.00" in place of "--"

like:

Question: To get the average for records that contain a value greater than 0, the below expression was created.  If the average produces a “NaN” it displays “---“ instead on the report.

=IIf((Sum(IIf(Fields!DX_001.Value>0,Fields!DX_001.Value,Nothing))
/ Sum(IIf(Fields!DX_001 > 0, 1, Nothing)))="NaN", "0.00", (Sum(IIf(Fields!DX_001.Value>0,Fields!DX_001.Value,Nothing))
/ Sum(IIf(Fields!DX_001.Value > 0, 1, Nothing))))

nulad

Author

Commented:
I am sorry.  I copied the incorrect expression.  The below expression is being used.

=Replace(Sum(IIf(Fields!DX_001.Value>0,Fields!DX_001.Value,Nothing))
/ Sum(IIf(Fields!DX_001.Value > 0, 1, Nothing)),"NaN", "---")

I can not use the "0.00" in place of "---", because that would indicate a 0 average.  I want to make sure the user knows there were no entries to average.
Nico Bontenbal

Commented:
Because you use the replace function, the expression returns a string. So the format property will have no effect. You can use this expression to format the number with 2 decimals.
=Replace(formatnumber(Sum(IIf(Fields!DX_001.Value>0,Fields!DX_001.Value,Nothing)) 
/ Sum(IIf(Fields!DX_001.Value > 0, 1, Nothing)),2,false,false,true),"NaN", "---")

Open in new window

Commented:
And why not use the Avg function. This expression will also give you the avg of the positive values:
=Avg(IIf(Fields!DX_001.Value>0,Fields!DX_001.Value,Nothing))

Open in new window

It will leave the cell blank when there are no positive values. If you insist on the "--" you could use:
=iif(isnothing(Avg(IIf(Fields!DX_001.Value>0,Fields!DX_001.Value,Nothing))),"--",Avg(IIf(Fields!DX_001.Value>0,Fields!DX_001.Value,Nothing)))

Open in new window

Both these function return a number so you can use the format property.
nulad

Author

Commented:
Thank you so much for your solution.  Worked perfectly!  Just what I was looking for.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial