Counting by age (19 and under, 20-29, 30-39, etc)...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to count 1500+ records with the age (20, 24, 26, 31, 44, etc) and then break it down to do a count of people between 20-29

We'd like to find the average of data based on those people

First off, I want to find the count. Here is what I tried: =COUNTIF(C2:C290,"<=" & 19

This is only for 290 of the people, but you get the idea...right

Thanks for any help you may have
Tony
 
Tony

one way:

=SUMPRODUCT((C2:C290>19)*(C2:C290<=29))

Regards

Trevor


Tony said:
I'm trying to count 1500+ records with the age (20, 24, 26, 31, 44, etc)
Click to expand...
and then break it down to do a count of people between 20-29.
 
Let's assume ages in B1:B20. Insert the values
19,29,39,49,59,69,79,89,99 in a range (say E5:E13). Now
select F5:F13 and insert the following formula:

=FREQUENCY(B1:B20,E5:E13)

and press <enter> while holding down the <ctrl> and
<shift> keys together. This will give you a count of <=19,
20-29, 30-39, etc.

HTH
Jason
Atlanta, GA
-----Original Message-----
I'm trying to count 1500+ records with the age (20, 24,
Click to expand...
26, 31, 44, etc) and then break it down to do a count of
people between 20-29.
We'd like to find the average of data based on those people.

First off, I want to find the count. Here is what I
Click to expand...
tried: =COUNTIF(C2:C290,"<=" & 19)
 
Hi
try
=SUMPRODUCT(--(C2:C290>=20),--(C2:C290<=29))
or
=COUNTIF(C2:C290,"<=29")-COUNTIF(C2:C290,"<20")

to get the average for the people between 20-29 use the following array
formula (entered with CTRL+SHIFT+ENTER):
=AVERAGE(IF((C2:C290>=20)*(C2:C290<=29),C2:C290))

or a non-array alternative:
=SUMPRODUCT(--(C2:C290>=20),--(C2:C290<=29),C2:C290)/SUMPRODUCT(--(C2:C
290>=20),--(C2:C290<=29))
 
Pivot Table - If you just have those numbers then give the column a heading and
drag it into both the Row fields and the Data field, then right click on the Row
fields, select group and Outline and hit group, choose 10 as the group number
and then right clcik on the data in the Data field, hit Field settings and then
choose Count as opposed to Sum.

Just repeat the last part and choose Average to get your last request.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



Tony said:
I'm trying to count 1500+ records with the age (20, 24, 26, 31, 44, etc) and
Click to expand...
then break it down to do a count of people between 20-29.
 
Thanks Trevor

I tried it that way and it worked for 20-29, 30-39, 40-49...but I'm not sure on how to do it for people over 50

It's got to be a very simple formula, but I'm not seeing it. I tried to modify what you gave me for that one and tried this
=SUM((C2:C290>50)
but I get an error of #Value

Thanks again for your help Trevor.
 
Tony

back to your original approach but SUMIF rather than COUNTIF ;-)

=SUMIF(C2:C290,">"&50)

Regards

Trevor


Tony said:
Thanks Trevor.

I tried it that way and it worked for 20-29, 30-39, 40-49...but I'm not
Click to expand...
sure on how to do it for people over 50?
It's got to be a very simple formula, but I'm not seeing it. I tried to
Click to expand...
modify what you gave me for that one and tried this:
 
Back
Top