Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    ignoring 0's and get min values (Excel 00)

    I thought I could find a post on this, so sorry if there is one.

    I have a range of numbers
    0
    1
    2
    3
    0
    3

    in the range Iwould like the min value not including 0's
    so in the above example the min is 1
    how so Iwrite a function to do this in excel
    thank you ahead

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ignoring 0's and get min values (Excel 00)

    See the thread starting with <post:=593,750>post 593,750</post:>.
    Legare Coleman

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: ignoring 0's and get min values (Excel 00)

    Does this do what you want?

    <pre>Option Explicit
    Function MinNo0(rng As Range)
    Dim rCell As Range
    Dim dMin As Double
    dMin = 9.99999999999999E+307
    For Each rCell In rng
    If IsNumeric(rCell) Then
    If rCell < dMin And _
    rCell <> 0 Then
    dMin = rCell
    End If
    End If
    Next
    MinNo0 = dMin
    Set rCell = Nothing
    End Function</pre>


    Use like:
    =MinNo0(A1:A10)

    Steve

Posting Permissions

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