get lowest positive number

[Tommy]

I have a cell that has number ranges from -37 to 88, what is the formula to
show the lowest positive number.

Thank You

[Don Guillett]

try this. It is an array formula so must be entered/edited with
ctrl+shift+enter and will not work on full columns.

=MIN(IF(D1:D100>0,D1:D100))

--
Don Guillett
SalesAid Software
[email protected]
"Tommy" <[email protected]> wrote in message
news:[email protected]...
> I have a cell that has number ranges from -37 to 88, what is the formula
to
> show the lowest positive number.
>
> Thank You

[CLR]

Not sure what you mean by having a "cell" with a range of numbers in
it........ an example would be appreciated if that's really the case.......

On the other hand, if you meant that you have a "Column" with a range of
numbers in it, then assuming it's column A, use a helper column in column B1
put this formula.........=IF(A1>0,A1,"") and copy it down, then in C1 put
this formula.........=MIN(B:B)

If you meant you have a "row" with a range of numbers in it, then assuming
it's Row 1, put this formula in A2 and copy
across.......=IF(A1>0,A1,"").........then in A3 put this
formula..............=MIN(2:2)


Vaya con Dios,
Chuck, CABGx3


"Tommy" <[email protected]> wrote in message
news:[email protected]...
> I have a cell that has number ranges from -37 to 88, what is the formula
to
> show the lowest positive number.
>
> Thank You

[Tommy]

I'm sorry I needed the lowest positive number above 0. When I use this it
comes back with the lowest -number.

Thanks

"Don Guillett" wrote:

> try this. It is an array formula so must be entered/edited with
> ctrl+shift+enter and will not work on full columns.
>
> =MIN(IF(D1:D100>0,D1:D100))
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "Tommy" <[email protected]> wrote in message
> news:[email protected]...
> > I have a cell that has number ranges from -37 to 88, what is the formula
> to
> > show the lowest positive number.
> >
> > Thank You
>
>
>

[Tommy]

CLR

It was a column and it worked great. One more question is there a way to
sort the same numbers from lowest above 0. example 55, 56, 70, 71 and so
on.

Thank you

"CLR" wrote:

> Not sure what you mean by having a "cell" with a range of numbers in
> it........ an example would be appreciated if that's really the case.......
>
> On the other hand, if you meant that you have a "Column" with a range of
> numbers in it, then assuming it's column A, use a helper column in column B1
> put this formula.........=IF(A1>0,A1,"") and copy it down, then in C1 put
> this formula.........=MIN(B:B)
>
> If you meant you have a "row" with a range of numbers in it, then assuming
> it's Row 1, put this formula in A2 and copy
> across.......=IF(A1>0,A1,"").........then in A3 put this
> formula..............=MIN(2:2)
>
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
> "Tommy" <[email protected]> wrote in message
> news:[email protected]...
> > I have a cell that has number ranges from -37 to 88, what is the formula
> to
> > show the lowest positive number.
> >
> > Thank You
>
>
>

[Bob Phillips]

That is because you probably didn't array enter it. After typing the
formula, use Ctrl-Shift-Enter not just Enter to commit it.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Tommy" <[email protected]> wrote in message
news:[email protected]...
> I'm sorry I needed the lowest positive number above 0. When I use this it
> comes back with the lowest -number.
>
> Thanks
>
> "Don Guillett" wrote:
>
> > try this. It is an array formula so must be entered/edited with
> > ctrl+shift+enter and will not work on full columns.
> >
> > =MIN(IF(D1:D100>0,D1:D100))
> >
> > --
> > Don Guillett
> > SalesAid Software
> > [email protected]
> > "Tommy" <[email protected]> wrote in message
> > news:[email protected]...
> > > I have a cell that has number ranges from -37 to 88, what is the
formula
> > to
> > > show the lowest positive number.
> > >
> > > Thank You
> >
> >
> >

[CLR]

Sort the column in the normal way, select a cell therein and click either
the A-Z or Z-A icon on the tool bar, then Select a cell in the column, then
do Data > Filter > AutoFilter, and then click the dropdown arrow at the top
of the column and select Custom > greater than > 0...........to return to
all the data to view, just do Data > Filter > AutoFilter again........it's a
toggle.

