using offset with array formulas
 

I have a formula that calculates a list of maximum values from another
sheet. In addition, I want to get the list of labels associated with
each of those items. The formula I am using is:

{=LARGE(IF(IF(ISNUMBER(Heat!G$2:G$1654),Heat!G$2:G $1654)<MIN(MIN(B$32:B$39),1100),IF(Heat!$C$2:$C$16 54="",Heat!G$2:G$1654)),
ROW(B1))}

and what I want to do is basically just add a nested OFFSET(...,0,-7)
to that formula, where the "..." is the above formula in the
neighboring column. That way I will have the label for each item.
However this doesn't seem to work and excel gives me an error message.
Is it because the formula is an array? How can I get this to work then?


--
QuantumPion
------------------------------------------------------------------------
QuantumPion's Profile: http://www.excelforum.com/member.php...o&userid=23991
View this thread: http://www.excelforum.com/showthread...hreadid=377097

Hi!

If your large number is in column G and you want to offset that by 7 columns
to the left then your labels must be in column A:

Array entered:

=INDEX(Heat!A$2:A$1654,MATCH(LARGE(IF(ISNUMBER(Hea t!G$2:G$1654),IF(Heat!G$2:G$1654<MIN(B$32:B$39,110 0),IF(Heat!$C$2:$C$1654="",Heat!G$2:G$1654))),ROW( 1:1)),Heat!G$2:G$1654,0))

Biff

"QuantumPion"
wrote in message
...

I have a formula that calculates a list of maximum values from another
sheet. In addition, I want to get the list of labels associated with
each of those items. The formula I am using is:

{=LARGE(IF(IF(ISNUMBER(Heat!G$2:G$1654),Heat!G$2:G $1654)<MIN(MIN(B$32:B$39),1100),IF(Heat!$C$2:$C$16 54="",Heat!G$2:G$1654)),
ROW(B1))}

and what I want to do is basically just add a nested OFFSET(...,0,-7)
to that formula, where the "..." is the above formula in the
neighboring column. That way I will have the label for each item.
However this doesn't seem to work and excel gives me an error message.
Is it because the formula is an array? How can I get this to work then?


--
QuantumPion
------------------------------------------------------------------------
QuantumPion's Profile:
http://www.excelforum.com/member.php...o&userid=23991
View this thread: http://www.excelforum.com/showthread...hreadid=377097