Year of Mfg to Age help...

I

imapoboy2

New member
Joined
Jul 2, 2013
Messages
12
Reaction score
0
Points
1
Age
56
Location
Wisconsin
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.
 
Try:

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

where A2:A4 is the range of Years of Manufacturing
 
NBVC said:
Try:

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

where A2:A4 is the range of Years of Manufacturing
Click to expand...

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?
 
When you click in one of the cells in the range, does it show a date or just the year?
 
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.
 
NBVC said:
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.
Click to expand...

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!!
 
You must log in or register to reply here.
Back
Top