Working changing Excel cell adresses

February 22, 2011 at 07:12:43
Specs: Windows XP
 Having a list of data, say A1 to A10 In cell A11 I put the max value from A1 to A10 (located in this example at A6 but may vary)Now I want to work with the cell 1 below the max value from the list ( in example A7) Thanks

See More: Working changing Excel cell adresses

#1
February 22, 2011 at 10:17:21
 This formula will return the value in the cell that is one row below the maximum value in the specified range.It should not be used in A11 because it will fail if A10 contains the maximum value.=OFFSET(INDEX(\$A\$1:\$A\$10,MATCH(MAX(\$A\$1:\$A\$10),\$A\$1:\$A\$10,0)),1,0)MAX will find the maximum valueMATCH will return the position within the range where the maximum value was found. Note: This is not the Excel Row number, but the position within the array.INDEX will return the Address of the cell at that locationOFFSET will return the value in the cell one Row below that Address.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions