Results 1 to 7 of 7

Thread: Year of Mfg to Age help...

  1. #1

    Question Year of Mfg to Age help...



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have a report that shows a list of vehicles by Year of Manufacturing. I need a formula that will calculate the average age of listed vehicles from the current date.

    A2 = 1995, A3 = 2003, A4 = 2011, etc... Today's date is 07-01-2013, I need the average age in Years form todays date. I hope this makes sense.

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,463
    Articles
    0
    Excel Version
    Excel 2016
    Try:

    =AVERAGE(INDEX(YEAR(TODAY())-A2:A4,0))

    where A2:A4 is the range of Years of Manufacturing


  3. #3
    Quote Originally Posted by NBVC View Post
    Try:

    =AVERAGE(INDEX(YEAR(TODAY())-A2:A4,0))

    where A2:A4 is the range of Years of Manufacturing
    NBVC,

    Thanks for the quick response... When I calculate the list manually, I come up with 11 years + months/days. When I tried your formula, changing the cells identified in my report, I get 143.1. The cells are formatted general. Do they need to be formatted as a date or number?

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,463
    Articles
    0
    Excel Version
    Excel 2016
    When you click in one of the cells in the range, does it show a date or just the year?


  5. #5
    Quote Originally Posted by NBVC View Post
    When you click in one of the cells in the range, does it show a date or just the year?

    Just a year... (i.e. 2011, 2008, 2002, etc...)

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,463
    Articles
    0
    Excel Version
    Excel 2016
    Hmm... interesting. Do you have blanks in your range?

    if so try:

    =AVERAGE(INDEX((A2:A4<>"")*(YEAR(TODAY())-A2:A4),0))

    adjusting the ranges again.


  7. #7
    Quote Originally Posted by NBVC View Post
    Hmm... interesting. Do you have blanks in your range?

    if so try:

    =AVERAGE(INDEX((A2:A4<>"")*(YEAR(TODAY())-A2:A4),0))

    adjusting the ranges again.
    Yes I do have blank spaces... Not sure why I didn't say that from the start. It is working perfect now with your last formula. Greatly, greatly appreciated!!

Posting Permissions

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