# How do I calculate different age ranges from a column of different ages?

1. ## How do I calculate different age ranges from a column of different ages?

Hi Folks
I'm from decidely damp Dorset though it's still beautiful.
I have created a column that displays a real time age based on system date. Date of birth is subtracted from the system 'now' and displays the age. What I need to do is calculate different age ranges within that column so that for all those aged 16-17 return a result in one exterior cell, those aged 18-21 return a result in a seperate cell, those aged 22-24 return a result in another cell and so on.
Column A is: =NOW()
Column E is =(A4-D5)/365
With Column D serving as input for date of birth
Hope someone can put me right...
Cheers fellas
:-)
Martin

2. ## Re: Intro and (probably) stupidly easy question

Hi Martin and welcome to the forum

Because thread titles are used in searching the forum it is vital they be written to accurately describe your thread content or overall objective using ONLY search friendly key words. That is, your title used as search terms would return relevant results.

Many members will look at a thread title, and if it is of interest to them, or it falls within their area of expertise, they might only open those threads.

Look at it this way...if you typed that title into google, what would you expect to get back?
To change a Title on your post, click EDIT on you're 1st post, then Go Advanced and change your title

3. ## Re: Intro and (probably) stupidly easy question

Thank you- changed post title.

4. ## Re: How do I calculate different age ranges from a column of different ages?

Hi

I can help with this.

Please post a worksheet with samples of before and after data.

5. ## Re: How do I calculate different age ranges from a column of different ages?

try this...

=if(and(YEAR(NOW())-YEAR(C29)>=16,YEAR(NOW())-YEAR(C29)<=17,YEAR(NOW())-YEAR(C29),"")
you can then either change the hard-coded 16 and 17 to a cell reference, or just change the values for each column

6. ## Re: How do I calculate different age ranges from a column of different ages?

I have attached the entire sheet... hope that's what you mean.

7. ## Re: How do I calculate different age ranges from a column of different ages?

=if(and(YEAR(NOW())-YEAR(C29)>=16,YEAR(NOW())-YEAR(C29)<=17,YEAR(NOW())-YEAR(C29),"")
Says too many arguments...
Now it says'missing a parenthesis...'

8. ## Re: How do I calculate different age ranges from a column of different ages?

where do you want the answers, and what are your age ranges?

edit: oops too many brackets lol, and i meant to change now() to today()...

=IF(AND(YEAR(TODAY())-YEAR(D4)>=16,YEAR(TODAY())-YEAR(D4)<=17),YEAR(TODAY())-YEAR(D4),"")

now() includes dat AND time. today() only uses date

9. ## Re: How do I calculate different age ranges from a column of different ages?

Age range - 16-17 in Cell E1004
18-21 in Cell E1005
22-24 in Cell E1006
25-49 in Cell E1007
50-64 in Cell E1008
65+ in Cell E1009
If you open the attachement (above), you can see what I'm aiming for...

10. ## Re: How do I calculate different age ranges from a column of different ages?

I put that in with these values;
=IF(AND(YEAR(TODAY())-YEAR(D4:E1002)>=16,YEAR(TODAY())-YEAR(D4:D1002)<=17),YEAR(TODAY())-YEAR(D4:D1002),"")
It came up with a #VALUE!

11. ## Re: How do I calculate different age ranges from a column of different ages?

OK didnt fully understand your request. I am out of time now (should be back in an hour or so). In the mean time, this should get you headed in the right direction...

=COUNTIFS(E4:E1002,">="&16,E4:E1002,"<="&17)

Note the 17 you have in E102 is actually 17.2821917808219, so maybe change the formula to...

=COUNTIFS(E4:E1002,">"&15,E4:E1002,"<"&18)

12. ## Re: How do I calculate different age ranges from a column of different ages?

Martin's profile says 2003 so for an Excel 2003 compatible formula try this for 16-17

=COUNTIF(E\$4:E\$1002,">=16")-COUNTIF(E\$4:E\$1002,">=18")

you can use similar formulas for your other ranges

13. ## Re: How do I calculate different age ranges from a column of different ages?

Daddylonglegs... That is BRILLIANT and works like a charm! Thank you so much all for your input!

Martin

14. ## Re: How do I calculate different age ranges from a column of different ages?

DDL the profile says 2003 but the file was .xlsx - hence the countifS()

15. ## Re: How do I calculate different age ranges from a column of different ages?

home is 2003, work is 2007
lol
thanks all