+ Reply to Thread
Results 1 to 3 of 3

How to evaluate a data range for max values and list all corresponding column headings

  1. #1
    Registered User
    Join Date
    05-23-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2007
    Posts
    1

    Question How to evaluate a data range for max values and list all corresponding column headings

    I have a worksheet that looks similar to the example table below. Multiple areas are being audited and scored. I have my sheet set up to evaluate each row for the maximum achieved rating (the "Top Rating" column) and the number of top ratings (the "# Top" column).
    What I am wanting to do is list any and all top rated depts in the "Top Depts" column - i.e., cell D2 would show "Eng.", and cell D3 would show "Accting, Sales, Shipping".
    Seems like a job, at least in part, for the IF function, but I don't know how to apply it over a range of cells and any help would be appreciated.

    Thanks,
    BradB76

    A B C D E F G H I
    1 Criteria Top Rating # Top Top Depts Accting Sales Eng. Mfg. Shipping
    2 Quality 5 1 3 4 5 4 2
    3 Safety 5 3 5 5 4 3 5

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,064

    Re: How to evaluate a data range for max values and list all corresponding column headings

    Use MATCH maybe with OFFSET function to locate the MAX value in a row
    Howevere be aware that if there is more than one MAX value in a row the MATCH will only find the first occurrence.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: How to evaluate a data range for max values and list all corresponding column headings

    It's clunky, but how's about this...

    =LEFT(IF(E3=$B3,E$1&", ","")&IF(F3=$B3,F$1&", ","")&IF(G3=$B3,G$1&", ","")&IF(H3=$B3,H$1&", ","")&IF(I3=$B3,I$1&", ",""),LEN(IF(E3=$B3,E$1&", ","")&IF(F3=$B3,F$1&", ","")&IF(G3=$B3,G$1&", ","")&IF(H3=$B3,H$1&", ","")&IF(I3=$B3,I$1&", ",""))-2)

    The main formula is =IF(E3=$B3,E$1&", ","")&IF(F3=$B3,F$1&", ","")&IF(G3=$B3,G$1&", ","")&IF(H3=$B3,H$1&", ","")&IF(I3=$B3,I$1&", ","")

    But then I used a LEFT function to trim the last ", "

+ 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