# Thread: Locate Cell containing Maximum (Excel 2002 SP2)

1. ## 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?

2. ## 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:

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. ## 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. ## 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
•