Results 1 to 2 of 2
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 105 Times in 90 Posts

    Minimum, excluding zeros

    I have been racking my brains, without success, to find a formula which shows the minimum value in a contiguous block of cells - but excluding any zero or missing values.

    I can do this easily enough with VBA but it would be much more convenient to have a formula I can copy around.

    Thanks

    Darn - as soon as I posted this I went back to the problem and got to 'S' in the function list: this does it:

    =SMALL(Cell1:Cell2,COUNTIF(Cell1:Cell2,0)+1)
    Last edited by MartinM; 2011-09-29 at 12:29. Reason: Found the solution !

  2. #2
    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
    Try the array formula (confirm with ctrl-shift-enter, and change to the appropriate range):
    =MIN(IF(A1:C7<>0,A1:C7))

    It will ignore zeroes, text, blanks cells, etc

    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
  •