Thread: Year of Mfg to Age help...

1. Year of Mfg to Age help...

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. Try:

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

where A2:A4 is the range of Years of Manufacturing

3. Originally Posted by NBVC
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. When you click in one of the cells in the range, does it show a date or just the year?

5. Originally Posted by NBVC
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. Hmm... interesting. Do you have blanks in your range?

if so try:

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

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

if so try:

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