Determing Range Criteria

[Guest]

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?

 

[Don Guillett]

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))

 

[Guest]

Half way there. That works to find the largest negative value.
I tried the ARRAY formula
=MIN(IF(A1:A100>0,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

 

[David Biddulph]

Are you sure you used that as an *array* formula? For me it does the job.
It excludes the negative numbers and gives me the smallest +ve one.

 

[Bob Phillips]

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)

 

[Alan Beban]

Not on my machine.

Alan Beban

 

[Guest]

To confirm an array formula - I type the formula as normal, hit enter.
Then reselect the cell and hit CTL+SHIFT+ENTER.
This works fine on the first formula, it also encloses it in { }
However, then I do it the second time with the =MIN(IF(A1:A100>0,A1:A100)) I
do not get the added {} after pressing CTL+SHFT+ENTER.

Any other ideas? I am sure it is something simple that I am overlooking.
--
If this post was helpful, please consider rating it.

Jim

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

 

[Guest]

All values are integers

it might be that you mean the largest *absolute* value among the group of
negative integers...

then try this array formula.

{=MAX(IF(A1:A100<0,ABS(A1:A100)))}

 

[Guest]

god bless

it might be that you mean the largest *absolute* value among the group of
negative integers...

then try this array formula.

{=MAX(IF(A1:A100<0,ABS(A1:A100)))}

 

[David Biddulph]

Don't hit enter first. Just type the formula, & hit Control Shift Enter.

 

[Guest]

Amen. It is done.
--
If this post was helpful, please consider rating it.

Jim

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

 

[Guest]

Your solution would answer a different question.
Thank you for the idea.