×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Highlighting MIN value greater than zero

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.

RE: Highlighting MIN value greater than zero

What happens when you try to do both?

RE: Highlighting MIN value greater than zero

(OP)
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.

RE: Highlighting MIN value greater than zero

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: Eng-Tips.com Forum Policies

RE: Highlighting MIN value greater than zero

(OP)
"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.

 

RE: Highlighting MIN value greater than zero

(OP)
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.

RE: Highlighting MIN value greater than zero

Quote (trackfiend):


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

RE: Highlighting MIN value greater than zero

(OP)
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.

RE: Highlighting MIN value greater than zero

Quote (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.

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

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.
 

RE: Highlighting MIN value greater than zero

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources