Highlighting MIN value greater than zero 1

[Trackfiend]

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.

 

[melone]

What happens when you try to do both?

 

[IRstuff]

Why can't you just conditionally format for equivalence to the cell containing the found minimum value?

TTFN

FAQ731-376

 

[Trackfiend]

If I try to input the first equation:

{=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.

 

[IRstuff]

That's not what suggested. Your found minimum is located in some cell, say B1. So, your conditional formatting will highlight the cell that is equal to B1. The conditional formatting command should look like

Cell Value Is | equal to | =$B$1

TTFN

FAQ731-376

 

[Trackfiend]

"Why can't you just conditionally format for equivalence to the cell containing the found minimum value?"

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.

 

[Trackfiend]

Sorry if the posts are timed oddly. I would be trying to post a remark but wouldn't get it out before you had responded.

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.

 

[yogia]

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))

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

 

[Trackfiend]

It did work. In using the conditional formatting tool, I did not have to use the "array" function. I simply typed in the formula stated in my last post. If I were looking to find the minimum value greater than zero and post this value in another cell, then I would have to use the "array" function as stated previously. I have no idea why the "array" function does nothing in the conditional formatting tool box.

 

[yogia]

It did work. In using the conditional formatting tool, I did not have to use the "array" function. I simply typed in the formula stated in my last post. If I were looking to find the minimum value greater than zero and post this value in another cell, then I would have to use the "array" function as stated previously. I have no idea why the "array" function does nothing in the conditional formatting tool box.

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

 

[JaredS]

Use the reciprocal, calc a column 1/A1, 1/A2 and so on

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.

 

[Trackfiend]

yogia - "'It' does not work for me as Conditional Formatting formula."

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))