Reply With QuoteThere is probably something easier but =IF(SUMPRODUCT(--ISNUMBER(A1:C1)),0,"#n/a") seems to work
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?
There is probably something easier but =IF(SUMPRODUCT(--ISNUMBER(A1:C1)),0,"#n/a") seems to work
It sort of depends on what you're wanting to happen.Originally Posted by davidroger
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. :-)
@euanm Although the use of iserror as you did is correct, it will not be very practical if the number of cells increases...
Hi all,
Thank you for your suggestion.
just use countif can make the formula works.
Bookmarks