+ Reply to Thread
Results 1 to 12 of 12

Exclude Zero's from Min Function nested inside an index+Match... all within CELL funtion

  1. #1
    Forum Contributor
    Join Date
    12-20-2011
    Location
    United States, Ohio
    MS-Off Ver
    Excel 2010
    Posts
    293

    Question Exclude Zero's from Min Function nested inside an index+Match... all within CELL funtion

    I need help excluding the zero's from my MIN function. My formula is returning the wrong cell address of where the min occurs.

    I do know the strategy of entering an array formula to exclude zeros from a simple MIN function, that is {=MIN(IF(A1:A10>0,A1:A10))} Entering in with Control+Shift+Enter, however, I cannot get that strategy to work in my case since I am not dealing with a simple MIN Function. My MIN function is nested with an Index/Match function...all nested within the Cell function.

    My formula is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    The purpose of that formula is to return the address of the minimum value that occurs within the named range of "LineItemTotalRange."

    The formula was working correctly when ZEROS were not entered in the named range "LineItemTotalRange." Now if I enter ZEROS in any of the cells that are within that range, the formula returns the cell address of the cells containing zeros.

    EDIT: "PercentWorksheet" is simply the name of the worksheet that I am applying this formula against.
    Last edited by clemsoncooz; 08-05-2014 at 04:21 PM.

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,377

    Re: Exclude Zero's from Min Function nested inside an index+Match... all within CELL funti

    Array enter:

    =CELL("address",INDEX(PercentWorksheet!E13:E500,MATCH(MIN(IF(LineItemTotalRange>0,LineItemTotalRange)),PercentWorksheet!E13:E500,0)))
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Forum Contributor
    Join Date
    12-20-2011
    Location
    United States, Ohio
    MS-Off Ver
    Excel 2010
    Posts
    293

    Re: Exclude Zero's from Min Function nested inside an index+Match... all within CELL funti

    Quote Originally Posted by Bernie Deitrick View Post
    Array enter:

    =CELL("address",INDEX(PercentWorksheet!E13:E500,MATCH(MIN(IF(LineItemTotalRange>0,LineItemTotalRange)),PercentWorksheet!E13:E500,0)))
    Thanks for the reply Bernie.

    if by "array enter" you mean to enter the formula and rather than hitting enter, I should hit CONTROL+SHIFT+ENTER - then if I do that, it returns a #VALUE! error.

  4. #4
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Exclude Zero's from Min Function nested inside an index+Match... all within CELL funti

    Are these two ranges the same thing?

    PercentWorksheet!E13:E500

    LineItemTotalRange

    ?
    Audere est facere

  5. #5
    Forum Contributor
    Join Date
    12-20-2011
    Location
    United States, Ohio
    MS-Off Ver
    Excel 2010
    Posts
    293

    Re: Exclude Zero's from Min Function nested inside an index+Match... all within CELL funti

    Quote Originally Posted by daddylonglegs View Post
    Are these two ranges the same thing?

    PercentWorksheet!E13:E500

    LineItemTotalRange

    ?
    They are the same column....but not the same range.

    As I understand INDEX/Match....
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    12-20-2011
    Location
    United States, Ohio
    MS-Off Ver
    Excel 2010
    Posts
    293

    Re: Exclude Zero's from Min Function nested inside an index+Match... all within CELL funti

    Additional Information:
    The formula worked before added zeros into my range.

    If I wanted the MAX value, the orignal formula works great if I change the "MIN" to "MAX." So I have to believe the syntx is correct.... ?

  7. #7
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Exclude Zero's from Min Function nested inside an index+Match... all within CELL funti

    Quote Originally Posted by Bernie Deitrick View Post
    Array enter:

    =CELL("address",INDEX(PercentWorksheet!E13:E500,MATCH(MIN(IF(LineItemTotalRange>0,LineItemTotalRange)),PercentWorksheet!E13:E500,0)))
    This works for me.

    If that returns #Value! for you, that indicates the formula has not been correctly array entered.
    You'll know it's correctly entered when the formula is enclosed in {brackets}

  8. #8
    Forum Contributor
    Join Date
    12-20-2011
    Location
    United States, Ohio
    MS-Off Ver
    Excel 2010
    Posts
    293

    Re: Exclude Zero's from Min Function nested inside an index+Match... all within CELL funti

    05-Aug 04.50.57 PM.jpg

    Screenshot attached.

    I have brackets around it. Still returns #Value! for me.

  9. #9
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Exclude Zero's from Min Function nested inside an index+Match... all within CELL funti

    Perhaps you could attach a sample XL file.
    Pictures don't really tell the whole story.

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

  10. #10
    Forum Contributor
    Join Date
    12-20-2011
    Location
    United States, Ohio
    MS-Off Ver
    Excel 2010
    Posts
    293

    Re: Exclude Zero's from Min Function nested inside an index+Match... all within CELL funti

    Sample File attached. Did the best I could to replicate all of the above.

    Sample.xlsx

  11. #11
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Exclude Zero's from Min Function nested inside an index+Match... all within CELL funti

    I think the problem is related to what I suggested - LineItemTotalRange is not a continuous range, so that won't work in this sort of formula, why not just use the same range all the way through?

    =CELL("address",INDEX(PercentWorksheet!E13:E500,MATCH(MIN(IF(PercentWorksheet!E13:E500>0,PercentWorksheet!E13:E500)),PercentWorksheet!E13:E500,0)))

    That gives me $E$30 as required

  12. #12
    Forum Contributor
    Join Date
    12-20-2011
    Location
    United States, Ohio
    MS-Off Ver
    Excel 2010
    Posts
    293

    Re: Exclude Zero's from Min Function nested inside an index+Match... all within CELL funti

    Quote Originally Posted by daddylonglegs View Post
    I think the problem is related to what I suggested - LineItemTotalRange is not a continuous range, so that won't work in this sort of formula, why not just use the same range all the way through?

    =CELL("address",INDEX(PercentWorksheet!E13:E500,MATCH(MIN(IF(PercentWorksheet!E13:E500>0,PercentWorksheet!E13:E500)),PercentWorksheet!E13:E500,0)))

    That gives me $E$30 as required
    I guess I was making it more difficult than I should. I know why I excluded thinking that...because that logic doesnt work on MAX because within that range are subtotals and its possible for a subtotal to be greater than a single line item thus not returning a true MAX value. So using the same range on max wouldn't work....but I do think in this instance it works on MIN since a subtotal would never be considered a min value. thanks for the help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Nested INDIRECT in INDEX/MATCH function
    By xtort81 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-24-2014, 03:08 PM
  2. I want to exclude blank cell in index match formula with multiple criteria
    By NOOR8225 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 09-16-2013, 06:51 AM
  3. Choosing Name Range inside Index / Match Function
    By gallow in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-05-2012, 07:08 AM
  4. Index/Match function inside an array
    By tittiot in forum Excel General
    Replies: 2
    Last Post: 01-20-2010, 09:48 PM
  5. Nested Function using MATCH and/or INDEX
    By ExcelJunkie in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-26-2006, 03:54 PM

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