Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 6
Default 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?
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up 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.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default 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?



  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 6
Default 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?




  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default 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?






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
ignore text in formula jatman Excel Worksheet Functions 11 December 29th 08 11:34 PM
Ignore #N/A in a formula [email protected] Excel Discussion (Misc queries) 1 February 14th 07 04:16 PM
Ignore #N/A in a formula [email protected] Excel Discussion (Misc queries) 3 February 14th 07 07:02 AM
Ignore Text for Formula JohnHill Excel Discussion (Misc queries) 2 April 26th 06 02:26 AM
How can I ignore an #N/A value in a =SUM() formula? JD Excel Worksheet Functions 2 February 13th 06 05:50 PM


All times are GMT +1. The time now is 09:55 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"