dynamic list length to find top 8 from last 20

    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

    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))

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login