Vaya con Dios,
Chuck, CABGx3


"Tommy" <[email protected]> wrote in message
news:[email protected]...
> CLR
>
> It was a column and it worked great. One more question is there a way to
> sort the same numbers from lowest above 0. example 55, 56, 70, 71 and
so
> on.
>
> Thank you
>
> "CLR" wrote:
>
> > Not sure what you mean by having a "cell" with a range of numbers in
> > it........ an example would be appreciated if that's really the
case.......
> >
> > On the other hand, if you meant that you have a "Column" with a range of
> > numbers in it, then assuming it's column A, use a helper column in
column B1
> > put this formula.........=IF(A1>0,A1,"") and copy it down, then in C1
put
> > this formula.........=MIN(B:B)
> >
> > If you meant you have a "row" with a range of numbers in it, then
assuming
> > it's Row 1, put this formula in A2 and copy
> > across.......=IF(A1>0,A1,"").........then in A3 put this
> > formula..............=MIN(2:2)
> >
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> > "Tommy" <[email protected]> wrote in message
> > news:[email protected]...
> > > I have a cell that has number ranges from -37 to 88, what is the
formula
> > to
> > > show the lowest positive number.
> > >
> > > Thank You
> >
> >
> >

[Don Guillett]

Chuck,
Don't you think a helper column with a lot of unnecessary formulas seems
like over doing it?

--
Don Guillett
SalesAid Software
[email protected]
"CLR" <[email protected]> wrote in message
news:[email protected]...
> Not sure what you mean by having a "cell" with a range of numbers in
> it........ an example would be appreciated if that's really the
case.......
>
> On the other hand, if you meant that you have a "Column" with a range of
> numbers in it, then assuming it's column A, use a helper column in column
B1
> put this formula.........=IF(A1>0,A1,"") and copy it down, then in C1 put
> this formula.........=MIN(B:B)
>
> If you meant you have a "row" with a range of numbers in it, then assuming
> it's Row 1, put this formula in A2 and copy
> across.......=IF(A1>0,A1,"").........then in A3 put this
> formula..............=MIN(2:2)
>
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
> "Tommy" <[email protected]> wrote in message
> news:[email protected]...
> > I have a cell that has number ranges from -37 to 88, what is the formula
> to
> > show the lowest positive number.
> >
> > Thank You
>
>

[CLR]

Not really Don, and I'll tell you why. First off, let me say that I hold
you in the highest esteem for your Excel skill. I read and learn from your
responses to others, and you have answered several questions for me as well.
This answer to your question is in no way meant to be argumentative.

I just think that the "best" technical answer is not necessarily always the
best answer. Of course I recognize that your Array-Formula is the "better"
way to solve this particular Excel problem technically, but it's only
"better" if one has the skill level to be able to use "and" feel comfortable
enough with it so that when they have to modify it some time down the road,
they will still understand how it worked and be able to deal with it. Many
many times I do things in my own work that is not done the "best" way, but
it's the way I can remember how to do at the time and it works and I feel
comfortable with it, so I do it.

I've sent questions of my own to these newsgroups and sometimes receive
answers that supposedly are the "best" ones, but I don't choose to use some
of them because I don't understand them myself, and I don't always have the
time to try to figure them out.

Besides being able to get answers here, I think the greatest benefit of
these newsgroups is that an OP can usually get not just the "best" answer,
but several answers to his question and then he can choose which one best
fits his needs and current skill-level.

My best to you and yours........

Vaya con Dios,
Chuck, CABGx3



