I am having a hard time wrapping my mind around what way I should start
working up some code to separate people by age on a spreadsheet.

I will have a list of say 10,000 people. Column H is date of birth. I
need to put those people into their age groups as totals on a second
page.

For example:

the age bands are
0-29
30-34
35-39
40-44
50-99

The mind warping problem is my if statement. I figure somehow I have
to do an if a person's age is between 0 and 29 then put in B2 on
sheet2. But how would I tell it to calculate it? This is something
that would be used once a month so would it be something like if
today's date minus date in column H = 30,31,32,33, or 34 then move to
sheet2 B3?

I have this feeling I am making this way more difficult than I need to
but I can't think of a simple way to do it.

Thanks in advance
Mary

On the data sheet use this formula for determing ages
=($A$1-H2)/365 ' A1 has formula "=today()"
'You can alter this to your needs, of course.

On the summary sheet use this formula
=COUNTIF(Sheet1!H2:H10000,"<=29") '
=IF(Sheet1!H2:H10000 29,(COUNTIF(Sheet1!H2:H10000,"<=34")),"")
--
Best wishes,

Jim


"Dagonini" wrote:

I am having a hard time wrapping my mind around what way I should start
working up some code to separate people by age on a spreadsheet.

I will have a list of say 10,000 people. Column H is date of birth. I
need to put those people into their age groups as totals on a second
page.

For example:

the age bands are
0-29
30-34
35-39
40-44
50-99

The mind warping problem is my if statement. I figure somehow I have
to do an if a person's age is between 0 and 29 then put in B2 on
sheet2. But how would I tell it to calculate it? This is something
that would be used once a month so would it be something like if
today's date minus date in column H = 30,31,32,33, or 34 then move to
sheet2 B3?

I have this feeling I am making this way more difficult than I need to
but I can't think of a simple way to do it.

Thanks in advance
Mary

I forgot to remind you to change the cell references to suit your situation.
Of course you knew that anyway.
--
Best wishes,

Jim


"Dagonini" wrote:

I am having a hard time wrapping my mind around what way I should start
working up some code to separate people by age on a spreadsheet.

I will have a list of say 10,000 people. Column H is date of birth. I
need to put those people into their age groups as totals on a second
page.

For example:

the age bands are
0-29
30-34
35-39
40-44
50-99

The mind warping problem is my if statement. I figure somehow I have
to do an if a person's age is between 0 and 29 then put in B2 on
sheet2. But how would I tell it to calculate it? This is something
that would be used once a month so would it be something like if
today's date minus date in column H = 30,31,32,33, or 34 then move to
sheet2 B3?

I have this feeling I am making this way more difficult than I need to
but I can't think of a simple way to do it.

Thanks in advance
Mary

How accurate do you have to be:

=sumproduct(--(((date-H1:H50)/365.25)=30),--(((date-H1:H50)/365.25)<35))

should do 30 to 34

--
Regards,
Tom Ogilvy





"Dagonini" wrote:

I am having a hard time wrapping my mind around what way I should start
working up some code to separate people by age on a spreadsheet.

I will have a list of say 10,000 people. Column H is date of birth. I
need to put those people into their age groups as totals on a second
page.

For example:

the age bands are
0-29
30-34
35-39
40-44
50-99

The mind warping problem is my if statement. I figure somehow I have
to do an if a person's age is between 0 and 29 then put in B2 on
sheet2. But how would I tell it to calculate it? This is something
that would be used once a month so would it be something like if
today's date minus date in column H = 30,31,32,33, or 34 then move to
sheet2 B3?

I have this feeling I am making this way more difficult than I need to
but I can't think of a simple way to do it.

Thanks in advance
Mary