Lookup or Reference function ignoring errors

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JD - 24 Jul 2007 22:38 GMT

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

Reply to this Message

Elkar - 24 Jul 2007 22:50 GMT

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

> 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
[quoted text clipped - 10 lines]
> Any help would be appreciated,
> Thanks JD

Reply to this Message

JD - 24 Jul 2007 23:00 GMT

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

> You could try something like:
>
[quoted text clipped - 24 lines]
> > Any help would be appreciated,
> > Thanks JD

Reply to this Message