+ Reply to Thread
Results 1 to 8 of 8

Help finding lowest value using multiple criterias...little different than usual

  1. #1
    Forum Contributor
    Join Date
    08-24-2012
    Location
    Portugal
    MS-Off Ver
    Excel 2003
    Posts
    119

    Help finding lowest value using multiple criterias...little different than usual

    Hi folks

    I need some help in this example file that i created. What im trying to do is to search for the lowest number in column E (Cons.) using two criterias (i think). So what i was hoping to do is a formula that picks the number in K4 and rounds it to look like the ones in column C (Tir.) and then it should look for that number in column C that match, and in the same time using the number in L4 to match and than search for the lowest number in column E.
    Sorry but im getting some difficulties to show my problem in words (not english guy) :-(

    The result expected in this example should be the value in E14 (890) because is the lowest value that match the criteria of K4 (rounding to 310) in column C and L4 (18) in column D.
    Is this possible?

    Thanks for the attention
    Kind regards
    Attached Files Attached Files
    Last edited by FDibbins; 01-02-2014 at 09:03 PM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,938

    Re: Help finding lowest value using multiple criterias...little different than usual

    This ARRAY formula will find the lowest value, based onthe criteria in K4...
    =MIN(IF($B$3:$B$31=K4,$E$3:$E$31))
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

    BUT...I am unsure how you would round 311809 and get 312100??
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Contributor
    Join Date
    08-24-2012
    Location
    Portugal
    MS-Off Ver
    Excel 2003
    Posts
    119

    Re: Help finding lowest value using multiple criterias...little different than usual

    Thanks Ford for your time.
    I think i couldn't explain well. What i need is some formula that round the number in K4 like they are rounded in column C and after that it should look for the lowest value that match that number in K4 rounded, in this case after round is 310. Using this number 310 and the other one in L4 (18) than it should look in column C and D for an exact match of this two values giving me than the lowest value in column E. In this case it should be E14 because is the lowest value using 310 and 18 on the criteria. It should look for all values that match 310 and 18, in this case it should be C14 and D14,C19 and D19 to C22 and D22. What i want is the lowest value in column E that match in this selection.

    Sorry for the confusion

    Thanks
    Last edited by bazofio; 01-02-2014 at 08:23 PM.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,938

    Re: Help finding lowest value using multiple criterias...little different than usual

    OK I think I understand now, You explained well, I just didnt read well LOL. Try this ARRAY...
    =MIN(IF($C$3:$C$31=ROUNDDOWN(K4/1000,-1),$E$3:$E$31))

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

  5. #5
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Help finding lowest value using multiple criterias...little different than usual

    Try this version

    =MIN(IF(C3:C50=INT(K4/5/1000)*5,IF(D3:D50=L4,E3:E50)))

    confirmed with CTRL+SHIFT+ENTER
    Audere est facere

  6. #6
    Forum Contributor
    Join Date
    08-24-2012
    Location
    Portugal
    MS-Off Ver
    Excel 2003
    Posts
    119

    Re: Help finding lowest value using multiple criterias...little different than usual

    Quote Originally Posted by FDibbins View Post
    OK I think I understand now, You explained well, I just didnt read well LOL. Try this ARRAY...
    =MIN(IF($C$3:$C$31=ROUNDDOWN(K4/1000,-1),$E$3:$E$31))

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    This is working but in your formula there isn't the option of the L4, and its just looking for the rounded value of K4. But the one posted by daddylonglegs its working perfectly.
    Thanks Ford for your help ;-)

  7. #7
    Forum Contributor
    Join Date
    08-24-2012
    Location
    Portugal
    MS-Off Ver
    Excel 2003
    Posts
    119

    Re: Help finding lowest value using multiple criterias...little different than usual

    Quote Originally Posted by daddylonglegs View Post
    Try this version

    =MIN(IF(C3:C50=INT(K4/5/1000)*5,IF(D3:D50=L4,E3:E50)))

    confirmed with CTRL+SHIFT+ENTER
    Great, it works just fine ;-)
    Many thanks daddylonglegs.

    Just to say that people in this forum are fantastic.

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,938

    Re: [SOLVED] Help finding lowest value using multiple criterias...little different than us

    Happy to help and thanks for the feedback

+ 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] Finding the lowest value
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-23-2013, 03:30 PM
  2. Finding the lowest value based on multiple text criteria
    By meherenow9 in forum Excel General
    Replies: 14
    Last Post: 09-16-2011, 02:01 PM
  3. Finding lowest cost price with multiple criteria
    By jimbokeep in forum Excel General
    Replies: 8
    Last Post: 06-30-2010, 01:11 PM
  4. Q: Finding cells based on two criterias
    By Greffin in forum Excel General
    Replies: 11
    Last Post: 02-11-2009, 09:20 AM
  5. Finding unique values with Criterias
    By dolpphinv4 in forum Excel General
    Replies: 1
    Last Post: 04-13-2005, 10:06 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