# formula involve with if and #n/a

#### davidroger

##### New member
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?

#### Pecoflyer

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

#### EuanM

##### New member
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. 