I have a table with column A having a 0 or a 1, and column B having a value that I want to average. I know I can use averageif(A:A, "=1", B:B) to get the average of all of the values that have a 1 in column A, but I want something a bit more refined.

I want to be able to limit my average to the first ten 1's that show up in A.

Is this possible? I'd assume so, and that my problem is I just can't find the words to articulate what I need.


In a google spreadsheet, something like this should work:

=average(array_constrain(filter(B:B, A:A=1),10,1))

or alternatively:

=average(query(A:B, "Select B where A =1 limit 10",0))
  • I really like the query style. This was exactly what I needed. Mar 6 '15 at 15:40
  • You are welcome, Dr. Cyanide. Query is indeed a very powerful function.
    – JPV
    Mar 6 '15 at 16:25

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.