Identify where max values are found

[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

[Max]

"owen080808" wrote:
> 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

One way ..
Put in say C1: ="B"&MATCH(MAX(B10:B34),B10:B34,0)+9
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

[Ken Johnson]

Hi Owen,
this worked for me...

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

Ken Johnson

[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

[Ken Johnson]

You're welcome Owen.
Thanks for the feedback.
Ken Johnson