Excel - Take Average of Monthly Data

Question

I have a historical data set for commodity pricing. Throughout the data set, the data starts inputting prices on specific days, rather than the average of the entire month. In order to keep the flow of having only the average pricing for the months.

In the best case scenario, I would use an Averageif function, however, the data for each month doesn't display a consistent amount of days.

How can I automate this the process: If the month is the same as the previous row and different than the next row, calculate the average of the ^ rows until you hit the next month.

Here's a simple display of what I mean:

]1

Answer 1

You can use a pivot table to get the output you want. It will also be neatly organized instead of having your averages mixed in with a mostly blank column. Photo below shows the set-up/output of a pivot table generated with random data.