Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Voorhees, New Jersey
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    non-zero MIN (Excel 2003)

    I want a cell to indicate the non-zero minimum of a group of non-contiguous cells in the same row. (Some of the cells are zero values.)

    For instance I want cell G3 to display the non-zero minimum of the values in cells A3, C3, & E3.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: non-zero MIN (Excel 2003)

    You could use an array formula like this (confirm with Ctrl+Shift+Enter instead of just Enter):

    =MIN(IF((A3:F3<>0)*(MOD(COLUMN(A3:F3),2)=1),A3:F3) )

  3. #3
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Voorhees, New Jersey
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: non-zero MIN (Excel 2003)

    Thanks for the quick reply!
    I'll try it.
    Does it matter that I have other number-containing cells "like such as" at B3, D3, and F3 that shouldn't be included?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: non-zero MIN (Excel 2003)

    The condition MOD(COLUMN(A3:F3),2)=1 causes only cells with an odd column number to be included; cells with an even column number are ignored.

    Explanation:
    COLUMN(...) returns the column number of a cell.
    MOD(COLUMN(...),2) returns the remainder of the column number after division by 2, i.e. 0 for even column numbers and 1 for odd column numbers.
    MOD(COLUMN(A3:F3),2)=1 is true if and only if the column number is odd.

    The other condition A3:F3<>0 ensures that only non-zero values are included.

  5. #5
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Voorhees, New Jersey
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: non-zero MIN (Excel 2003)

    Thanks for the clear explanation.

    I wasn't familiar with the COLUMN function...I'm guessing there's also a ROW function...

    I guess I also could create a copy of only the odd columns (so that they would be contiguous) but I was trying to avoid that...
    I'll let you know how it works out after I get back to the office.

    Again, thanks for your help!

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: non-zero MIN (Excel 2003)

    Yep, there is also a ROW function that returns the row number of the argument (or of the cell containing the formula, if no argument is provided).
    If you look up either of these functions in the Excel help, the See Also link will mention the other.

  7. #7
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Voorhees, New Jersey
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: non-zero MIN (Excel 2003)

    Hans,

    I tried your formula and it does the trick!
    Thanks very much for your assistance with this.

    Regards,

    Tom

Posting Permissions

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