T
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
show the lowest positive number.
Thank You
D
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
100>0,D1100))
ctrl+shift+enter and will not work on full columns.
=MIN(IF(D1
100>0,D1100))
C
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
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
T
Tommy
I'm sorry I needed the lowest positive number above 0. When I use this it
comes back with the lowest -number.
Thanks
comes back with the lowest -number.
Thanks
T
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
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
B
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)
formula, use Ctrl-Shift-Enter not just Enter to commit it.
--
HTH
RP
(remove nothere from the email address if mailing direct)
C
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
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
D
Don Guillett
Chuck,
Don't you think a helper column with a lot of unnecessary formulas seems
like over doing it?
Don't you think a helper column with a lot of unnecessary formulas seems
like over doing it?
C
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
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
B
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
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
D
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.
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.
C
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
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
B
Bob Phillips
Hi Chuck,
So we're not going o get rid of you then?
Amen to that. That is why I try to read as many as I can, as well as those I
respond to.
I agree. Much better than the web based forums.
Regards
Bob
So we're not going o get rid of you then?
Amen to that. That is why I try to read as many as I can, as well as those I
respond to.
I agree. Much better than the web based forums.
Regards
Bob