View Single Post
  #5   Report Post  
 
Posts: n/a
Default Average Calculation

Bob Phillips wrote:
"smallcap" wrote:
I have a set of data(numbers)like
1,60,50,50,44,24,10,15,13,5,15 in a column.
Now, I have to calculate the average of ranges 0-10, 11-20, 21-30....


=AVERAGE(IF((D1:D10010)*(D1:D100<=20),D1:D100))

will give the average for 11-20. This is an array formula,
so commit with Ctrl-Shift-Enter


Could you explain this formula somewhat?

First, I think you assume the OP's data is in D1:D100.
Right?

Second, I infer that the syntax "D1:D100" returns a
(relative?) row number -- at least in this context.
Hence, the test for "10" and "<=20". And the two
conditional clauses are multiplied to effect an AND
operation. Right?

And somehow, when the IF condition is true, the
corresponding element is selected from D1:D100 in
the "then" part of the IF function. Right?

Does that work only because the same range, D1:D100,
is used in all parts of the IF function, or only
because all ranges have the same number of elements?

Finally, where would I learn about this "trick"?
I see no hint of it on the AVERAGE help page. The
only clue I see is that ":" appears in the precedence
table on the "about calculation order" help page.
But my experience has been that ranges cannot be
combined with other operators in all circumstances.

(Perhaps that conclusion is wrong, and I simply do
not know how.)