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)

<How can I determine at what cell the maximum value (a6) is located

How can I put this adress in a formula so I can change that adress to A7 (or B6) by adding somthing to the cell adress?/b>

Thanks

This formula will return the value in the cell that is one row below the maximum value in the specified range. It should

notbe 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 value

MATCH 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 location

OFFSET 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.

Ask Your Question

Weekly Poll

Did you buy anything on Amazon's Prime Day?

Discuss in The Lounge

Poll History