Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Function help: Reference cell based on another cell's value?
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.
The worst part is, I had a function working but when I transferred the spreadsheet to my laptop the only thing that was saved was the value. I would appreciate any help! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Function help: Reference cell based on another cell's value?
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 -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Function help: Reference cell based on another cell's value?
"Troo" wrote:
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Function help: Reference cell based on another cell's value?
"Claus Busch" wrote:
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:A1 000)),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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to replace cell value based on other cell's value using macro | Excel Programming | |||
Resolve a text cell reference to the cell's value | Excel Discussion (Misc queries) | |||
increment cell value based on another cell's value | Excel Programming | |||
Select a cell based on an other cell's value | Excel Worksheet Functions | |||
How do I populate a cell with another cell's value based on condit | Excel Worksheet Functions |