how to find the min value in a list with #div/0!

[freak11]

hi,
i have a list with lots of values and i want to find the min value. when i type in =min() i get #div/0! i want numbers and not this error. at the same time i do not want to make any changes to the table. is there a way?

[Richard Buttrey]

Hi,

This suggests that one or more of your values is returning a #Div/0 hence the smartest way would be to wrap all those formulae in an IFERROR() (or =IF(ISERROR()) if you only have 2003)

Otherwise you'll need an array formula which returns non error values.

[martindwilson]

min() will not give #div/0 on its own you must have #div/0 errors in the table
so try
=MIN(IF(ISNUMBER($D$1:$E$9),$D$1:$E$9)) array entered with ctrl shift and enter

[joeu2004]

i have a list with lots of values and i want to find the min value. when i type in =min() i get #div/0! i want numbers and not this error. at the same time i do not want to make any changes to the table. is there a way?

AFAIK, MIN(A1:A100) returns a #DIV/0 error only when one of A1:A100 contains the #DIV/0 error. Use the following array-entered formula (press ctrl+shift+Enter instead just Enter) to ignore cells with Excel errors:

=MIN(IF(ISNUMBER(A1:A100),A1:A100))

That formula returns zero if there are no numeric values in A1:A100. If you want something else (e.g. null string), use the following array-entered formula (press ctrl+shift+Enter):

=IF(COUNT(A1:A100)=0,"",MIN(IF(ISNUMBER(A1:A100),A1:A100)))

[EDIT] PS.... IMHO, it would be better to remove Excel errors in the data (A1:A100). But you wrote: "i do not want to make any changes to the table".

[freak11]

thanks for hte help,
how can i modify the value in such a manner that i get the second smallest value because i do not want zero

AFAIK, MIN(A1:A100) returns a #DIV/0 error only when one of A1:A100 contains the #DIV/0 error. Use the following array-entered formula (press ctrl+shift+Enter instead just Enter) to ignore cells with Excel errors:

=MIN(IF(ISNUMBER(A1:A100),A1:A100))

That formula returns zero if there are no numeric values in A1:A100. If you want something else (e.g. null string), use the following array-entered formula (press ctrl+shift+Enter):

=IF(COUNT(A1:A100)=0,"",MIN(IF(ISNUMBER(A1:A100),A1:A100)))

[EDIT] PS.... IMHO, it would be better to remove Excel errors in the data (A1:A100). But you wrote: "i do not want to make any changes to the table".

[martindwilson]

Try
=MIN(IF(ISNUMBER(A1:A100),if(A1:A100>0,A1:A100)))
array entered

[Tony Valko]

If you have Excel 2010 or later...

Data Range

	A	B	C
1	45		2
2	17
3	#DIV/0!
4	83
5	92
6	#N/A
7	45
8	0
9	2
10	28
11	------	------	------

=AGGREGATE(15,6,1/(1/A1:A10),1)

[joeu2004]

Use [one of] the following array-entered formula (press ctrl+shift+Enterinstead just Enter) [....]:

=MIN(IF(ISNUMBER(A1:A100),A1:A100))

[....or....]

=IF(COUNT(A1:A100)=0,"",MIN(IF(ISNUMBER(A1:A100),A1:A100)))

how can i modify the value in such a manner that i get the second smallest value because i do not want zero

I presume you mean: you just want to ignore zeros.

In that case, try one of the following array-entered formula (press ctrl+shift+Enter instead just Enter), whichever serves your intent better:

=MIN(IF(ISNUMBER(A1:A100),IF(A1:A100<>0,A1:A100)))

or

=IF(COUNT(A1:A100)=0,"",MIN(IF(ISNUMBER(A1:A100),IF(A1:A100<>0,A1:A100))))