Ignore #N/A in formula

  • Thread starter Thread starter sasquatch
  • Start date Start date
S

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?
 
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.
 
Brilliant!
Thanks much!

T. Valko said:
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.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Set chart series endpoint dynamically 0
Set add function ignore #N/A 1
Conditional format Formula -Duplicate & n/a 1
ignore #n/a 2
VLOOKUP returning #N/A result 2
Can I use a named range in SUMPRODUCT? 2
Ignoring Cells Returning #N/A 4
Lookup returning #n/a 1

Back
Top