| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
min function
I often use the min function to check that no value in a huge block is negative, generally something that is unallowed. However there are exceptions and, once I verify that some cell really should is an exception and should be negative, I want to exclude that value from my min evaluation, without having to break it up into multiple ranges. Or, failing that, perhaps a cleverly written macro could be used to automatically change the range into the sub-ranges which are above, below, left, and right of it? I suppose that would get tricky if you had to do it a 2nd or 3rd time, a real possibility.
Is there some way, where you can get the minimum of this entire block of cells, but exclude this one (or more) single cells, which is somewhere in the middle? Or some clever trick? Dean |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
min function
That works very nicely, thank you. It took me a bit to get it right, as I generally don't use array functions, but I got it now. Very clever. And I can even use it to tier down to the next lowest and the next lowest and ...
Actually, I should ask, is there any regular EXCEL function that would give you say, the 3rd (nth in general) lowest value in a block of cells? Thanks! Dean "Don Guillett" wrote in message ... try this idea where you first find the minimum and then wrap into a min(if array formula which must be entered using ctrl+shift+enter. =MIN(IF(B1:B21MIN(B:B),B1:B21)) -- Don Guillett SalesAid Software "Dean" wrote in message ... I often use the min function to check that no value in a huge block is negative, generally something that is unallowed. However there are exceptions and, once I verify that some cell really should is an exception and should be negative, I want to exclude that value from my min evaluation, without having to break it up into multiple ranges. Or, failing that, perhaps a cleverly written macro could be used to automatically change the range into the sub-ranges which are above, below, left, and right of it? I suppose that would get tricky if you had to do it a 2nd or 3rd time, a real possibility. Is there some way, where you can get the minimum of this entire block of cells, but exclude this one (or more) single cells, which is somewhere in the middle? Or some clever trick? Dean |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
min function
dean:
check the large function in help -- Gary "Dean" wrote in message ... That works very nicely, thank you. It took me a bit to get it right, as I generally don't use array functions, but I got it now. Very clever. And I can even use it to tier down to the next lowest and the next lowest and ... Actually, I should ask, is there any regular EXCEL function that would give you say, the 3rd (nth in general) lowest value in a block of cells? Thanks! Dean "Don Guillett" wrote in message ... try this idea where you first find the minimum and then wrap into a min(if array formula which must be entered using ctrl+shift+enter. =MIN(IF(B1:B21MIN(B:B),B1:B21)) -- Don Guillett SalesAid Software "Dean" wrote in message ... I often use the min function to check that no value in a huge block is negative, generally something that is unallowed. However there are exceptions and, once I verify that some cell really should is an exception and should be negative, I want to exclude that value from my min evaluation, without having to break it up into multiple ranges. Or, failing that, perhaps a cleverly written macro could be used to automatically change the range into the sub-ranges which are above, below, left, and right of it? I suppose that would get tricky if you had to do it a 2nd or 3rd time, a real possibility. Is there some way, where you can get the minimum of this entire block of cells, but exclude this one (or more) single cells, which is somewhere in the middle? Or some clever trick? Dean |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
min function
forgot the small function, too
-- Gary "Dean" wrote in message ... That works very nicely, thank you. It took me a bit to get it right, as I generally don't use array functions, but I got it now. Very clever. And I can even use it to tier down to the next lowest and the next lowest and ... Actually, I should ask, is there any regular EXCEL function that would give you say, the 3rd (nth in general) lowest value in a block of cells? Thanks! Dean "Don Guillett" wrote in message ... try this idea where you first find the minimum and then wrap into a min(if array formula which must be entered using ctrl+shift+enter. =MIN(IF(B1:B21MIN(B:B),B1:B21)) -- Don Guillett SalesAid Software "Dean" wrote in message ... I often use the min function to check that no value in a huge block is negative, generally something that is unallowed. However there are exceptions and, once I verify that some cell really should is an exception and should be negative, I want to exclude that value from my min evaluation, without having to break it up into multiple ranges. Or, failing that, perhaps a cleverly written macro could be used to automatically change the range into the sub-ranges which are above, below, left, and right of it? I suppose that would get tricky if you had to do it a 2nd or 3rd time, a real possibility. Is there some way, where you can get the minimum of this entire block of cells, but exclude this one (or more) single cells, which is somewhere in the middle? Or some clever trick? Dean |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
min function
Yes, "small" is what I really need. I'm glad I asked. Don's solution was clever as hell, and I'm glad he taught it to me, but this is much easier, plus my workbooks tend to get so big that they can self-corrupt and I suppose having a ton of array formulas won't help that any!
Thanks all, Dean "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... forgot the small function, too -- Gary "Dean" wrote in message ... That works very nicely, thank you. It took me a bit to get it right, as I generally don't use array functions, but I got it now. Very clever. And I can even use it to tier down to the next lowest and the next lowest and ... Actually, I should ask, is there any regular EXCEL function that would give you say, the 3rd (nth in general) lowest value in a block of cells? Thanks! Dean "Don Guillett" wrote in message ... try this idea where you first find the minimum and then wrap into a min(if array formula which must be entered using ctrl+shift+enter. =MIN(IF(B1:B21MIN(B:B),B1:B21)) -- Don Guillett SalesAid Software "Dean" wrote in message ... I often use the min function to check that no value in a huge block is negative, generally something that is unallowed. However there are exceptions and, once I verify that some cell really should is an exception and should be negative, I want to exclude that value from my min evaluation, without having to break it up into multiple ranges. Or, failing that, perhaps a cleverly written macro could be used to automatically change the range into the sub-ranges which are above, below, left, and right of it? I suppose that would get tricky if you had to do it a 2nd or 3rd time, a real possibility. Is there some way, where you can get the minimum of this entire block of cells, but exclude this one (or more) single cells, which is somewhere in the middle? Or some clever trick? Dean |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
min function
glad you got what you needed. don is sharp, and since this is a programming group, gave you some code to answer your question. so, now you have 2 ways and learned something new besides.
-- Gary "Dean" wrote in message ... Yes, "small" is what I really need. I'm glad I asked. Don's solution was clever as hell, and I'm glad he taught it to me, but this is much easier, plus my workbooks tend to get so big that they can self-corrupt and I suppose having a ton of array formulas won't help that any! Thanks all, Dean "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... forgot the small function, too -- Gary "Dean" wrote in message ... That works very nicely, thank you. It took me a bit to get it right, as I generally don't use array functions, but I got it now. Very clever. And I can even use it to tier down to the next lowest and the next lowest and ... Actually, I should ask, is there any regular EXCEL function that would give you say, the 3rd (nth in general) lowest value in a block of cells? Thanks! Dean "Don Guillett" wrote in message ... try this idea where you first find the minimum and then wrap into a min(if array formula which must be entered using ctrl+shift+enter. =MIN(IF(B1:B21MIN(B:B),B1:B21)) -- Don Guillett SalesAid Software "Dean" wrote in message ... I often use the min function to check that no value in a huge block is negative, generally something that is unallowed. However there are exceptions and, once I verify that some cell really should is an exception and should be negative, I want to exclude that value from my min evaluation, without having to break it up into multiple ranges. Or, failing that, perhaps a cleverly written macro could be used to automatically change the range into the sub-ranges which are above, below, left, and right of it? I suppose that would get tricky if you had to do it a 2nd or 3rd time, a real possibility. Is there some way, where you can get the minimum of this entire block of cells, but exclude this one (or more) single cells, which is somewhere in the middle? Or some clever trick? Dean |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
min function
Indeed, I did! Thanks all!
"Gary Keramidas" <GKeramidasATmsn.com wrote in message ... glad you got what you needed. don is sharp, and since this is a programming group, gave you some code to answer your question. so, now you have 2 ways and learned something new besides. -- Gary "Dean" wrote in message ... Yes, "small" is what I really need. I'm glad I asked. Don's solution was clever as hell, and I'm glad he taught it to me, but this is much easier, plus my workbooks tend to get so big that they can self-corrupt and I suppose having a ton of array formulas won't help that any! Thanks all, Dean "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... forgot the small function, too -- Gary "Dean" wrote in message ... That works very nicely, thank you. It took me a bit to get it right, as I generally don't use array functions, but I got it now. Very clever. And I can even use it to tier down to the next lowest and the next lowest and ... Actually, I should ask, is there any regular EXCEL function that would give you say, the 3rd (nth in general) lowest value in a block of cells? Thanks! Dean "Don Guillett" wrote in message ... try this idea where you first find the minimum and then wrap into a min(if array formula which must be entered using ctrl+shift+enter. =MIN(IF(B1:B21MIN(B:B),B1:B21)) -- Don Guillett SalesAid Software "Dean" wrote in message ... I often use the min function to check that no value in a huge block is negative, generally something that is unallowed. However there are exceptions and, once I verify that some cell really should is an exception and should be negative, I want to exclude that value from my min evaluation, without having to break it up into multiple ranges. Or, failing that, perhaps a cleverly written macro could be used to automatically change the range into the sub-ranges which are above, below, left, and right of it? I suppose that would get tricky if you had to do it a 2nd or 3rd time, a real possibility. Is there some way, where you can get the minimum of this entire block of cells, but exclude this one (or more) single cells, which is somewhere in the middle? Or some clever trick? Dean |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel Data Validation/Lookup function does function correcty | Excel Worksheet Functions | |||
| copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
| LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
| Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
| Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
![Dean[_8_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
