Function help: Reference cell based on another cell's value?

[Troo]

I have a list of data, the top row being months and the bottom row bein
average temperatures. I need a function that will find the highes
average temperature, and then return the month at the top of the list.

The worst part is, I had a function working but when I transferred th
spreadsheet to my laptop the only thing that was saved was the value.
would appreciate any help

 

[Claus Busch]

Hi,

Am Thu, 8 Nov 2012 19:47:35 +0000 schrieb Troo:

I have a list of data, the top row being months and the bottom row being
average temperatures. I need a function that will find the highest
average temperature, and then return the month at the top of the list.

do you know, which row is the bottom row? If not, try:
=INDEX(1:1,MATCH(MAX(OFFSET($A$1,COUNTA(A:A)-1,,1,12)),OFFSET($A$1,COUNTA(A:A)-1,,1,12),0))


Regards
Claus Busch

 

[joeu2004]

I have a list of data, the top row being months and the
bottom row being average temperatures. I need a function
that will find the highest average temperature, and then
return the month at the top of the list.

=INDEX(A2:Z2,1,MATCH(MAX(A100:Z100),A100:Z100,0))

where A2:Z2 represents "top row" and A100:Z100 represents "bottom row".
Change the ranges appropriately.

 

[joeu2004]

do you know, which row is the bottom row? If not, try:
=INDEX(1:1,MATCH(MAX(OFFSET($A$1,COUNTA(A:A)-1,,1,12)),
OFFSET($A$1,COUNTA(A:A)-1,,1,12),0))

Although this is easy to read and understand, there are several aspects
about it that are undesirable, IMHO.

1. COUNTA(A:A) must look at all rows. In Excel 2007, that is 1+ million
comparisons for each COUNTA(A:A) -- 2+ million in total for this formula.

2. OFFSET is a volatile function. Consequently, this formula and any
dependent formulas are recalculated every time __any__ cell in __any__
worksheet is edited, often resulting in noticable performance degradation
(delays). This compounds the problem noted in #1.

Since we almost never expect to have 1+ million rows of data -- probably not
even 65,000+ in Excel 2003 -- it would be better to choose a reasonable, but
limited range for COUNTA; for example, COUNTA(A1:A1000).

Also, we can replace the volatile function OFFSET with an INDEX:INDEX
formula. Claus's formula would become:

=INDEX(1:1,1,MATCH(MAX(INDEX(A:A,COUNTA(A1:A1000)):INDEX(L:L,COUNTA(A1:A1000))),
INDEX(A:A,COUNTA(A1:A1000)):INDEX(L:L,COUNTA(A1:A1000)),0))

Also note the correction: INDEX(1:1,1,MATCH...) instead of
INDEX(1:1,MATCH...).

Needless to say, it would be better to replace the COUNTA uses above with
references to a cell whose formula is =COUNT(A1:A10000).

PS: It is okay to use INDEX(A:A,...) because Excel does not actually
process the entire column. However, arguably INDEX(A1:A1000,...) would be
better since it avoids recalculation if there is unrelated data and formulas
in column A below the table.