# Thread: formula involve with if and #n/a

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?  Reply With Quote

2. There is probably something easier but =IF(SUMPRODUCT(--ISNUMBER(A1:C1)),0,"#n/a") seems to work  Reply With Quote

3. Originally Posted by davidroger 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. :-)  Reply With Quote

4. @euanm Although the use of iserror as you did is correct, it will not be very practical if the number of cells increases...  Reply With Quote

5. Hi all,

Thank you for your suggestion.

just use countif can make the formula works.  Reply With Quote

#### Tags for this Thread

#n/a, if formula #### Posting Permissions

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