attempted to divide by zero

hi,

i had this formula written for a textbox in a table, but yet still encounter the following error:

expression:

=iif(countdistinct(Fields!room.Value)=0,0, sum(Fields!rate.Value)/countdistinct(Fields!room.Value))

error:

attempted to divide by zero.

any way i can solve this problem?

thanks!

IIF is a function call which evaluates all arguments before it executes. Hence, given your expression a division by zero is possible. Try the following expression instead:
=IIf( CountDistinct(Fields!room.Value) = 0, 0, Sum(Fields!rate.Value) / iif(CountDistinct(Fields!room.Value) = 0, 1, CountDistinct(Fields!room.Value)))

In general, you want a pattern like this to avoid division by zero:
=iif(B=0, 0, A / iif(B=0, 1, B))

You could also define a generic DivideXByY function in the custom code section of the report that uses IF-ELSE-ENDIF statements (instead of the IIF function call) to perform the division and avoid the DivisionByZero exception.

-- Robert

|||thanks a lot, Robert.|||

Hello Robert,

Thanks for this post.This helps me a lot.

I have tried on many sites to get help but not getting much

Thanks again

Sunil Pawar.

|||This worked for me good work on this|||It would be good if we had a VBA function to do this. This is a common requirement. And a time waster until I found this post.|||

HI Everyone,

I generally use the following statement

=IIF(Fields!Profit.Value<>0, Fields!Profit.Value/ Fields!Sales.Value, Nothing)

For the most part this formula is simple and effective...

BUT (there is always a but!!)

I received an error message "attempted to divide by zero". I checked the tables to validate column formatting and everything appears to be okay (decimals(11,2) on both columns. If any one has any suggestions, it would be greatly appreciated.

Regards,

A.Akin

|||Hi,
Please try out with this formula,

=iif(countdistinct(Fields!room.Value)=0,0, sum(Fields!rate.Value)/IIF(countdistinct(Fields!room.Value))=0,1,countdistinct(Fields!room.Value))

I think it will work.

Cheers,

Shri