min, max functions
 

in trying to find a min value, how do i exclude zeros from the list to be
looked at?

if you are sure there are some zero values then use
=small(dataange,2)

if you are not sure then use Dmin. see help


jtribe wrote in message
...
in trying to find a min value, how do i exclude zeros from the list to be
looked at?

Hi!

Array entered with the key combo of CTRL,SHIFT,ENTER:

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

Biff

-----Original Message-----
in trying to find a min value, how do i exclude zeros
from the list to be
looked at?
.

That won't exclude zeroes. You can use SMALL but will still need to use
something like Biff's example, eg

=SMALL(IF(Rng<0,Rng),1) array entered.

or

=SMALL(IF(Rng,Rng),1) array entered.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"R.VENKATARAMAN" wrote in message
...
if you are sure there are some zero values then use
=small(dataange,2)

if you are not sure then use Dmin. see help


jtribe wrote in message
...
in trying to find a min value, how do i exclude zeros from the list to
be
looked at?

or slightly shorter

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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Biff" wrote in message
...
Hi!

Array entered with the key combo of CTRL,SHIFT,ENTER:

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

Biff

-----Original Message-----
in trying to find a min value, how do i exclude zeros
from the list to be
looked at?
.

apologies to the newsgroup
=small(datarange,2)
will be ok only if there is only one zero
probably I was hasty.
I modified this to
=IF(MIN(A1:A13)=0,SMALL(A1:A13,COUNTIF(A1:A13,"0") +1),MIN(A1:A13))
but I found that others have since given more elegant
solutions.


R.VENKATARAMAN wrote in message
...
if you are sure there are some zero values then use
=small(dataange,2)

if you are not sure then use Dmin. see help


jtribe wrote in message
...
in trying to find a min value, how do i exclude zeros from the list to
be
looked at?