Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I use LOOKUP as the reference in OFFSET function? | Excel Discussion (Misc queries) | |||
Statistical Functions ignoring errors | Excel Discussion (Misc queries) | |||
Ignoring text and errors with Sumproduct | Excel Worksheet Functions | |||
Lookup function returning reference, not value | Excel Worksheet Functions | |||
Ignoring Lines with Errors in Pivot Tables | Excel Worksheet Functions |