Determing Range Criteria
 

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?

Determing Range Criteria
 

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?

Determing Range Criteria
 

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?

Determing Range Criteria
 

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.
--
David Biddulph

"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.

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


"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?

Determing Range Criteria
 

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?

Determing Range Criteria
 

Not on my machine.

Alan Beban

Jim Aksel wrote:
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)

Determing Range Criteria
 

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:A1000,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



"David Biddulph" wrote:

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.
--
David Biddulph

"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.

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


"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?

Determing Range Criteria
 

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

"Jim Aksel" wrote:

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?

Determing Range Criteria
 

god bless

"Mankind" wrote:

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

"Jim Aksel" wrote:

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?

Determing Range Criteria
 

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

"Jim Aksel" wrote in message
...
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:A1000,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.

"David Biddulph" wrote:

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.

"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.

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

"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?

Determing Range Criteria
 

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



"David Biddulph" wrote:

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

"Jim Aksel" wrote in message
...
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:A1000,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.

"David Biddulph" wrote:

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.

"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.

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

"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?

Determing Range Criteria
 

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



"Alan Beban" wrote:

Not on my machine.

Alan Beban

Jim Aksel wrote:
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)