averaging data ranges

[Girish]

I've got a set of numbers (age) from 1 to about 100 and a related number (number of shoes) (s)he has. Total I have around 5000 rows. i want a chart that displays the average of shoes in the age range 1-10, 11-20 etc. Is there a way of doing this ?
Any hint will be appreciated

[Aladin Akyurek]

On 2002-05-03 08:18, Girish wrote:
I've got a set of numbers (age) from 1 to about 100 and a related number (number of shoes) (s)he has. Total I have around 5000 rows. i want a chart that displays the average of shoes in the age range 1-10, 11-20 etc. Is there a way of doing this ?
Any hint will be appreciated

Care to post 5 rows of your data?

[Girish]

Thanks for responding... here is a sample data ...

Age{
20,
39,
23,
20,
48,
37}

Shoes{
5,
4,
6,
9,
8,
5}

[Aladin Akyurek]

On 2002-05-03 08:38, Girish wrote:
Thanks for responding... here is a sample data ...

Age{
20,
39,
23,
20,
48,
37}

Shoes{
5,
4,
6,
9,
8,
5}

Lets suppose that the sample is in A1:B7 including labels.

In D1 enter: Cat

And from D2 on downwards enter: 10, 20, 30, 40, etc up to 100.

In E1 enter: Cat Avg
In E2 enter and copy down:

=SUMPRODUCT(($A$2:$A$7>D1)*($A$2:$A$7<=D2),($B$2:$B$7))/MAX(1,SUMPRODUCT(($A$2:$A$7>D1)*($A$2:$A$7<=D2)))

If you like, the denominator can also be written in terms of COUNTIF, but I leave that to you along with the graph that you want.

Aladin

[Girish]

It worked !
Thanks very much Aladin