0

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

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.