+ Reply to Thread
Results 1 to 7 of 7

Return the min value greater than 0

  1. #1
    Forum Contributor
    Join Date
    05-10-2011
    Location
    Central Ohio
    MS-Off Ver
    Excel 2000, and 2010
    Posts
    654

    Return the min value greater than 0

    I have a column in a sheet, populated from a drop down, that contains time values formated as m:ss.000 some of the values equate to 0 and are causing a problem with my formula. I am using =MIN(AJ:AJ) and it works fine except when a 0 pop up. I need to modify the MIN formula to say "Return the minimum value greater than 0". Any ideas?

    Thanks

    Jim O
    Last edited by JO505; 09-18-2014 at 05:20 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,213

    Re: Return the min value greater than 0

    =if(a1>0,MIN(AJ:AJ),"")


    sorry, brain fart, that's useless to you. i'll edit this with something better in a minute maybe
    Last edited by Speshul; 09-18-2014 at 05:03 PM.
    You should hit F5, because chances are I've edited this post at least 5 times.
    Example of Array Formulas
    Quote Originally Posted by Jacc View Post
    Sorry, your description makes no sense. I just made some formula that looks interesting cause I had nothing else to do.
    Click the * below on any post that helped you.

  3. #3
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,213

    Re: Return the min value greater than 0

    Use Control + Shift + Enter to enter this Array Formula

    =MIN(IFERROR(1/(AJ:AJ<>0)*AJ:AJ,""))

  4. #4
    Forum Contributor
    Join Date
    03-06-2013
    Location
    Salt Lake City, Utah
    MS-Off Ver
    2007, 2010
    Posts
    127

    Re: Return the min value greater than 0

    Maybe something like:

    =SMALL(AJ:AJ,COUNTIF(AJ:AJ,0)+1)
    BrownBoy

    If happy, mark "SOVLED" & add to "REP"

  5. #5
    Forum Contributor
    Join Date
    05-10-2011
    Location
    Central Ohio
    MS-Off Ver
    Excel 2000, and 2010
    Posts
    654

    Re: Return the min value greater than 0

    BrownBoy,

    That seems to do the trick.Thank you for the response.

    Jim O

  6. #6
    Forum Contributor
    Join Date
    03-06-2013
    Location
    Salt Lake City, Utah
    MS-Off Ver
    2007, 2010
    Posts
    127

    Re: Return the min value greater than 0

    Good, thanks for the rep.

  7. #7
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Return the min value greater than 0

    Here's another one...

    Array entered**:

    =MIN(IF(AJ2:AJ100>0,AJ2:AJ100))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    You should avoid using entire columns as range references in array formulas. Use smaller specific ranges.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. If anyone of these cells are greater than 10 return a yes, if not no
    By Jr66 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-09-2013, 06:42 PM
  2. [SOLVED] if the difference is less than or greater than 50 return value
    By deancorleone in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-02-2013, 07:29 PM
  3. [SOLVED] Return next value in a column greater than zero
    By sordid in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-05-2012, 09:29 AM
  4. Return the Greater Value
    By Ocean Zhang in forum Excel General
    Replies: 1
    Last Post: 08-27-2011, 03:19 PM
  5. vlookup must return a value that is greater than
    By BG in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-03-2006, 07:15 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1