Identify where max values are found

O

owen080808

Hi all

Does anyone know if there's a way for excel to tell me which cell has
returned the max from a range, for example if i ask excel to find a max
"=max(B10:B34)" then it will display the result 0.15 for example. Is
there a way to make excel tell me which cell it found this data in, for
example if the max first appears in B22 i would like it to display this
in a new column

any help greatly appreciated

Thanks in advance
Owen
 
M

Max

owen080808 said:
Does anyone know if there's a way for excel to tell me which cell has
returned the max from a range, for example if i ask excel to find a max
"=max(B10:B34)" then it will display the result 0.15 for example. Is
there a way to make excel tell me which cell it found this data in, for
example if the max first appears in B22 i would like it to display this
in a new column
Click to expand...

One way ..
Put in say C1: ="B"&MATCH(MAX(B10:B34),B10:B34,0)+9
 
K

Ken Johnson

Hi Owen,
this worked for me...

=ADDRESS(ROW(B10)-1+MATCH(MAX(B10:B34),B10:B34,0),COLUMN(B10),4)

Ken Johnson
 
O

owen080808

thats great!

thanks a lot guys, im still battling with it a little due to the way
ive decided to set it out but you have both helped to put me on the
right track.

Thanks again guys

Owen:)
 
Top