What's causing error message?
 

Hello all,

The formula: =ADDRESS(2,MATCH(MAX(B2:AY2),A2:AY2,0),4) returns the cell
address (G2) of the largest number in B2:AY2 correctly.

The formula: =OFFSET(G2,-1,0) returns the name of the team in G1 that has
that highest total.

So why, when I replace the "G2" in the OFFSET formula with the ADDRESS
formula:
=OFFSET(ADDRESS(2,MATCH(MAX(B2:AY2),A2:AY2,0),4),-1,0) do I get an error
message?

Thanks for any ideas.

--
Jim T

What's causing error message?
 

The error is cause because OFFSET can't take the textstring derived from a
formula and turn it into a
valid cell reference. You would need to use INDIRECT

=OFFSET(INDIRECT(ADDRESS(2,MATCH(MAX(B2:AY2),A2:AY 2,0),4)),-1,0)


however this is not a good way to solve this, much better to use

=INDEX(A1:AY1,MATCH(MAX(B2:AY2),A2:AY2,0))

a much more efficient formula and it is not volatile either

--


Regards,


Peo Sjoblom

"Jim Tibbetts" wrote in message
...
Hello all,

The formula: =ADDRESS(2,MATCH(MAX(B2:AY2),A2:AY2,0),4) returns the cell
address (G2) of the largest number in B2:AY2 correctly.

The formula: =OFFSET(G2,-1,0) returns the name of the team in G1 that has
that highest total.

So why, when I replace the "G2" in the OFFSET formula with the ADDRESS
formula:
=OFFSET(ADDRESS(2,MATCH(MAX(B2:AY2),A2:AY2,0),4),-1,0) do I get an error
message?

Thanks for any ideas.

--
Jim T

What's causing error message?
 

Doh! cause should be because

--


Regards,


Peo Sjoblom

"Peo Sjoblom" wrote in message
...
The error is cause because OFFSET can't take the textstring derived from a
formula and turn it into a
valid cell reference. You would need to use INDIRECT

=OFFSET(INDIRECT(ADDRESS(2,MATCH(MAX(B2:AY2),A2:AY 2,0),4)),-1,0)


however this is not a good way to solve this, much better to use

=INDEX(A1:AY1,MATCH(MAX(B2:AY2),A2:AY2,0))

a much more efficient formula and it is not volatile either

--


Regards,


Peo Sjoblom

"Jim Tibbetts" wrote in message
...
Hello all,

The formula: =ADDRESS(2,MATCH(MAX(B2:AY2),A2:AY2,0),4) returns the cell
address (G2) of the largest number in B2:AY2 correctly.

The formula: =OFFSET(G2,-1,0) returns the name of the team in G1 that has
that highest total.

So why, when I replace the "G2" in the OFFSET formula with the ADDRESS
formula:
=OFFSET(ADDRESS(2,MATCH(MAX(B2:AY2),A2:AY2,0),4),-1,0) do I get an error
message?

Thanks for any ideas.

--
Jim T

What's causing error message?
 

Peo - Many thanks for your help. Both formulas return the result I need.
--
Jim T


"Peo Sjoblom" wrote:

The error is cause because OFFSET can't take the textstring derived from a
formula and turn it into a
valid cell reference. You would need to use INDIRECT

=OFFSET(INDIRECT(ADDRESS(2,MATCH(MAX(B2:AY2),A2:AY 2,0),4)),-1,0)


however this is not a good way to solve this, much better to use

=INDEX(A1:AY1,MATCH(MAX(B2:AY2),A2:AY2,0))

a much more efficient formula and it is not volatile either

--


Regards,


Peo Sjoblom

"Jim Tibbetts" wrote in message
...
Hello all,

The formula: =ADDRESS(2,MATCH(MAX(B2:AY2),A2:AY2,0),4) returns the cell
address (G2) of the largest number in B2:AY2 correctly.

The formula: =OFFSET(G2,-1,0) returns the name of the team in G1 that has
that highest total.

So why, when I replace the "G2" in the OFFSET formula with the ADDRESS
formula:
=OFFSET(ADDRESS(2,MATCH(MAX(B2:AY2),A2:AY2,0),4),-1,0) do I get an error
message?

Thanks for any ideas.

--
Jim T

What's causing error message?
 

You are welcome, I notice that you probably have a typo, shouldn't this


MATCH(MAX(B2:AY2),A2:AY2,0)


be


MATCH(MAX(A2:AY2),A2:AY2,0))

?

--


Regards,


Peo Sjoblom

"Jim Tibbetts" wrote in message
...
Peo - Many thanks for your help. Both formulas return the result I need.
--
Jim T


"Peo Sjoblom" wrote:

The error is cause because OFFSET can't take the textstring derived from
a
formula and turn it into a
valid cell reference. You would need to use INDIRECT

=OFFSET(INDIRECT(ADDRESS(2,MATCH(MAX(B2:AY2),A2:AY 2,0),4)),-1,0)


however this is not a good way to solve this, much better to use

=INDEX(A1:AY1,MATCH(MAX(B2:AY2),A2:AY2,0))

a much more efficient formula and it is not volatile either

--


Regards,


Peo Sjoblom

"Jim Tibbetts" wrote in message
...
Hello all,

The formula: =ADDRESS(2,MATCH(MAX(B2:AY2),A2:AY2,0),4) returns the cell
address (G2) of the largest number in B2:AY2 correctly.

The formula: =OFFSET(G2,-1,0) returns the name of the team in G1 that
has
that highest total.

So why, when I replace the "G2" in the OFFSET formula with the ADDRESS
formula:
=OFFSET(ADDRESS(2,MATCH(MAX(B2:AY2),A2:AY2,0),4),-1,0) do I get an
error
message?

Thanks for any ideas.

--
Jim T