Find Min or Max [for last n rows] value from single column where row data keeps expanding
Friends / Seniors,
My current data starts from Cell = C12. The last data is in Cell C31 , and this data would keep incerasing from C31 to C32 to C33 and so on. Starting from Bottom Last Cell in Column C having value i.e. C31, I want to [in Cell C7] find previous n th data (controller in Cell A7), currently for last 10 rows from bottom C31).
Presently, C7 is =OFFSET($C$31,-A7,0).
Starting from Last cell having values, to move upward steps based in A7 value. So, defined Range is C21:C30.
Assuming max data size could be upto C3000, I tried to get address of last cell in Column C using array formula in Cell C9 =ADDRESS(MAX((C12:C3000<>"")*(ROW(C12:C3000))),COLUMN(C12:C3000))
Now, how do i get MIN or MAX value from these range C21:C30. Min value result 17.64[C23], and Max 19.90[C27]. Issue is to how to make use of MIN/MAX function in given situation, and how do i get Cell reference to offset from the last cell.
How to get the result in C7?
Sample file attached.
Bookmarks