+ Reply to Thread
Results 1 to 6 of 6

Finding the Min of cells grouped in pairs

  1. #1
    Forum Contributor
    Join Date
    07-28-2009
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    99

    Finding the Min of cells grouped in pairs

    I attached the file and highlighted the cells in orange for what I'm trying to find.

    Basically I am trying to find the Min of a bunch of numbers that are paired together in 2s.

    For example: Cell 1: Price for Salt
    Cell 2: Price for Snow Removal

    Cell 4: Price for Salt
    Cell 5: Price for Snow Removal

    Cell 7: Price for Salt
    Cell 8: Price for Snow Removal

    I'm trying to find the min price of (Price for Salt + Price for Snow removal)

    However, some of my cells are blank thus my final result is "0' which I don't want.

    it sounds confusing but if you see in my attached document it will make a lot more sense.
    Attached Files Attached Files

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

    Re: Finding the Min of cells grouped in pairs

    You could use this "array formula" in C11

    =MIN(IF(C21:C213<>"",IF(B21:B213="Snow",C21:C213+C22:C214)))

    for 2nd smallest you can use SMALL like this

    =SMALL(IF(C21:C213<>"",IF(B21:B213="Snow",C21:C213+C22:C214)),2)

    These formula need to be confirmed with CTRL+SHIFT+ENTER

    See attached
    Attached Files Attached Files
    Audere est facere

  3. #3
    Forum Contributor
    Join Date
    07-28-2009
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    99

    Re: Finding the Min of cells grouped in pairs

    Thank you daddylonglegs,

    One last thing that I thought I would be able to do but is proving to be a bit difficult.

    If you look at the excel file again, I am trying to find the answers for cells C11 and C12

    C11 = Name of company for Best Pricing
    C12= Name of company for 2nd best pricing

    For example, cell C11 should show "Steve Brophey Snow Removal" and Cell C12 should show "Lloyd Contracting"

    I tried {=INDEX($A$21:$A$215,MATCH(C11,IF($B$21:$B$213="Snow",C21:C213+C22:C214)))}

    but that is just returning a blank.

  4. #4
    Forum Contributor
    Join Date
    07-28-2009
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    99

    Re: Finding the Min of cells grouped in pairs

    Actually I just figured out how to get C11 with {=INDEX($A$21:$A$215,MATCH(C11,IF(B21:B215<>"RFI",C21:C215+C22:C216,""),0))}

    but I'm still stumped on C12

    any help?

  5. #5
    Forum Contributor
    Join Date
    07-28-2009
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    99

    Re: Finding the Min of cells grouped in pairs

    nvm i figured it out =)

  6. #6
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Finding the Min of cells grouped in pairs

    Hi Tommy,
    I read this answer and it made my head hurt. I remembered a video done by Mr. Excel. He called this Ctrl-Shift-Enter method of entering a formula CSE. He named them Super Formulas in his video and said Microsoft calls them Array Formulas.

    I broke your problem down into two steps so I could do my first Array Formula. After many error messages I found some websites that explained it better. Click Here for the one that solved the problem and Here for the one that made some sense.

    Find the attached with your answer broken down in two steps. It might make more sense. I'm still a little confused and need more study but it seems to work.
    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)

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