Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Named ranges are causing a #REF error | Excel Discussion (Misc queries) | |||
Query causing #ref error in spreadsheet | Excel Discussion (Misc queries) | |||
cells without values causing error message | Excel Discussion (Misc queries) | |||
VLOOKUP Formula causing an error | Excel Discussion (Misc queries) | |||
Formula Causing a Save Error Message | Excel Worksheet Functions |