"Don Guillett" <[email protected]> wrote in message
news:#[email protected]...
> Chuck,
> Don't you think a helper column with a lot of unnecessary formulas seems
> like over doing it?
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "CLR" <[email protected]> wrote in message
> news:[email protected]...
> > Not sure what you mean by having a "cell" with a range of numbers in
> > it........ an example would be appreciated if that's really the
> case.......
> >
> > On the other hand, if you meant that you have a "Column" with a range of
> > numbers in it, then assuming it's column A, use a helper column in
column
> B1
> > put this formula.........=IF(A1>0,A1,"") and copy it down, then in C1
put
> > this formula.........=MIN(B:B)
> >
> > If you meant you have a "row" with a range of numbers in it, then
assuming
> > it's Row 1, put this formula in A2 and copy
> > across.......=IF(A1>0,A1,"").........then in A3 put this
> > formula..............=MIN(2:2)
> >
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> > "Tommy" <[email protected]> wrote in message
> > news:[email protected]...
> > > I have a cell that has number ranges from -37 to 88, what is the
formula
> > to
> > > show the lowest positive number.
> > >
> > > Thank You
> >
> >
>
>

[Bob Phillips]

Chuck,

Good points, but just picking up on one thing you say, hopefully in an
equally non-argumentative way..

You say '... but I don't choose to use some of them because I don't
understand them myself, and I don't always have the time to try to figure
them out ...'.

How about asking for an explanation? Two benefits from this:
- sometimes the answers that are given seem obvious to the responder but not
to others, so by asking for an explanation, others get a better
understanding as well
- it will help to develop your own skills, making you more self-reliant in
future, and who knows we might even see less of you :-) (as a poster not is,
maybe more as a responder).

Best Regards

Bob


"CLR" <[email protected]> wrote in message
news:%[email protected]...
> Not really Don, and I'll tell you why. First off, let me say that I hold
> you in the highest esteem for your Excel skill. I read and learn from
your
> responses to others, and you have answered several questions for me as
well.
> This answer to your question is in no way meant to be argumentative.
>
> I just think that the "best" technical answer is not necessarily always
the
> best answer. Of course I recognize that your Array-Formula is the
"better"
> way to solve this particular Excel problem technically, but it's only
> "better" if one has the skill level to be able to use "and" feel
comfortable
> enough with it so that when they have to modify it some time down the
road,
> they will still understand how it worked and be able to deal with it.
Many
> many times I do things in my own work that is not done the "best" way, but
> it's the way I can remember how to do at the time and it works and I feel
> comfortable with it, so I do it.
>
> I've sent questions of my own to these newsgroups and sometimes receive
> answers that supposedly are the "best" ones, but I don't choose to use
some
> of them because I don't understand them myself, and I don't always have
the
> time to try to figure them out.
>
> Besides being able to get answers here, I think the greatest benefit of
> these newsgroups is that an OP can usually get not just the "best" answer,
> but several answers to his question and then he can choose which one best
> fits his needs and current skill-level.
>
> My best to you and yours........
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
> "Don Guillett" <[email protected]> wrote in message
> news:#[email protected]...
> > Chuck,
> > Don't you think a helper column with a lot of unnecessary formulas seems
> > like over doing it?
> >
> > --
> > Don Guillett
> > SalesAid Software
> > [email protected]
> > "CLR" <[email protected]> wrote in message
> > news:[email protected]...
> > > Not sure what you mean by having a "cell" with a range of numbers in
> > > it........ an example would be appreciated if that's really the
> > case.......
> > >
> > > On the other hand, if you meant that you have a "Column" with a range
of
> > > numbers in it, then assuming it's column A, use a helper column in
> column
> > B1
> > > put this formula.........=IF(A1>0,A1,"") and copy it down, then in C1
> put
> > > this formula.........=MIN(B:B)
> > >
> > > If you meant you have a "row" with a range of numbers in it, then
> assuming
> > > it's Row 1, put this formula in A2 and copy
> > > across.......=IF(A1>0,A1,"").........then in A3 put this
> > > formula..............=MIN(2:2)
> > >
> > >
> > > Vaya con Dios,
> > > Chuck, CABGx3
> > >
> > >
> > > "Tommy" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I have a cell that has number ranges from -37 to 88, what is the
> formula
> > > to
> > > > show the lowest positive number.
> > > >
> > > > Thank You
> > >
> > >
> >
> >
>
>

[Don Guillett]

Aw shucks, thanks for the kudos. The point I was making is that the workbook
gets large and takes a long time to calculate a lot of formulas as opposed
to one.
When I was driving Formula Fords I always tried to learn the fastest line
around the track.

