Excel Puzzler - Problem Solved with AverageIf Function

I could write SQL to solve this problem.. but the data was in Excel.

I pulled pricing and mileage data from several car for-sale sites and dropped it in an Excel sheet. Columns include year, price, kilometers (mileage), etc. Here is a snippet of the data:

Now I had some data in Excel that would mimic how it would be stored in a relational database but I wanted to analyze it.

I wanted to get the average price and mileage for each year. I started my IT career writing SQL, so I would do something like:

select year, avg(year), avg(mileage)

from car_data

group by year;

I figured that most users use maybe 5% of the features in Excel, so with a quick search, I found the "averageif" function.

I tried the following formula in cell N3:

=AVERAGEIF($C$3:$C$146,M3,$D$3:$D$146)

How it works is to evaluate the data in cells C3 to C146 (the year column), compare it to a value in cell M3 (where I put a list of years in column M) and if any matches are found, calculate the average of the data in column D (price).

Here is the end result. A real-life working example would be calculating the average spend by year, by customer ID, etc. I hope you found this useful!

About the Author
Darren Mallette
I am a senior systems consultant specializing in performance monitoring for virtualization, databases, applications and end user experience. I've been in the IT world for over 20 years, with experience...