Need to find average year model of buses.

[JaeBon]

I have a list of year models for buses. Is there a way to use an excel formula to find the average year model of all of the buses? I tried changing the format of the date column to numbers, but the average formula does just that--adds the numbers and averages them. I tried median, but it gives the median number in the list. Thanks for reading my message.

 

[SkipVought]

JaeBon,

Please post an example of the data that you are trying to average.

Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[GlennUK]

Hi JaeBon,

you say ...

but the average formula does just that--adds the numbers and averages them.

Why isn't this what you want? Can you give examples? Are the years for the models integer years or Excel dates?



Cheers, Glenn.

My grandfather was ill, and my grandmother smeared goose-grease on his back. He went downhill very quickly after that.

 

[SkipVought]

I'me guessing here, but if you'd use the Pivottable Wizard and use Count of BusYear to get counts for each buss year.

Then, an AVERAGE will be easy to calculate.

Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[JaeBon]

Thanks to all of you for such quick responses. I know this is probably VERY elementary to you all, but here is what I need to do. If a school system has a number of busses by year model, I need to find the average age of all their busses. For example:

Year Model
EXCEL DATE FORMAT
1998
2000
1999
2004
2001
1996

I need the average model year date of the busses.

I tried this (Pleaase don't roll off your chair laughing!)

Model Age
1999 5
2001 3
2001 3
2002 2
1996 8
1994 10
2000 4
2004 0
1980 24
Average Age = 7
Average Model Yr. = 1997

I found the age of the bus by subtracting the model from the current year with a formula something like =$A$2-E16.
Then I found the average of the age column with a formula=AVERAGE(F16:F24).
Then to find the Average Model Year, I put a formula to subtract the average age from the current year =$A$2-E27. Does it look as if I'm on the right track. There may be a much easier way, but so far, this is all I can come up with. I hate to appear so "Dense" and I do appreciate all your help.

 

[SkipVought]

Looks like you're on the right track.

Since you have a REAL DATE that is formatted to DISPLAY the Year, when you AVERAGE the dates, you get a DATE, that needs to be formatted as YEAR to display the result that you are looking for.

Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[JaeBon]

Thanks so much--sometimes I try to make easy things difficult! I appreciate you all.