--
Don Guillett
SalesAid Software
[email protected]
"CLR" <[email protected]> wrote in message
news:%[email protected]...
> Not really Don, and I'll tell you why. First off, let me say that I hold
> you in the highest esteem for your Excel skill. I read and learn from
your
> responses to others, and you have answered several questions for me as
well.
> This answer to your question is in no way meant to be argumentative.
>
> I just think that the "best" technical answer is not necessarily always
the
> best answer. Of course I recognize that your Array-Formula is the
"better"
> way to solve this particular Excel problem technically, but it's only
> "better" if one has the skill level to be able to use "and" feel
comfortable
> enough with it so that when they have to modify it some time down the
road,
> they will still understand how it worked and be able to deal with it.
Many
> many times I do things in my own work that is not done the "best" way, but
> it's the way I can remember how to do at the time and it works and I feel
> comfortable with it, so I do it.
>
> I've sent questions of my own to these newsgroups and sometimes receive
> answers that supposedly are the "best" ones, but I don't choose to use
some
> of them because I don't understand them myself, and I don't always have
the
> time to try to figure them out.
>
> Besides being able to get answers here, I think the greatest benefit of
> these newsgroups is that an OP can usually get not just the "best" answer,
> but several answers to his question and then he can choose which one best
> fits his needs and current skill-level.
>
> My best to you and yours........
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
> "Don Guillett" <[email protected]> wrote in message
> news:#[email protected]...
> > Chuck,
> > Don't you think a helper column with a lot of unnecessary formulas seems
> > like over doing it?
> >
> > --
> > Don Guillett
> > SalesAid Software
> > [email protected]
> > "CLR" <[email protected]> wrote in message
> > news:[email protected]...
> > > Not sure what you mean by having a "cell" with a range of numbers in
> > > it........ an example would be appreciated if that's really the
> > case.......
> > >
> > > On the other hand, if you meant that you have a "Column" with a range
of
> > > numbers in it, then assuming it's column A, use a helper column in
> column
> > B1
> > > put this formula.........=IF(A1>0,A1,"") and copy it down, then in C1
> put
> > > this formula.........=MIN(B:B)
> > >
> > > If you meant you have a "row" with a range of numbers in it, then
> assuming
> > > it's Row 1, put this formula in A2 and copy
> > > across.......=IF(A1>0,A1,"").........then in A3 put this
> > > formula..............=MIN(2:2)
> > >
> > >
> > > Vaya con Dios,
> > > Chuck, CABGx3
> > >
> > >
> > > "Tommy" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I have a cell that has number ranges from -37 to 88, what is the
> formula
> > > to
> > > > show the lowest positive number.
> > > >
> > > > Thank You
> > >
> > >
> >
> >
>
>

[CLR]

Hi Bob........

Of course you're right about asking for an explanation. When time permits
that's my preference also.....I only meant that when I ask a question for my
personal need, I'm usually in a hurry and thinking only of myself and my
problem. And, if I get three answers that work and one or two are beyond my
present skill-level, I usually go with the one that I can easily understand
and implement quickly. Even if it's not "the best", it's the best for me at
that moment. On other occasions, when time permits, and someone responds
with something that strikes my fancy or an area I am currently interested
in, I will delve deeper, and then rejoice in the flood of knowledge that
comes with each new level of understanding of Excel. We have no way of
knowing, usually, which mode the OP might be in when they write their often
cryptic questions. That's why I'm so in favor of giving them several
options. It's just amazing how often one responder or another will see
something in the question that will allow them to "hit the nail right on the
head" with their response. This system of newsgroups is the "best thing to
come along since sliced bread". The Responders are all to be commended for
their patience, their understanding, and for the selfless sharing of their
time and knowledge.

Vaya con Dios,
Chuck, CABGx3



