Average Calculation

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....

Please help me to write a formula.

This should do for 11-20. Adapt as necessary for other ranges.

=(SUMIF(A1:A10,">=11",A1:A10)-SUMIF(A1:A10,">=21",A1:A10))/(COUNTIF(A1:A10,">=11")-COUNTIF(A1:A10,">=21"))

There may be more elegant ways of doing it.
--
Ian
--
"smallcap" <smallcap.1xjzmf_1130403959.1331@excelforum-nospam.com> wrote in
message news:smallcap.1xjzmf_1130403959.1331@excelforum-nospam.com...
>
> 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....
>
> Please help me to write a formula.
>
>
> --
> smallcap
> ------------------------------------------------------------------------
> smallcap's Profile:
> http://www.excelforum.com/member.php...o&userid=28383
> View this thread: http://www.excelforum.com/showthread...hreadid=479718
>

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

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"smallcap" <smallcap.1xjzmf_1130403959.1331@excelforum-nospam.com> wrote in
message news:smallcap.1xjzmf_1130403959.1331@excelforum-nospam.com...
>
> 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....
>
> Please help me to write a formula.
>
>
> --
> smallcap
> ------------------------------------------------------------------------
> smallcap's Profile:
http://www.excelforum.com/member.php...o&userid=28383
> View this thread: http://www.excelforum.com/showthread...hreadid=479718
>

I thought there'd be a better way to do it :-)

--
Ian
--
"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:OJTSyHu2FHA.3704@TK2MSFTNGP10.phx.gbl...
> =AVERAGE(IF((D1:D100>10)*(D1:D100<=20),D1:D100))
>
> will give the average for 11-20. This is an array formula, so commit with
> Ctrl-Shift-Enter
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "smallcap" <smallcap.1xjzmf_1130403959.1331@excelforum-nospam.com> wrote
> in
> message news:smallcap.1xjzmf_1130403959.1331@excelforum-nospam.com...
>>
>> 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....
>>
>> Please help me to write a formula.
>>
>>
>> --
>> smallcap
>> ------------------------------------------------------------------------
>> smallcap's Profile:
> http://www.excelforum.com/member.php...o&userid=28383
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=479718
>>
>
>

Bob Phillips wrote:
> "smallcap" <smallcap.1xjzmf_1130403959.1331@excelforum-nospam.com> 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:D100>10)*(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.)

<joeu2004@hotmail.com> wrote in message
news:1130435112.407216.15270@g49g2000cwa.googlegroups.com...
> Bob Phillips wrote:
> > "smallcap" <smallcap.1xjzmf_1130403959.1331@excelforum-nospam.com>
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:D100>10)*(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?

Correct,

> 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?

No, D1:D100 is referring to that range, and in the context of the array
formula given here it means that each cell is eaxmined one at a time for
comparison against the condition to return True/False

> 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?

Taht is correct. Wheb not True, it returns a False.

> 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?

Because all ranges are the same size, absiolute impeartive.

> 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.

I have no idea where you could learn this per se. I have gleaned from many
other things I have seen on my Excel journey.

It is not really about average, its is more a way of reducing a range to a
subset of that range that meet a condition (or two conditions) using array
formulae. The AVERAGE is then just used against the resulting data. It could
just as easily have been SUM, MIN, MAX instead of average.

If you write to me offline, I will try and explain in more detail, over a
period of time.

And ... *just in case* you've been misunderstood by everyone (except me
<g>), and by ranges you mean *cell* ranges not value ranges, try this, and
copy down as needed to average every 10 rows in Column A:

=AVERAGE(INDEX(A:A,10*ROW(1:1)-9):INDEX(A:A,10*ROW(1:1)))
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"smallcap" <smallcap.1xjzmf_1130403959.1331@excelforum-nospam.com> wrote in
message news:smallcap.1xjzmf_1130403959.1331@excelforum-nospam.com...
>
> 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....
>
> Please help me to write a formula.
>
>
> --
> smallcap
> ------------------------------------------------------------------------
> smallcap's Profile:
http://www.excelforum.com/member.php...o&userid=28383
> View this thread: http://www.excelforum.com/showthread...hreadid=479718
>