Expression divide by zero

• jag7777777

  SSC Enthusiast

  Points: 113

  More actions

  December 6, 2012 at 9:21 am

  #265864

  I have an expression as follows:

  =SWITCH(SUM(Fields!TotalSales.Value) = 0 AND (SUM(Fields!Weight.Value) <> 0 OR IsNothing(SUM(Fields!Weight.Value))),0,

  SUM(Fields!TotalSales.Value) <> 0 AND SUM(Fields!Weight.Value) <> 0 AND NOT IsNothing(SUM(Fields!Weight.Value)) AND

  SUM(Fields!Qty.Value) <> 0 AND NOT IsNothing(SUM(Fields!Qty.Value)),SUM(Fields!TotalSales.Value) / (SUM(Fields!Weight.Value)*SUM(Fields!Qty.Value)))

  I'm trying to find the price per tonne based on there being a sales value, a weight (of the item in tonnes) and a quantity sold. So my basic calc is:

  Sales Value / (Weight * Qty)

  I need to return a zero when the sales value is £0 and the weight is neither zero nor nothing as this represents delivered tonnes at £0 (i.e. samples).

  I only want to run my basic calc when the sales is not zero and when the weight and qty are neither zero nor nothing - which is what I think I have in my expression....but I still get divide by zero errors?

  Any help would be appreciated.

  Thanks,

  jag7777777

• jag7777777

  SSC Enthusiast

  Points: 113

  More actions

  December 7, 2012 at 2:44 am

  #1566514

  Just in case this is useful for anyone else, resorted to VB code in the end. Really don't understand why the switch didn't work...?

  This is the function:

  '**********************************************************************************

  'Function to prevent divide by zero errors.

  '

  'Returns the numerator when the denominator is 0 and the numerator is neither 0

  'nor nothing

  '

  '**********************************************************************************

  Function Divide(ByVal dbleNumerator As Double, ByVal dbleDenominator As Double) As Double

  If (dbleDenominator = 0 Or IsDBNull(dbleDenominator)) And dbleNumerator <> 0 Then

  Divide = dbleNumerator

  ElseIf (dbleDenominator = 0 Or IsDBNull(dbleDenominator)) And (dbleNumerator = 0 Or IsDBNull(dbleNumerator)) Then

  Divide = 0

  Else

  Divide = dbleNumerator / dbleDenominator

  End If

  End Function

  Seems to work OK.

  jag7777777