attempted to divide by zero

attempted to divide by zero

• Iulian Popescu

  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

  
  

• Hal Styli

  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

   

  
  
  

• Brian Grunkemeyer

  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.
  

  
  

  
  

  
  

  
  

  
  

  
  

  
  

• R Gatliff

  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.
  

• David Huerta

  thanks a lot, Robert.
  

• BraBo

  This worked for me good work on this
  
  

• Visual Basic Nov.

  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

  
  
  
  
• attempted to divide by zero