How to calculate average monthly spend from unpredictable dates?

[edwg]

Hi,

I don't use Excel much so apologies if this is an obvious answer.. I have a sheet for how much I spend on fuel in my car.. I have a cell for the date and a cell for the cost but I'd like to calculate how much, on average, I spend per month.

Example

24/1/15 £40
15/2/15 £28
26/2/15 £30
11/3/15 £22

As you can see, there are two dates in the above example which are in february so I would need to work out the mean for each month first, then the mean of those averages.. Any ideas??

Thank you!!

[shukla.ankur281190]

Hi,

Welcome to forum.

You can use =AVERAGEIFS($B$3:$B$6,$A$3:$A$6,">="&E2,$A$3:$A$6,"<="&F2)

=AVERAGEIFS($B$3:$B$6,$A$3:$A$6,">="&E2,$A$3:$A$6,"<="&F2)

Please check the attached file.

[edwg]

Hi,

Sorry I've taken so long to get back to you - I've moved house and all sorts since then.. Thanks - this calculates the average spend per transaction that month, but then how do I make it calculate the average per month over say a year? Sorry if I didn't explain well the first time.

I'd like to be able to enter how much is spent on which day and then for excel to add that into a monthly average before averaging the whole data (say A2:A100) to find out the monthly average over the whole term.

Hope that makes sense!

Thanks

[JeteMc]

Sounds like a situation where a pivot table would work. Here I have added a pivot table to shukla.ankur281190's attachment, so that you can see if does what you want.
Copy of Averageifs-1.xlsx
Let me know if you have any questions.