Marking a cell adjacent to range to signify maximum based on criteria

  • I've searched through the "maximum based on criteria/conditions" threads and they have helped in my understanding, but I am missing a key part to help solve my excel issue.


    What I have is a list of names in one column, a number in the next column, and then a blank column next to that (three in total). An example below:


    Smith, Joe, XK ---------------- 12--------------BLANK
    Parker, Ed, XZ -----------------15--------------BLANK
    Yates, Doug, TY---------------- 2-------------BLANK
    Chandler, David, XK -------------1------------BLANK
    Hughes, Luke, TY ---------------3------------BLANK


    What I would like to have is a function that would have the largest number corresponding to a name with "XK" be marked with an "x" in the blank column. To help clarify, I want to mark in the blank column the highest valued people corresponding to their specific abbreviation next to their name.


    Something to the effect of:
    Smith, Joe, XK ---------------- 12--------------X
    Parker, Ed, XZ -----------------15--------------BLANK
    Yates, Doug, TY---------------- 2-------------BLANK
    Chandler, David, XK -------------1------------BLANK
    Hughes, Luke, TY ---------------3------------X


    Any help would be greatly appreciated. Please let me know if I need to clarify my issue.

  • Re: Marking a cell adjacent to range to signify maximum based on criteria


    Array formula:
    =IF(MAX(IF(RIGHT($A$2:$A$5,2)=RIGHT(A2,2),$B$2:$B$5,0))=B2,"X","")


    (Place in C2, confirm with Ctrl+Shift+Enter, then drag down)

  • Re: Marking a cell adjacent to range to signify maximum based on criteria


    This is an array formula, you need to enter it and then Pres Ctrl + Shjft+ Enter all at once


    I assumed the last two digits were the determining criteria


    =IF(MAX((RIGHT($A$2:$A$10,2)=RIGHT(A2,2))*($B$2:$B$10))=B2,"x","")


    see if it does what you want - in tersm of a draw for the max amount it will mark both.

    There are three types of people in this world.
    Those who can count and those who can't.

  • Re: Marking a cell adjacent to range to signify maximum based on criteria


    Thank you for the help. I finally understand how that works with the array!

  • Re: Marking a cell adjacent to range to signify maximum based on criteria


    To add another level of complexity to the formula (which I have expanded greatly), how would I have it ignore a text value and continue to function properly? See example below:


    Smith, Joe, XK ---------------- 12--------------BLANK
    Parker, Ed, XZ -----------------15--------------BLANK
    Yates, Doug, TY----------------TEXT-------------BLANK
    Chandler, David, XK -------------1------------BLANK
    Hughes, Luke, TY ---------------3------------BLANK


    Where the text in the second or B column would be ignored, not factored into marking an "x" for the maximum, and be kept blank in column 3 or C.


    Smith, Joe, XK ---------------- 12--------------X
    Parker, Ed, XZ -----------------15--------------BLANK
    Yates, Doug, TY----------------TEXT-------------BLANK
    Chandler, David, XK -------------1------------BLANK
    Hughes, Luke, TY ---------------3------------X

  • Re: Marking a cell adjacent to range to signify maximum based on criteria


    something like - array entered


    =IF(ISTEXT(B2),"",IF(MAX((RIGHT($A$2:$A$10,2)=RIGHT(A2,2))*($B$2:$B$10))=B2,"x",""))

    There are three types of people in this world.
    Those who can count and those who can't.

  • Re: Marking a cell adjacent to range to signify maximum based on criteria


    I actually tried that beforehand. The issue is that the two columns are multiplied with the text still in the second part of the equation (in bold). The issue lies with mutlipying two columns with text present - it can initially be ignored.



    =IF(ISTEXT(B2),"",IF(MAX((RIGHT($A$2:$A$10,2)=RIGHT(A2,2))*($B$2:$B$10))=B2,"x",""))

  • Re: Marking a cell adjacent to range to signify maximum based on criteria


    Sorry, I see the problem.


    The only solution I can suggest is to insert a column with a formula that only have values in it and no text an use that new column instead.

    There are three types of people in this world.
    Those who can count and those who can't.

  • Re: Marking a cell adjacent to range to signify maximum based on criteria


    my formula already did this. Enter in C2 and drag down.


    =IF(MAX(IF(RIGHT($A$2:$A$10,2)=RIGHT(A2,2),$B$2:$B$10,0))=B2,"X","")


    (array formula)

  • Re: Marking a cell adjacent to range to signify maximum based on criteria


    That's right, I didn't notice the extra IF statement. That works, thanks for the heads up.

  • Re: Marking a cell adjacent to range to signify maximum based on criteria


    Not related to the function I originally wanted, but related to the example provided - how would I go about calculating the total number of TY/XK/etc. that are listed in the format posted originally?


    IE
    Smith, Joe, XK ---------------- 12--------------BLANK
    Parker, Ed, XZ -----------------15--------------BLANK
    Yates, Doug, TY---------------- 2-------------BLANK
    Chandler, David, XK -------------1------------BLANK
    Hughes, Luke, TY ---------------3------------BLANK


    XK: 2
    TY: 2
    XZ: 1