"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> Chuck,
>
> Good points, but just picking up on one thing you say, hopefully in an
> equally non-argumentative way..
>
> You say '... but I don't choose to use some of them because I don't
> understand them myself, and I don't always have the time to try to figure
> them out ...'.
>
> How about asking for an explanation? Two benefits from this:
> - sometimes the answers that are given seem obvious to the responder but
not
> to others, so by asking for an explanation, others get a better
> understanding as well
> - it will help to develop your own skills, making you more self-reliant in
> future, and who knows we might even see less of you :-) (as a poster not
is,
> maybe more as a responder).
>
> Best Regards
>
> Bob
>
>
> "CLR" <[email protected]> wrote in message
> news:%[email protected]...
> > Not really Don, and I'll tell you why. First off, let me say that I
hold
> > you in the highest esteem for your Excel skill. I read and learn from
> your
> > responses to others, and you have answered several questions for me as
> well.
> > This answer to your question is in no way meant to be argumentative.
> >
> > I just think that the "best" technical answer is not necessarily always
> the
> > best answer. Of course I recognize that your Array-Formula is the
> "better"
> > way to solve this particular Excel problem technically, but it's only
> > "better" if one has the skill level to be able to use "and" feel
> comfortable
> > enough with it so that when they have to modify it some time down the
> road,
> > they will still understand how it worked and be able to deal with it.
> Many
> > many times I do things in my own work that is not done the "best" way,
but
> > it's the way I can remember how to do at the time and it works and I
feel
> > comfortable with it, so I do it.
> >
> > I've sent questions of my own to these newsgroups and sometimes receive
> > answers that supposedly are the "best" ones, but I don't choose to use
> some
> > of them because I don't understand them myself, and I don't always have
> the
> > time to try to figure them out.
> >
> > Besides being able to get answers here, I think the greatest benefit of
> > these newsgroups is that an OP can usually get not just the "best"
answer,
> > but several answers to his question and then he can choose which one
best
> > fits his needs and current skill-level.
> >
> > My best to you and yours........
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> >
> > "Don Guillett" <[email protected]> wrote in message
> > news:#[email protected]...
> > > Chuck,
> > > Don't you think a helper column with a lot of unnecessary formulas
seems
> > > like over doing it?
> > >
> > > --
> > > Don Guillett
> > > SalesAid Software
> > > [email protected]
> > > "CLR" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Not sure what you mean by having a "cell" with a range of numbers in
> > > > it........ an example would be appreciated if that's really the
> > > case.......
> > > >
> > > > On the other hand, if you meant that you have a "Column" with a
range
> of
> > > > numbers in it, then assuming it's column A, use a helper column in
> > column
> > > B1
> > > > put this formula.........=IF(A1>0,A1,"") and copy it down, then in
C1
> > put
> > > > this formula.........=MIN(B:B)
> > > >
> > > > If you meant you have a "row" with a range of numbers in it, then
> > assuming
> > > > it's Row 1, put this formula in A2 and copy
> > > > across.......=IF(A1>0,A1,"").........then in A3 put this
> > > > formula..............=MIN(2:2)
> > > >
> > > >
> > > > Vaya con Dios,
> > > > Chuck, CABGx3
> > > >
> > > >
> > > > "Tommy" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > I have a cell that has number ranges from -37 to 88, what is the
> > formula
> > > > to
> > > > > show the lowest positive number.
> > > > >
> > > > > Thank You
> > > >
> > > >
> > >
> > >
> >
> >
>
>

[Bob Phillips]

Hi Chuck,

"CLR" <[email protected]> wrote in message
news:[email protected]...

> Of course you're right about asking for an explanation. When time permits
> that's my preference also.....I only meant that when I ask a question for
my
> personal need, I'm usually in a hurry and thinking only of myself and my
> problem. And, if I get three answers that work and one or two are beyond
my
> present skill-level, I usually go with the one that I can easily
understand
> and implement quickly. Even if it's not "the best", it's the best for me
at
> that moment.

So we're not going o get rid of you then? :-)

> We have no way of knowing, usually, which mode the OP
> might be in when they write their often cryptic questions.
> That's why I'm so in favor of giving them several options.
> It's just amazing how often one responder or another will see
> something in the question that will allow them to "hit the nail right
> on the head" with their response.

Amen to that. That is why I try to read as many as I can, as well as those I
respond to.

> This system of newsgroups is the
> "best thing to come along since sliced bread".

I agree. Much better than the web based forums.


Regards

Bob