IF with ISERROR

Question

I don't think ISERROR can be used within an multiple IF statement.

Situation:

A1 may have a 0, value > 0, or #VALUE! (note: A1 is a calculated field)

In B1 I want to test for a value >0 in A1. If TRUE, B1=A1.  If FALSE, return "" to B1

I need to perform MEDIAN, MODE, STDEV on the B column and having 0 and #VALUE! don't play well.

ISERROR works by itself (=ISERROR(A1)), but when I add it to a multiple IF statement (I am using 2010 which doesn't have IFS) it doesn't work.

=IF(OR(ISERROR(A1),A1=0),"",A1)

So if there is an error in A1 (#VALUE!), or A1 is equal to 0, make B1="", else make B1=A1 (a positive integer)

If there is a positive integer or a 0 value in A1, then the calc works, but when it sees a #VALUE! it returns #VALUE!, when I expect it to return ""

Answer 1

The OR is forcing the comparison of A1 with 0, not just checking for the error. Try

=IF(ISERROR(A1),"",IF(A1=0,"",A1))

But you can do your Median, mode, etc on the column A range without column B by using array-enter (entered using Ctrl-Shift-Enter) formulas like

=MODE(IF(NOT(ISERROR(A1:A100)),IF(A1:A100<>0,A1:A100)))