# Return the min value greater than 0

1. ## 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

2. ## 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

3. ## 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. ## Re: Return the min value greater than 0

Maybe something like:

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

5. ## Re: Return the min value greater than 0

BrownBoy,

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

Jim O

6. ## Re: Return the min value greater than 0

Good, thanks for the rep.

7. ## 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.

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

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