Results 1 to 5 of 5

Thread: formula involve with if and #n/a

  1. #1

    formula involve with if and #n/a



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have three column of data.

    One example: 2500, #n/a, #n/a (say a1,b1,c1)

    I am using 'if' formula when any of the data consist of number not #n/a it would result of 0.

    My formula is =if(or(a1>0,b1>0,c1>0),0,#n/a) and it show #n/a.

    When I separate the formula is ok. =if(a1>0,0,#n/a) shows 0 and the other two show #n/a.

    Any idea what went wrong?

  2. #2
    Super Moderator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,336
    Articles
    0
    Excel Version
    2010 on Xubuntu
    There is probably something easier but =IF(SUMPRODUCT(--ISNUMBER(A1:C1)),0,"#n/a") seems to work

  3. #3
    Quote Originally Posted by davidroger View Post
    I have three column of data.

    One example: 2500, #n/a, #n/a (say a1,b1,c1)

    I am using 'if' formula when any of the data consist of number not #n/a it would result of 0.

    My formula is =if(or(a1>0,b1>0,c1>0),0,#n/a) and it show #n/a.

    When I separate the formula is ok. =if(a1>0,0,#n/a) shows 0 and the other two show #n/a.

    Any idea what went wrong?
    It sort of depends on what you're wanting to happen.

    I'm guessing that you want:
    - if ANY value in ANY column is a number, then you want a result of 0.
    - if ALL values in ALL columns are #n/a, then you want a result of #n/a

    This formula gives that result:
    =IF(AND(ISERROR(a1),ISERROR(b1),ISERROR(c1)),#N/A,0)

    An alternative formula to do the same thing, that gets the #N/A and the 0 the other way round, is:
    =IF(NOT(AND(ISERROR(a1),ISERROR(b1),ISERROR(c1))),0,#N/A)

    The first formula works this way:
    Are all the values #n/a ? (It only checks for *any* error, not #n/a/ specifically, but I'm sure that'll do)

    IF { ISERROR (col 1) *AND* ISERROR (col 2) *AND* ISERROR (col 3) } /* All of them are #n/a */
    [then] #n/a
    [else] 0

    The second one is similar

    IF { NOT ( ISERROR (col 1) *AND* ISERROR (col 2) *AND* ISERROR (col 3) ) } /* NOT all of them are #n/a */
    /* i.e. at least one is a number */
    [then] 0
    [else] #n/a


    If this isn't what you want it to do, let me know. :-)

  4. #4
    Super Moderator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,336
    Articles
    0
    Excel Version
    2010 on Xubuntu
    @euanm Although the use of iserror as you did is correct, it will not be very practical if the number of cells increases...

  5. #5
    Hi all,

    Thank you for your suggestion.

    just use countif can make the formula works.

Tags for this Thread

Posting Permissions

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