I have a excel sheet where data is continuously being added to the top of a column, after shifting the remaining rows/ cells down.
I want to find the largest value among the nth - 10 rows and result at the bottom of that column.
At 12:13 pm, the Range A would contain data in A2 to A31, but I want to find the maximum value among rows A2:A20 ignoring A21 to A31;
At 12:14 pm, the Range A would contain data in A2 to A32, but I want to find the maximum value among rows A2:A21 ignoring A22 to A32; and so on....
Please help with a formula that takes care of this dynamically expanding range.
1. Would appreciate a non-macro soluiton since they are volatile and remove the undo option;
2. Would appriciate a non-array formula since they can take a long time to process.
Would be OK, if I have to use multiple calculations in multiple cells to arrive at the result.
Sample file attached