It will if there are no zero values. But with zeroes, that formula extracts
them and gives the MIN of the rest.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Jim Aksel" wrote in message
...
Half way there. That works to find the largest negative value.
I tried the ARRAY formula
=MIN(IF(A1:A1000,A1:A100))
Using this formula provides the same value as =MIN(A1:A100)

No joy. Any other ideas? I need the smallest positive non-zero integer
value. I'm using E2007 if that adds to the joy.

PS the magic number in Excel2003 is 65,536 rows. In Excel2007 it is
2^20=1048576



--
If this post was helpful, please consider rating it.

Jim

Visit http://project.mvps.org/ for FAQs and more information
about Microsoft Project



"Don Guillett" wrote:

try this idea using this ARRAY formula which must be entered using
ctrl+shift+enter. Also suggest NOT using 65000. Use something less

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jim Aksel" wrote in message
...
I have a series of numbers. For all intents an purposes the range is
-infinity to +infinity. All values are integers, and there may be
multiples... Example, there may be a quantity of 40 with a value of
"15".
The numbers are in random order and cannot be sorted.

Determining the min/max is easy: =Min(A1:A65000) and Max(A1:A65000).

I want to determine the largest negative value and the smallest
positive
value. I don't care about its position or frequency of occurence, only
the
value is important to me.

Do I need to write my own function in VBA or is there something easy I
am
missing?