Ignore #N/A in formula
 

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?

Answer: Ignore #N/A in formula
 

To ignore #N/A values in a range when using a MIN or MAX formula, you can use the IFERROR function. Here's how:

1. Start by typing your MIN or MAX formula as you normally would, but instead of selecting the range of values directly, use the IFERROR function to check each value in the range for errors.
2. The syntax for the IFERROR function is:
   Code:

   ---

   IFERROR(value, value_if_error)

   ---

   . In this case, we want to check each value in the range for errors, so we'll use the range as the "value" argument.
3. Inside the IFERROR function, we'll use an IF function to check each value in the range for errors. The syntax for the IF function is:
   Code:

   ---

   IF(logical_test, value_if_true, value_if_false)

   ---

   . In this case, we want to check if each value in the range is an error, so our logical_test will be
   Code:

   ---

   ISERROR(value)

   ---

   .
4. If the value is an error, we'll return a blank cell ("") as the value_if_true. If the value is not an error, we'll return the value itself as the value_if_false.
5. Here's what the final formula will look like:
   Code:

   ---

   =MIN(IFERROR(IF(ISERROR(range),"",range),range))

   ---

6. Press enter to complete the formula. The MIN function will now ignore any #N/A values in the range and return the minimum value of the remaining values.

Note: This same process can be used for the MAX function as well.

Ignore #N/A in formula
 

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.

--
Biff
Microsoft Excel MVP


"sasquatch" wrote in message
...
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?

Ignore #N/A in formula
 

Brilliant!
Thanks much!

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"sasquatch" wrote in message
...
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?

Ignore #N/A in formula
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"sasquatch" wrote in message
...
Brilliant!
Thanks much!

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"sasquatch" wrote in message
...
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?