hi all,
I have a column where i put the students' Dates of Birth. I want a formula
that calculate different age groups from that column.
for example: formula to calculate the number of students between 10 and 15
years, and another formula to calculate the number of students older than 15.
How do i do that?
Many thanks - Tendresse
Using Excel 2003

Assume DOBs are within A2:A100

.. number of students between 10 and 15 years:
=SUMPRODUCT((A2:A100<"")*(DATEDIF(A2:A100,TODAY() ,"y")=10)*(DATEDIF(A2:A100,TODAY(),"y")<=15))

.. number of students older than 15:
=SUMPRODUCT((A2:A100<"")*(DATEDIF(A2:A100,TODAY() ,"y")15))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Tendresse" wrote:
I have a column where i put the students' Dates of Birth. I want a formula
that calculate different age groups from that column.
for example: formula to calculate the number of students between 10 and 15
years, and another formula to calculate the number of students older than 15.
How do i do that?
Many thanks - Tendresse
Using Excel 2003

hi Max,
thanks for your prompt reply. I adjusted the formula to suit my workbook as
follows:

=SUMPRODUCT(('Main Data'!E10:E9654<"")*(DATEDIF('Main
Data'!E10:E9654,TODAY(),"y")=10)*(DATEDIF('Main
Data'!E10:E9654,TODAY(),"y")<15))

but i'm getting an #NUM! error .. any idea why?

"Max" wrote:

Assume DOBs are within A2:A100

.. number of students between 10 and 15 years:
=SUMPRODUCT((A2:A100<"")*(DATEDIF(A2:A100,TODAY() ,"y")=10)*(DATEDIF(A2:A100,TODAY(),"y")<=15))

.. number of students older than 15:
=SUMPRODUCT((A2:A100<"")*(DATEDIF(A2:A100,TODAY() ,"y")15))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Tendresse" wrote:
I have a column where i put the students' Dates of Birth. I want a formula
that calculate different age groups from that column.
for example: formula to calculate the number of students between 10 and 15
years, and another formula to calculate the number of students older than 15.
How do i do that?
Many thanks - Tendresse
Using Excel 2003

Fixed it ... i found a couple of dates in the range bigger than today's date.
Thanks a lot for your help .. much appreciated.

"Tendresse" wrote:

hi Max,
thanks for your prompt reply. I adjusted the formula to suit my workbook as
follows:

=SUMPRODUCT(('Main Data'!E10:E9654<"")*(DATEDIF('Main
Data'!E10:E9654,TODAY(),"y")=10)*(DATEDIF('Main
Data'!E10:E9654,TODAY(),"y")<15))

but i'm getting an #NUM! error .. any idea why?

"Max" wrote:

Assume DOBs are within A2:A100

.. number of students between 10 and 15 years:
=SUMPRODUCT((A2:A100<"")*(DATEDIF(A2:A100,TODAY() ,"y")=10)*(DATEDIF(A2:A100,TODAY(),"y")<=15))

.. number of students older than 15:
=SUMPRODUCT((A2:A100<"")*(DATEDIF(A2:A100,TODAY() ,"y")15))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Tendresse" wrote:
I have a column where i put the students' Dates of Birth. I want a formula
that calculate different age groups from that column.
for example: formula to calculate the number of students between 10 and 15
years, and another formula to calculate the number of students older than 15.
How do i do that?
Many thanks - Tendresse
Using Excel 2003

"Tendresse" wrote:
Fixed it ... i found a couple of dates in the range bigger than today's date.
Thanks a lot for your help .. much appreciated.

Welcome, glad you got it working over there.
Perhaps you could just take a moment to press the "Yes" button to the
question: "Was this post helpful to you?" from where you're reading this.
It'll ensure a longer shelf life to this thread for the general benefit of
other readers.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---