Ignore #N/A in formula

[sasquatch]

I have a range of values that intentionally include #N/A (for charting
purposes). When I reference this range in a MIN or MAX formula, it returns
#N/A. How do I ignore the #N/A values in this range?

 

[T. Valko]

Try these array formulas** :

Min:

=MIN(IF(ISNUMBER(A1:A10),A1:A10))

Max:

=MAX(IF(ISNUMBER(A1:A10),A1:A10))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[sasquatch]

Brilliant!
Thanks much!

Try these array formulas** :

Min:

=MIN(IF(ISNUMBER(A1:A10),A1:A10))

Max:

=MAX(IF(ISNUMBER(A1:A10),A1:A10))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[T. Valko]

You're welcome. Thanks for the feedback!