+ Reply to Thread
Results 1 to 15 of 15

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

  1. #1
    Registered User
    Join Date
    04-09-2013
    Location
    Devon, England
    MS-Off Ver
    Excel 2010
    Posts
    25

    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
    Last edited by MartinHK; 04-09-2013 at 01:09 PM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Intro and (probably) stupidly easy question

    Hi Martin and welcome to the forum

    We would love to help you with your question, but 1st, in accordance with the forum rules, please rename your thread to something more meaningful, that actually describes your problem.

    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
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    04-09-2013
    Location
    Devon, England
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Intro and (probably) stupidly easy question

    Thank you- changed post title.

  4. #4
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    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. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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. #6
    Registered User
    Join Date
    04-09-2013
    Location
    Devon, England
    MS-Off Ver
    Excel 2010
    Posts
    25

    Smile 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.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    04-09-2013
    Location
    Devon, England
    MS-Off Ver
    Excel 2010
    Posts
    25

    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...'
    Last edited by MartinHK; 04-09-2013 at 01:50 PM.

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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
    Last edited by FDibbins; 04-09-2013 at 01:50 PM.

  9. #9
    Registered User
    Join Date
    04-09-2013
    Location
    Devon, England
    MS-Off Ver
    Excel 2010
    Posts
    25

    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. #10
    Registered User
    Join Date
    04-09-2013
    Location
    Devon, England
    MS-Off Ver
    Excel 2010
    Posts
    25

    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. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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. #12
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    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
    Audere est facere

  13. #13
    Registered User
    Join Date
    04-09-2013
    Location
    Devon, England
    MS-Off Ver
    Excel 2010
    Posts
    25

    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. #14
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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. #15
    Registered User
    Join Date
    04-09-2013
    Location
    Devon, England
    MS-Off Ver
    Excel 2010
    Posts
    25

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

    home is 2003, work is 2007
    lol
    thanks all

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1