More on MAX

[Old Red One]

In a column of data covering several years - e.g. the daily yield of the 30
year bond - finding the maximum yield figure in hundreds of entries would
be laborious and subject to error; is there a way to get the cell
identification using MAX? That is, the precise cell ID as in C3456?
Knowing the entry number is a beginning, but can it be used in other than a
manual fashion? If we know that the MAX number is entry number 3102 in a
coluumn of 5000 entries, how useful is that knowledge?

 

[Niek Otten]

=ADDRESS(MATCH(MAX(A1:A500),A1:A500,0),1)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| In a column of data covering several years - e.g. the daily yield of the 30
| year bond - finding the maximum yield figure in hundreds of entries would
| be laborious and subject to error; is there a way to get the cell
| identification using MAX? That is, the precise cell ID as in C3456?
| Knowing the entry number is a beginning, but can it be used in other than a
| manual fashion? If we know that the MAX number is entry number 3102 in a
| coluumn of 5000 entries, how useful is that knowledge?
|
|

 

[Old Red One]

Thank you Nick. BUT! I have a column of 718 dollar values (portfolio
valuations over two years), and your formula returns $A$142. So I can
laboriously count down 142 cells and find the cell in which the MAX number
appears. But isn't there an eassier, faster way? Please comment. Thanks.

 

[Old Red One]

Hello again Nick. There are times when I feel stupid, and there are times
when I feel really, really stupid! This is one of those times. Obviously
142 plus the beginning cell number yields the answer I want. My apologies.

 

[David F Cox]

I console myself that feeling stupid is one step above not knowing I was
stupid. ->)