+ Reply to Thread
Results 1 to 3 of 3

Ignore zero in min function

  1. #1
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    Office 365 Windows 10
    Posts
    2,293

    Ignore zero in min function

    To highlight largest and smallest values in a worksheet I have used large and small function to calculate and then and index to highlight name attached

    Please Login or Register  to view this content.
    it presented a problem with nothing being shown for the smallest value when zeros are present in the range. I corrected this by amending the small function to
    Please Login or Register  to view this content.
    but now it will not pick up the name for the resulting smallest numberhow can I amend the index to do this??

    Any help appreciated.
    Last edited by nigelog; 12-10-2009 at 10:57 AM.

  2. #2
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191

    Re: Ignore zero in min function

    Have you tried: =INDEX(B3:B41,MATCH(SMALL(I3:I41,COUNTIF(I3:I41,"<=0")+1),I3:I41,0))

  3. #3
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    Office 365 Windows 10
    Posts
    2,293

    Re: Ignore zero in min function

    Cheers for the prompt reply darkyam - spot on

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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