# How to calculate average monthly spend from unpredictable dates?

1. ## How to calculate average monthly spend from unpredictable dates?

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!!

2. ## Re: How to calculate average monthly spend from unpredictable dates?

Hi,

Welcome to forum.

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

Formula:
`Please Login or Register  to view this content.`

3. ## Re: How to calculate average monthly spend from unpredictable dates?

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

4. ## Re: How to calculate average monthly spend from unpredictable dates?

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)