|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 09:52 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com