Lookup or Reference function ignoring errors
 

I am working on a spreadsheet with alot of lookup and reference functions. I
have gotten to pretty much my last step where i want to pull the lowest
number from a certain range of cells, but in both ranges there are error
values (#N/A).

When I use just the function:
=MIN(N7:N17)
(where N7:N17 is say #N/A,#N/A,#N/A, 16, 18, 20, 22)

it returns #N/A. Is there a way to ignore the errors and just get the
lowest number from that list (16).

Any help would be appreciated,
Thanks JD

Lookup or Reference function ignoring errors
 

You could try something like:

=MIN(IF(NOT(ISERROR(N7:N17)),N7:N17))

or maybe:

=MIN(IF(ISNUMBER(N7:N17),N7:N17))

These are array formulas, so should be comitted with CTRL-SHIFT-ENTER
instead of just Enter. If done properly, the formula will be enclosed in { }.

HTH,
Elkar



"JD" wrote:

I am working on a spreadsheet with alot of lookup and reference functions. I
have gotten to pretty much my last step where i want to pull the lowest
number from a certain range of cells, but in both ranges there are error
values (#N/A).

When I use just the function:
=MIN(N7:N17)
(where N7:N17 is say #N/A,#N/A,#N/A, 16, 18, 20, 22)

it returns #N/A. Is there a way to ignore the errors and just get the
lowest number from that list (16).

Any help would be appreciated,
Thanks JD

Lookup or Reference function ignoring errors
 

It worked !! Thanks so much, I probably would have been working on this for
days before finding the CTRL-SHIFT-ENTER for array functions.

Thanks, Wont be at the office all night
JD

"Elkar" wrote:

You could try something like:

=MIN(IF(NOT(ISERROR(N7:N17)),N7:N17))

or maybe:

=MIN(IF(ISNUMBER(N7:N17),N7:N17))

These are array formulas, so should be comitted with CTRL-SHIFT-ENTER
instead of just Enter. If done properly, the formula will be enclosed in { }.

HTH,
Elkar



"JD" wrote:

I am working on a spreadsheet with alot of lookup and reference functions. I
have gotten to pretty much my last step where i want to pull the lowest
number from a certain range of cells, but in both ranges there are error
values (#N/A).

When I use just the function:
=MIN(N7:N17)
(where N7:N17 is say #N/A,#N/A,#N/A, 16, 18, 20, 22)

it returns #N/A. Is there a way to ignore the errors and just get the
lowest number from that list (16).

Any help would be appreciated,
Thanks JD