+ Reply to Thread
Results 1 to 2 of 2

How can I ignore BLANK or ZERO cells in a MIN/IF function??

  1. #1
    Registered User
    Join Date
    02-02-2012
    Location
    Spokane, USA
    MS-Off Ver
    Excel for Mac 2011
    Posts
    1

    How can I ignore BLANK or ZERO cells in a MIN/IF function??

    I AM TRYING TO USE AN IF FUNCTION TO LOOK FOR THE LOWEST (MIN) NUMBER IN A RANGE OF CELLS THAT IS LESS THAN MY SELECTED CELL. EXAMPLE: =IF((MIN($F$4:$F$18)<$I$4),TRUE,FALSE). Lets say that $I$4 equals 60, and the lowest number in the F4:F18 array is 61. This should return FALSE, but since some of the cells in F4:F18 have "0" or are Blank, the formula returns TRUE even though I DO NOT WANT IT TO! However, I must have some "0" or Blank cells in this F4:F18 cell range.

    So, I tried to combine AND with IF to argue something like this: =IF(AND(MIN($F$4:$F$18)>0,MIN($F$4:$F$18)<$I$4),TRUE,FALSE). This way I get TRUE if values in my cell range are "greater than 0" AND less than my selected cell ($I$4). PROBLEM IS THAT this blasted Excel will only return FALSE, even though both these arguments are TRUE!! How can I get this argument =IF(AND(MIN($F$4:$F$18)>0,MIN($F$4:$F$18)<$I$4),TRUE,FALSE) to return TRUE when some cells in my array have a "0" in them?

    THANK YOU IN ADVANCE TO ANYONE WHO CAN HELP ME RESOLVE THIS!

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: How can I ignore BLANK or ZERO cells in a MIN/IF function??

    Hi showmethemoney,

    Welcome to the forum.

    Use following formula:-

    [code]
    {=MIN(IF((F4:F18>0)*(F4:F18<>"")*(F4:F18<I4),$F$4:$F$18,""))}

    [/code}

    Above is an array formula and need to be entered with Ctrl + Shift+ Enter key combination. Thanks.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

+ 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