Results 1 to 4 of 4

Thread: formula

  1. #1
    Join Date
    Dec 2002
    Location
    Houston, Texas
    Posts
    74

    Unanswered: formula

    =(Sum(IIf(nz([adppassfail])="Passed",1,0)))/(Sum(IIf(IsNull([adppassfail]),0,1)))

    [adppassfail] field contains passed, failed and in some case no value "null".

    what i am doing is dividing all the passed by (passed + failed)

    This works great if there is any value for adpPassFail field.

    but i get #Num! for Null values. How do I show nothing if the field is vacant.

    Can anyone check my formula.

    Regards
    Dianna Goldsberg

  2. #2
    Join Date
    Jan 2004
    Posts
    184

    Re: formula

    Your denominator is (Sum(IIf(IsNull([adppassfail]),0,1)))

    If the Sum is O then you are doing a divide by zero which will return #Num.
    In abundance of water only the fool is thirsty. Bob Marley.

  3. #3
    Join Date
    Dec 2002
    Location
    Houston, Texas
    Posts
    74

    True

    You are 100% correct. I knew that one. What I want may be another iif not to display anything when there is 0/0.

  4. #4
    Join Date
    Jan 2004
    Posts
    184

    Re: True

    I agree how about (I added the extra If at the beginning)

    =IIF((Sum(IIf(IsNull([adppassfail]),0,1))=0,0,(Sum(IIf(nz([adppassfail])="Passed",1,0)))/(Sum(IIf(IsNull([adppassfail]),0,1))))
    In abundance of water only the fool is thirsty. Bob Marley.

Posting Permissions

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