Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2004
    Location
    North West England
    Posts
    2

    Arrow Unanswered: Tweek the expression (#error)

    Average Cost / Call: ([Monthly Cost]/([Total Calls]-[Free]))

    A simple expression for a query.

    Monthly Cost = Total cost for the month.
    Total Calls = Total number of calls made that month.
    Free = Number of call that were free.

    The expression works out the average call cost for ONLY the calls I pay.

    However,
    When the total calls = free i get the result #error (because you cant divide by zero)

    Can anyone suggest how I get around this? I attempted an IFF function but couldn't master it.

    Anyone???

  2. #2
    Join Date
    Oct 2003
    Posts
    21
    Average Cost / Call: iif([Total Calls]-[Free] = 0,0,[Monthly Cost]/([Total Calls]-[Free]))

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    maybe this is a slightly safer version:
    avgcall: iif(nz([total calls],0) > nz([Free],0), nz([Monthly Cost],0)/([Total Calls]-nz([Free],0)), 0)

    izy
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •