Year of Mfg to Age help...

imapoboy2

New member
Joined
Jul 2, 2013
Messages
11
Reaction score
0
Points
0
Excel Version(s)
2013
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.
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
Try:

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

where A2:A4 is the range of Years of Manufacturing
 

imapoboy2

New member
Joined
Jul 2, 2013
Messages
11
Reaction score
0
Points
0
Excel Version(s)
2013
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?
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
When you click in one of the cells in the range, does it show a date or just the year?
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
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.
 

imapoboy2

New member
Joined
Jul 2, 2013
Messages
11
Reaction score
0
Points
0
Excel Version(s)
2013
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!!
 
Top