+ Reply to Thread
Results 1 to 7 of 7

Return Min Value With Criteria

  1. #1
    Registered User
    Join Date
    08-20-2014
    Location
    CLT
    MS-Off Ver
    2010
    Posts
    61

    Return Min Value With Criteria

    Hello,

    I am having trouble with a formula not returning a value, and I am not sure why. I am trying to return the minimum value where certain criteria is met.

    The following formula returns the correct value:

    {=IF(ISERROR(MATCH(C$13&$A14&$B$1,Sales[First Name]&Sales[EndState]&Sales[StartState],0)),"-","Good")}

    However, when I add the part of the formula to return the min value it is returning as #N/A. Any suggestions on when the following is hitting an error?

    {=IF(ISERROR(MATCH(C$13&$A14&$B$1,Sales[First Name]&Sales[EndState]&Sales[StartState],0)),"-",MIN(IF(Sales[First Name]=C$13,IF(Sales[EndState]=$A14,IF(Sales[StartState]=$B$1,Sales[Dollars])))))}

  2. #2
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: Return Min Value With Criteria

    Let's clip off the error-checking for now; see if this does it for you.

    Please Login or Register  to view this content.
    Click the [* Add Reputation] Button to thank people for help!
    Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
    Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.

  3. #3
    Registered User
    Join Date
    08-20-2014
    Location
    CLT
    MS-Off Ver
    2010
    Posts
    61

    Re: Return Min Value With Criteria

    Hensel, appreciate the reply. Doing the above still returns #N/A. I am stumped. Does this deal with the way the columns are formatted potentially?

  4. #4
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,390

    Re: Return Min Value With Criteria

    zmster2033,

    Can you attach a simple worksheet showing an example of what you start with, and what the "end result" should show?

    Difficult to see a problem with the formula without knowing what the cells are like that everything refers to?

    Ochimus

  5. #5
    Registered User
    Join Date
    08-20-2014
    Location
    CLT
    MS-Off Ver
    2010
    Posts
    61

    Re: Return Min Value With Criteria

    Attached is a sample file. So it seems when there is an error in the table the formula doesn't work correctly. It is able to see if there is a match on multiple criteria. Is there anyway around this? If you populate the #N/A record with a name the formula loads properly. The fields that are being used in the formula are lookup values from other columns I have deleted.
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: Return Min Value With Criteria

    Try: =IF(ISERROR(MATCH(C$3&$A4&$B$1,CurrentSales[Name]&CurrentSales[Destination2]&CurrentSales[Origin2],0)),"-",MIN(IFERROR(IF(CurrentSales[Name]=C$3,IF(CurrentSales[Destination2]=$A4,IF(CurrentSales[Origin2]=$B$1,CurrentSales[Rates]))),"")))

  7. #7
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: Return Min Value With Criteria

    Quote Originally Posted by zmster2033 View Post
    Hensel, appreciate the reply. Doing the above still returns #N/A. I am stumped. Does this deal with the way the columns are formatted potentially?
    Cell Sales!D6 of the example sheet has #N/A as the value. This is "infecting" every function that sees it with the same error code. That propagates through and is why all your functions are returning #N/A.

    Replacing that with a non-error value -- eg, zero -- will fix everything, as near as I can tell.

    While I was looking at it, I noticed that you can output this information through a Pivot Table, I think, rather than mucking about with array formulas.
    Attached Files Attached Files

+ 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. Replies: 1
    Last Post: 10-01-2014, 04:50 PM
  2. Index Match using two criteria to return on of criteria values
    By RogueLeader in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-17-2014, 09:50 AM
  3. Return dates that match a single criteria (a countif criteria)
    By nickmax1 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-07-2014, 12:43 PM
  4. Return Value With Criteria
    By Kumara_faith in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-05-2014, 10:41 AM
  5. Replies: 3
    Last Post: 12-13-2013, 06:23 AM
  6. Lookup two criteria and return a third criteria as result. Aaaargh!
    By dearthofjoy in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 10-08-2013, 12:13 PM
  7. [SOLVED] Excel 2007 : Return value if four criteria are met
    By CatherineCarey in forum Excel General
    Replies: 4
    Last Post: 04-26-2012, 10:16 AM

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