Google-sheets – How to create a formula to look at the most recent past 20 scores in a column and averages the lowest 8 scores

formulasgoogle sheets

In Google Sheets I would like to create a formula to look at my most recent past 20 scores in a column and averages the lowest 8 scores.

In another post I found the following formula that will average my 8 most recent scores. However, I need it to look at my most recent 20 scores and average the lowest 8.

Here was the formula I found:

=AVERAGE(QUERY(SORT(K3:K, ROW(K3:K)*ISNUMBER(K3:K),0),"select * LIMIT 8"))

Best Answer

Say the dates are in column A starting at A2 and the scores are in column B starting at B2. Assume the data is not sorted in any way.

In C2 enter:

=sort(B2:B26,A2:A26,false)

Column C are the scores sorted with the most recent at the top. In D2 enter:

=sort(C2:C21,1,true)

Column D are the most recent 20 scores sorted from lowest to highest. Finally in E2 enter:

=average(D2:D9)

This is the average of the eight lowest scorers

enter image description here

Related Topic