+ Reply to Thread
Results 1 to 4 of 4

Locate cell position for max value in column

Hybrid View

  1. #1
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Locate cell position for max value in column

    I like your idea to list each relative position of the max value.

    I'd prefer to avoid the array formulas, though, and go with something like this:

    Using your sample structure:
    D2: =MAX(A2:A51)
    D3: =COUNTIF(A2:A51,D2)
    
    D4: Result
    D5: =IF(ROWS($5:5)<=$D$3,MATCH($D$2,$A$2:$A$51,0),"")
    
    D6: =IF(ROWS($5:6)<=$D$3,MATCH($D$2,INDEX($A$2:$A$51,D5+1):$A$51,0)+D5,"")
    
    Copy D6 down as far as needed.
    
    Note: Those formulas return the RELATIVE POSTITION of the 
    max value(s) in the referenced range.  If the actual row number is required:
    D5: =IF(ROWS($5:5)<=$D$3,MATCH($D$2,$A$2:$A$51,0)+ROW($A$1),"")
    
    EDITED TO CORRECT THE BELOW FORMULA
    D6: =IF(ROWS($5:6)<=$D$3,MATCH($D$2,INDEX($A:$A,D5+1):$A$51,0)+D5,"")
    Last edited by Ron Coderre; 12-08-2007 at 07:09 PM.
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

+ 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