formula involve with if and #n/a

davidroger

New member
Joined
Dec 17, 2013
Messages
6
Reaction score
0
Points
0
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

Admin Alumnus
Joined
Oct 13, 2011
Messages
1,769
Reaction score
0
Points
36
Location
Brussels Belgium
Excel Version(s)
2010 on Xubuntu - O365
There is probably something easier but =IF(SUMPRODUCT(--ISNUMBER(A1:C1)),0,"#n/a") seems to work
 

EuanM

New member
Joined
Dec 22, 2013
Messages
4
Reaction score
0
Points
0
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. :)
 

Pecoflyer

Admin Alumnus
Joined
Oct 13, 2011
Messages
1,769
Reaction score
0
Points
36
Location
Brussels Belgium
Excel Version(s)
2010 on Xubuntu - O365
@euanm Although the use of iserror as you did is correct, it will not be very practical if the number of cells increases...
 

davidroger

New member
Joined
Dec 17, 2013
Messages
6
Reaction score
0
Points
0
Hi all,

Thank you for your suggestion.

just use countif can make the formula works.
 
Top