More on MAX

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Old Red One - 17 Oct 2006 18:42 GMT

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?

Reply to this Message

Niek Otten - 17 Oct 2006 19:01 GMT

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

Signature

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
[quoted text clipped - 3 lines]
| manual fashion? If we know that the MAX number is entry number 3102 in a
| coluumn of 5000 entries, how useful is that knowledge?

Reply to this Message

Old Red One - 17 Oct 2006 23:40 GMT

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.

> =ADDRESS(MATCH(MAX(A1:A500),A1:A500,0),1)
>
[quoted text clipped - 9 lines]
> a
> | coluumn of 5000 entries, how useful is that knowledge?

Reply to this Message

Old Red One - 17 Oct 2006 23:45 GMT

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.

> 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
[quoted text clipped - 3 lines]
> 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?

Reply to this Message

David F Cox - 18 Oct 2006 00:23 GMT

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

> Hello again Nick. There are times when I feel stupid, and there are times
> when I feel really, really stupid! .....

Reply to this Message

Rate this thread: