Excel- running/moving monthly average

[bopper]

Columns:
A: date
B: gallons filled
I: gallons used

Rows:
Fill in each day that a car is used and each fill-up. Could be 10 rows, could be 100 rows.

I'm making a vehicular fuel tracker. Each fill-up and daily trips are tracked. It'd be easy enough to calculate a monthly average for a complete year, but I want to show a monthly average for the information available at any given time. I currently have 4 months in the tracker and want it to give me a monthly average. I'd think that I'd only need two data points- date and gallons. It'd probably be best to use gallons used since fill-ups are so sporadic. Is there a way to do this without any add-ons?

 

[Trizoy]

If you want to average a group of values in a common column when they have a common row value, you can you use =AverageIf(range, criteria, range_for_averaging) to average the values.

If you have a date column, add another column (say column J) that has the month based on the date. Like =text(a2, "mmm"). This will write Jan, Feb, Mar etc of the date. This is the column you will group or use as criteria. Fill this down to the other rows.

Then use =AverageIf(J2:J50, "Jan", I2:I50)

This will look in J2:J50 where "Jan" is found, and average the values from I2:I50.

When you get into 2 years of data(multiple January's, you can use =text(j2, "mmm yyyy") for grouping by months in a specific year .

Hope it helps.

 

[Intel_man]

You can do this using Pivot Tables.

Set up your spreadsheet with the columns as listed and format them as Tables and give it a name. You can then create a PivotTable for that named Table and you can get it to Sort and Calculate rolling averages per month.