Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2002
    Location
    Hartford, CT
    Posts
    106

    Unanswered: Devision in a report

    Hi,
    I am working in MS Office 2007. I am trying to calculate a percent of a change in two fields. I would like to display like this:
    If LY_PAT_COUNT=0 print %100 (or 1)
    if TY_PAT_COUNT=0 print -%100 (or -1)
    otherwise, [TY_PAT_COUNT]-[LY_PAT_COUNT])/[LY_PAT_COUNT] normal figure.

    I came up with this formula but getting the wrong number of arguments error.

    =IIf([LY_PAT_COUNT]=0,1,iif([TY_PAT_COUNT],0,-1,([TY_PAT_COUNT]-[LY_PAT_COUNT])/[LY_PAT_COUNT]))
    Mo

  2. #2
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    =IIf([LY_PAT_COUNT]=0,1,iif([TY_PAT_COUNT]=0,-1,([TY_PAT_COUNT]-[LY_PAT_COUNT])/[LY_PAT_COUNT]))

  3. #3
    Join Date
    Oct 2002
    Location
    Hartford, CT
    Posts
    106
    Hi,
    I see what I did wrong in that line. But did not fix it. I am trying this line. I think it makes sense but still getting the same error:

    =IIf([LY_PAT_COUNT]=0,1,IIf([TY_PAT_COUNT]=0,-1,([TY_PAT_COUNT]-[LY_PAT_COUNT])/[LY_PAT_COUNT]),([TY_PAT_COUNT]-[LY_PAT_COUNT])/[LY_PAT_COUNT])
    Mo

  4. #4
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    the parenthesis might be the problem, give this a try...

    =IIf([LY_PAT_COUNT]=0,1,iif([TY_PAT_COUNT]=0,-1,(([TY_PAT_COUNT]-[LY_PAT_COUNT])/[LY_PAT_COUNT])))

  5. #5
    Join Date
    Oct 2002
    Location
    Hartford, CT
    Posts
    106
    That bypassed the error, but the report is still printing ##### when [TY_PAT_COUNT]=0.
    Mo

Posting Permissions

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