|
Posted to microsoft.public.excel.worksheet.functions
|
|
|
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?
|