Highlighting MIN value greater than zero
Highlighting MIN value greater than zero
(OP)
I can't seem to get this to work. Here's the situation. I have a column of numerical values, say 0 to 100. I would like to find the minimum value that is greater than zero and would also like to highlight the cell (or row if possible) it is grabbing the number from.
I can get the MIN value greater than zero with the following formula:
{=MIN(IF(A1:A101>0,A1:A101))}
I can also highlight the cell with the MIN value using the conditional formatting tool, setting it "equal to" and typing the following formula:
=A1=MIN(A1:A101)
What I can't seem to do is do both functions, highlight and give MIN value greater than zero, at the same time. Any suggestions would be extremely helpful.
I can get the MIN value greater than zero with the following formula:
{=MIN(IF(A1:A101>0,A1:A101))}
I can also highlight the cell with the MIN value using the conditional formatting tool, setting it "equal to" and typing the following formula:
=A1=MIN(A1:A101)
What I can't seem to do is do both functions, highlight and give MIN value greater than zero, at the same time. Any suggestions would be extremely helpful.





RE: Highlighting MIN value greater than zero
RE: Highlighting MIN value greater than zero
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
RE: Highlighting MIN value greater than zero
{=MIN(IF(A1:A101>0,A1:A101))}
in the conditional formatting tool box nothing happens. And yes, I've assigned a pattern (color). I've tried a few combinations, such as adding the "=A1" to it but to no avail.
RE: Highlighting MIN value greater than zero
Cell Value Is | equal to | =$B$1
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
RE: Highlighting MIN value greater than zero
The value range that I have is 0 to 3125. I used 0 to 101 for simplicity. Here is a more detailed description of the data set:
Head Flow
1.1 1000
2.4 2455
1.8 2100
0.1 0
5.5 3100
The intent is to get the MIN value greater than zero of the flow in order to obtain the corresponding head value. There are 3125 different flow values and I would rather not have to scroll through them all to see the corresponding head value.
RE: Highlighting MIN value greater than zero
Thanks for the suggestion, it helped. I simply used the conditional formatting tool but didn't use the array or {}. The following formula worked:
=MIN(IF(A1:A101>0,A1:A101))
Thanks for the responses.
RE: Highlighting MIN value greater than zero
Hi trackfiend:
I don't think using ... =MIN(IF(A1:A101>0,A1:A101)) as Conditional Formatting formula will work.
Would you please clarify, if you did use a formulation for Conditional Formatting other than reference to the cell with the related MINIMUM value, what is that formulation.
Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC
http://www.energyefficientbuild.com
RE: Highlighting MIN value greater than zero
RE: Highlighting MIN value greater than zero
Hi Trackfiend:
The 'It' you refered to in your post is the Conditional Formatting formula:
=MIN(IF(A1:A101>0,A1:A101))
All I am saying is that the 'It' does not work me as Conditional Formatting formula. What does work for me is ...
=A1=MIN(IF(A$1:A$101>0,A$1:A$101))
Now mind you I am using Excel 2000 ... I don't know what version of Excel you are using.
As far as using the formula as an array formula in Conditional Formatting, that is right ... one can not enter the formula there as an array formula.
If you do have some different information please do post that for my information.
Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC
http://www.energyefficientbuild.com
RE: Highlighting MIN value greater than zero
then find =1/MAX(B1:B1000)
this works because the reciprocal of the smallest positive number will produce a large positive number whereas a small negative number would produce a large negative number.
RE: Highlighting MIN value greater than zero
Did you set the cell value is "equal to"?
Did you change the formatting of the box to a different pattern or color in order to identify the particular value you are looking for?
If your formula works, then use what works. However, I don't see why the following formula would not work:
=MIN(IF(A1:A101>0,A1:A101))
Maybe try =MIN(IF($A$1:$A$101>0,$A$1:$A$101))