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)
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:
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!
Thanks Darren... I got cleared my doubts on Excel Puzzler.