Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#9
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? |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Determing the SQL Datasource After the Fact | Excel Discussion (Misc queries) | |||
Determing Value From Date | Excel Discussion (Misc queries) | |||
Counting from one range to another range, multiple criteria | Excel Discussion (Misc queries) | |||
Formula for determing average based on weighting | Excel Worksheet Functions | |||
Find dates in a range; then sum values in that range by a criteria | Excel Discussion (Misc queries) |