Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
up vote 1 down vote accepted

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))
share|improve this answer
I really like the query style. This was exactly what I needed. – Dr. Cyanide 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 posting your answer, you agree to the privacy policy and terms of service.

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