Locate Cell containing Maximum (Excel 2002 SP2)

Topic

New Reply

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?

Reply | Quote

Replies

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.

Reply | Quote

I forgot about cell “address”, that is better than my approach and works for more columns

Steve

Reply | Quote

I forgot about cell “address”, that is better than my approach and works for more columns

Steve

Reply | Quote

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.

Reply | Quote

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),List,0)-1

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

Steve

Reply | Quote

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),List,0)-1

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

Steve

Reply | Quote