CODEWITHSUNDEEP
excelexcel-formula
tsrplatelayer
tsrplatelayer

Reputation: 111

Get a cell relative to another from a named range

Using MS Excel 2007. I use a named range SysData.

=MAX(SysData)

returns the max value.

How can I return the value in the cell to the right and left of this maximum?

I tried getting the reference for the cell containing the max value as a starting point using:

=CELL("address",INDEX(SysData,MATCH(MAX(SysData),SysData,0)))

but this returns N/A.

Here is a sample of my spreadsheet:

spreadsheet image

SysData is B3:B5, B6 has the formula max(SysData)

As you can see - it didnt work I also tried the alternative formula

=ADDRESS(ROW(SysData)+INDEX(SysData,MAX(SysData))-1,COLUMN(SysData))

which also gives #REF!

Upvotes: 1

Views: 125

Answers (2)

Robin Mackenzie
Robin Mackenzie

Reputation: 19289

If you data is in a column you can use this:

=ADDRESS(ROW(SysData)+INDEX(SysData,MAX(SysData))-1,COLUMN(SysData))

And, if your data is an a row you can use this:

=ADDRESS(ROW(SysData),COLUMN(SysData)+INDEX(SysData,MAX(SysData))-1)

Once you have the cell you can then use OFFSET and INDIRECT to get a cell relative to the one that contains the MAX. E.g. where the data is in a row:

=OFFSET(INDIRECT(ADDRESS(ROW(SysData),COLUMN(SysData)+INDEX(SysData,MAX(SysData))-1)),0,-1)

enter image description here

Edit

If you data is in a single 2d range the formula is a bit different:

To identify the cell of the maximum number:

=ADDRESS(
  SUMPRODUCT(--(SysData=MAX(SysData))*ROW(SysData)),
  SUMPRODUCT(--(SysData=MAX(SysData))*COLUMN(SysData))
)

And then, to get some offset, you can just do the same process with INDIRECT and OFFSET:

=OFFSET(
  INDIRECT(
    ADDRESS(
      SUMPRODUCT(--(SysData=MAX(SysData))*ROW(SysData)),
      SUMPRODUCT(--(SysData=MAX(SysData))*COLUMN(SysData))
    )
  ),
 0,-1)

The two SUMPRODUCT formulas are basically looking at the array of the data e.g.

{1,2,3; 4,5,6; 7,8,9; 0,10,0}

And saying converting it to an array of TRUE and FALSE based on whether there is a match to the MAX e.g.:

{FALSE,FALSE,FALSE; FALSE,FALSE,FALSE; FALSE,FALSE,FALSE; FALSE,TRUE,FALSE}

Then the double unary operator converts to 0 and 1 e.g.

{0,0,0; 0,0,0; 0,0,0; 0,1,0}

Because ROW(SysData) or COLUMN(SysData) are themselves arrays of the absolute row and column references of the named range then multiplying those arrays by the 0/1 array returns the location of the MAX value.

In my example below the SysData array is B2:D5 and the ROW array is:

{2,3,4,5}

And the column array is:

{2,3,4}

Screenshot:

enter image description here

Upvotes: 0

Solar Mike
Solar Mike

Reputation: 8375

Will this work:

INDEX(sysdata,,MATCH(MAX(sysdata),sysdata,FALSE)-1)

the match returns the column number and the 1 is added or removed to get the value of the cell either side.

In response to the comment it does not work, the image shows the sheet and the range of sys data is D2:L2.

enter image description here

Upvotes: 0

Related Questions