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$1654="",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?

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(Heat!G$2:G$1654),IF(Heat!G$2:G$1654<MIN(B$32:B$39,1100),IF(Heat!$C$2:$C$1654="",Heat!G$2:G$1654))),ROW(1:1)),Heat!G$2:G$1654,0))

Biff

"QuantumPion" <[email protected]>
wrote in message
news:[email protected]...
>
> 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$1654="",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(Heat!G$2:G$1654),IF(Heat!G$2:G$1654<MIN(B$32:B$39,1100),IF(Heat!$C$2:$C$1654="",Heat!G$2:G$1654))),ROW(1:1)),Heat!G$2:G$1654,0))

Biff

"QuantumPion" <[email protected]>
wrote in message
news:[email protected]...
>
> 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$1654="",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(Heat!G$2:G$1654),IF(Heat!G$2:G$1654<MIN(B$32:B$39,1100),IF(Heat!$C$2:$C$1654="",Heat!G$2:G$1654))),ROW(1:1)),Heat!G$2:G$1654,0))

Biff

"QuantumPion" <[email protected]>
wrote in message
news:[email protected]...
>
> 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$1654="",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(Heat!G$2:G$1654),IF(Heat!G$2:G$1654<MIN(B$32:B$39,1100),IF(Heat!$C$2:$C$1654="",Heat!G$2:G$1654))),ROW(1:1)),Heat!G$2:G$1654,0))

Biff

"QuantumPion" <[email protected]>
wrote in message
news:[email protected]...
>
> 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$1654="",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(Heat!G$2:G$1654),IF(Heat!G$2:G$1654<MIN(B$32:B$39,1100),IF(Heat!$C$2:$C$1654="",Heat!G$2:G$1654))),ROW(1:1)),Heat!G$2:G$1654,0))

Biff

"QuantumPion" <[email protected]>
wrote in message
news:[email protected]...
>
> 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$1654="",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(Heat!G$2:G$1654),IF(Heat!G$2:G$1654<MIN(B$32:B$39,1100),IF(Heat!$C$2:$C$1654="",Heat!G$2:G$1654))),ROW(1:1)),Heat!G$2:G$1654,0))

Biff

"QuantumPion" <[email protected]>
wrote in message
news:[email protected]...
>
> 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$1654="",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(Heat!G$2:G$1654),IF(Heat!G$2:G$1654<MIN(B$32:B$39,1100),IF(Heat!$C$2:$C$1654="",Heat!G$2:G$1654))),ROW(1:1)),Heat!G$2:G$1654,0))

Biff

"QuantumPion" <[email protected]>
wrote in message
news:[email protected]...
>
> 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$1654="",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(Heat!G$2:G$1654),IF(Heat!G$2:G$1654<MIN(B$32:B$39,1100),IF(Heat!$C$2:$C$1654="",Heat!G$2:G$1654))),ROW(1:1)),Heat!G$2:G$1654,0))

Biff

"QuantumPion" <[email protected]>
wrote in message
news:[email protected]...
>
> 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$1654="",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(Heat!G$2:G$1654),IF(Heat!G$2:G$1654<MIN(B$32:B$39,1100),IF(Heat!$C$2:$C$1654="",Heat!G$2:G$1654))),ROW(1:1)),Heat!G$2:G$1654,0))

Biff

"QuantumPion" <[email protected]>
wrote in message
news:[email protected]...
>
> 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$1654="",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
>