Results 1 to 3 of 3

Thread: IIF NZ (A2k)

  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    IIF NZ (A2k)

    Sorry but I'm having a brain block this morning.

    Unbound text : =[TG]/[TT]

    To handle Nulls, is it : =Nz([TG]) / Nz([TT]) or: =IIF([TT]=0),0,([TG]) / ([TT])

    I know it's embarrassing but it might have been the beer last night !! <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IIF NZ (A2k)

    A calculation like =[TG]/[TT] will result in Null (empty) if either of the arguments is Null (empty). It will result in #Div/0! if TT = 0. So there are two cases you have to handle here:
    - What should the result be if TT = 0? If you want 0, try this:

    =IIf([TT]=0,0,[TG]/[TT])

    - Is it OK that the result is empty if either of the arguments hasn't been filled in? If so, you don't need to do anything about this; if you want to return 0, see the next item.
    - If you want to return 0 if either of the arguments is Null (empty), expand the expression as follows:

    =Nz(IIf([TT]=0,0,[TG]/[TT]),0)

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IIF NZ (A2k)

    Thankyou Hans.

    The second version suits my needs, any of the items may be Null.

Posting Permissions

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