min function

[Dean]

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

[Don Guillett]

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:B21>MIN(B:B),B1:B21))

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Dean" <whooshbopbang4@adelphia.net> wrote in message news:P4udnZgRQqjNKFvZnZ2dnUVZ_umdnZ2d@adelphia.com...
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

[Dean]

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" <dguillett1@austin.rr.com> wrote in message news:OzuElqEsGHA.1288@TK2MSFTNGP02.phx.gbl...
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:B21>MIN(B:B),B1:B21))

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Dean" <whooshbopbang4@adelphia.net> wrote in message news:P4udnZgRQqjNKFvZnZ2dnUVZ_umdnZ2d@adelphia.com...
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

[Gary Keramidas]

dean:

check the large function in help

--


Gary


"Dean" <whooshbopbang4@adelphia.net> wrote in message news:7JudneCFbNGwflvZnZ2dnUVZ_oOdnZ2d@adelphia.com...
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" <dguillett1@austin.rr.com> wrote in message news:OzuElqEsGHA.1288@TK2MSFTNGP02.phx.gbl...
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:B21>MIN(B:B),B1:B21))

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Dean" <whooshbopbang4@adelphia.net> wrote in message news:P4udnZgRQqjNKFvZnZ2dnUVZ_umdnZ2d@adelphia.com...
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

[Gary Keramidas]

forgot the small function, too

--


Gary


"Dean" <whooshbopbang4@adelphia.net> wrote in message news:7JudneCFbNGwflvZnZ2dnUVZ_oOdnZ2d@adelphia.com...
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" <dguillett1@austin.rr.com> wrote in message news:OzuElqEsGHA.1288@TK2MSFTNGP02.phx.gbl...
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:B21>MIN(B:B),B1:B21))

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Dean" <whooshbopbang4@adelphia.net> wrote in message news:P4udnZgRQqjNKFvZnZ2dnUVZ_umdnZ2d@adelphia.com...
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

[Dean]

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 news:evwMqXGsGHA.1224@TK2MSFTNGP03.phx.gbl...
forgot the small function, too

--


Gary


"Dean" <whooshbopbang4@adelphia.net> wrote in message news:7JudneCFbNGwflvZnZ2dnUVZ_oOdnZ2d@adelphia.com...
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" <dguillett1@austin.rr.com> wrote in message news:OzuElqEsGHA.1288@TK2MSFTNGP02.phx.gbl...
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:B21>MIN(B:B),B1:B21))

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Dean" <whooshbopbang4@adelphia.net> wrote in message news:P4udnZgRQqjNKFvZnZ2dnUVZ_umdnZ2d@adelphia.com...
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

[Gary Keramidas]

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" <whooshbopbang4@adelphia.net> wrote in message news:fvednRDDM5n4vlrZnZ2dnUVZ_rydnZ2d@adelphia.com...
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 news:evwMqXGsGHA.1224@TK2MSFTNGP03.phx.gbl...
forgot the small function, too

--


Gary


"Dean" <whooshbopbang4@adelphia.net> wrote in message news:7JudneCFbNGwflvZnZ2dnUVZ_oOdnZ2d@adelphia.com...
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" <dguillett1@austin.rr.com> wrote in message news:OzuElqEsGHA.1288@TK2MSFTNGP02.phx.gbl...
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:B21>MIN(B:B),B1:B21))

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Dean" <whooshbopbang4@adelphia.net> wrote in message news:P4udnZgRQqjNKFvZnZ2dnUVZ_umdnZ2d@adelphia.com...
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

[Dean]

Indeed, I did! Thanks all!
"Gary Keramidas" <GKeramidasATmsn.com> wrote in message news:%23WLlUfLsGHA.1580@TK2MSFTNGP05.phx.gbl...
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" <whooshbopbang4@adelphia.net> wrote in message news:fvednRDDM5n4vlrZnZ2dnUVZ_rydnZ2d@adelphia.com...
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 news:evwMqXGsGHA.1224@TK2MSFTNGP03.phx.gbl...
forgot the small function, too

--


Gary


"Dean" <whooshbopbang4@adelphia.net> wrote in message news:7JudneCFbNGwflvZnZ2dnUVZ_oOdnZ2d@adelphia.com...
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" <dguillett1@austin.rr.com> wrote in message news:OzuElqEsGHA.1288@TK2MSFTNGP02.phx.gbl...
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:B21>MIN(B:B),B1:B21))

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Dean" <whooshbopbang4@adelphia.net> wrote in message news:P4udnZgRQqjNKFvZnZ2dnUVZ_umdnZ2d@adelphia.com...
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