I am trying to find the top 4 values from a variable list of values in a column - the column could contain any number of values up to 100 or more but the top 4 should only be taken from the last 15 in the list - the problem is that the list is constantly having new values added so a range that changes all the time so needs always to look at the latest 15 and the top 4 within that. Once the last 15 has been established I assume can use the 'large' function to obtain the 4 but not sure how to create the dynamic list and take the last 15 at any time. Hope this explains things
dynamic list length to find top 8 from last 20
-
-
-
Are the latest 15 anywhere in the list or are they the last 15 items in a column?
-
They will always be the last 15 but the position will change as new values are added to the end
-
Extract top 4 from the last 15 in the list (new values will be added to the end of the list)
Assume data in A2:A?
In C2, enter formula and copied down 3 lines :
=LARGE(OFFSET(A$1,MATCH(9^9,A:A)-1,0,-15),ROW(A1))