Results 1 to 4 of 4
  1. #1
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Locate Cell containing Maximum (Excel 2002 SP2)

    Good Morning All,

    While I am snowed in, I thought that I would tackle an excel item that has been hanging around. I have a list of (say) 10 numbers. I want to locate the cell reference to the maximum (and minimum) from this list. Why? I am trying to calculate a weighted average (excluding max and min) and I need to exclude the weights (or set the weights to zero) for the max / min.

    I was thinking about RANK, CELL, ROW and COLUMN in some ugly looking formula. Anyone got a bright idea?
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

  2. #2
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Locate Cell containing Maximum (Excel 2002 SP2)

    There are probably other (any maybe easier) ways of doing this.

    If your list is in the range B2:B11 you could use:
    =CELL("address",INDEX(B2:B11,MATCH(MAX(B2:B11),B2: B11,0),1))
    =CELL("address",INDEX(B2:B11,MATCH(MIN(B2:B11),B2: B11,0),1))

    If there are more than 1 value the same for either MAX or MIN, the above shows the cell reference for the first one.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Locate Cell containing Maximum (Excel 2002 SP2)

    Several approaches
    =(Sum(list)-max(list)-min(list))/(count(list)-1)
    will give the average without them

    For weighted
    =SUMPRODUCT(List,Weights)/SUM(weights)
    will give the weighted average with all points

    or use:
    =SUMPRODUCT(List,Weights2)/SUM(weights2)

    where weights2 is a column from
    =if(or(a1=min(list), a1=max(list)),0,B1)
    Assuming list is in Col A and weights are in B

    This could eliminate more than 2 values if you have dup Min or dup max.

    You can find the "index" of the 1st min or max using match"
    =match(min(list),list,0)
    or
    =match(max(list),list,0)
    So if the List is in the first 26 columns the address of the max is:
    =CHAR(COLUMN(List)+64)&ROW(List)+MATCH(MAX(List),L ist,0)-1

    And the address of Min is then:
    =CHAR(COLUMN(List)+64)&ROW(List)+MATCH(MIN(List),L ist,0)-1

    Steve

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Locate Cell containing Maximum (Excel 2002 SP2)

    I forgot about cell "address", that is better than my approach and works for more columns <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •