Monthly Average Based off a start and end date?

**ImpetuousRacer** · 06-12-2012, 03:49 PM

Ok, I have a spreadsheet with hundreds of line items with a start date, end date, and invoice total.
The end goal is to know how much revenue I can expect per month based on averaging out the total based on the date range.

For Example, Let's say I have a start date of 11/20/2011 and an end date of 03/05/2012. The total is $5000

There are 106 days, $47.71 Daily Average.

I need the formula to do the following:
-Look at the date range, enter 0 if it's before or after that date range
-Provide the average correctly (Meaning taking in account number of days for that particular month)
-Calculate the correct total for that month when it isnt a complete month.

This may be super easy to do in a Pivot Table? Im looking for any solution. Pivot, VBA, Formula, etc.
Attached is an example. I had a formula I used, but didnt work for date ranges with multiple years, and didnt work with start and end dates one month apart.

Thanks for the help.

**amotto11** · 06-12-2012, 05:33 PM

Which columns show the average on this sheet? also which one's show your attempted formula, i am guessing the really long one in most of the boxes. Are columns J through AH supposed to be the averages? If so why exactly are the months in the column headins?

**ben_hensel** · 06-12-2012, 05:42 PM

I don't 100% understand the way you've set up your data here, so I won't be providing specific syntax, but my first attempt would be, a SUMIFS divided by a COUNTIFS.

For the SUMIFS...

SUMIFS(value_range, date_range, ">"&low_date_cell, date_range, "<"&high_date_cell)

COUNTIFS(date_range,">"&low_date_cell, date_range ,"<"&high_date_cell)

Then the average would be the SUMIFS / COUNTIFS.

Anyway, a pivot table might do it, but I don't think you've got the right set-up for that data. I could be wrong thou.

**ImpetuousRacer** · 06-12-2012, 06:05 PM

Originally Posted by amotto11

Which columns show the average on this sheet? also which one's show your attempted formula, i am guessing the really long one in most of the boxes. Are columns J through AH supposed to be the averages? If so why exactly are the months in the column headins?

Columns J through AH show the average using the long formula (Example formula: Cell J6) I created. In some instances my formula works fine, other times it doesnt.
For Example, if you add J6:U6, you get $240k, which matches the 240k amount in cell H6. The numbers aren't 100% accurate.

The headings all relate to the formula and show what the average per month is.
Regardless, you can throw my J through AH in the garbage, and reinvent this from the ground up. Data I have is B:H.
I just showed J through AH to show how I wanted the averages laid out.

I have 30k for Client 1, he is running 12/20/2010 to 12/31/2011. I want to know how much per month Im going to be paid by client 1 monthly from that 30k and want to know what I can expect to receive each month if that makes sense.

Thanks for the help!

**ImpetuousRacer** · 06-12-2012, 06:27 PM

Originally Posted by ben_hensel

I don't 100% understand the way you've set up your data here, so I won't be providing specific syntax, but my first attempt would be, a SUMIFS divided by a COUNTIFS.

For the SUMIFS...

SUMIFS(value_range, date_range, ">"&low_date_cell, date_range, "<"&high_date_cell)

COUNTIFS(date_range,">"&low_date_cell, date_range ,"<"&high_date_cell)

Then the average would be the SUMIFS / COUNTIFS.

Anyway, a pivot table might do it, but I don't think you've got the right set-up for that data. I could be wrong thou.

Tried the formula you mentioned but receiving some errors. Not sure what I'm doing wrong. Attached is an example. Thanks.

**shg** · 06-12-2012, 07:51 PM

In J5 and copy across and down,

= MAX(0, $H5/($E5-$D5+1) * (MIN($E5, EOMONTH(J$4, 0)) - MAX($D5, J$4) + 1))

**ImpetuousRacer** · 06-13-2012, 05:44 PM

Originally Posted by shg

In J5 and copy across and down,

= MAX(0, $H5/($E5-$D5+1) * (MIN($E5, EOMONTH(J$4, 0)) - MAX($D5, J$4) + 1))

This is awesome! Seems to work good. However, I was supplied another formula and it results differ from this one. Both formulas seem to be a little off.
The grand total of the monthly breakout do match that of the IO total, just how they are broken out is different.

Also, I created a sum and check to make sure that the summed total from the breakout matches the IO total. It matches on this small sample, but when working with the real worksheet with a lot more line items, it doesn't match up?

Please view the attached and let me know your thoughts. The "Formula 2" tab is your formula. Thanks so much for the help on this!

**shg** · 06-13-2012, 06:53 PM

The formula you have in there doesn't count the first day. Change the start date to 1/31/11 and you get $0 in January.

**ImpetuousRacer** · 06-13-2012, 09:07 PM

Great. Thanks for all your help. Final question. Could you break down how the formula works in regards in regards to the min and max functions, I havent used those before. Im sure most people would get this, I just want to wrap my head around how it all works. Thanks so much.

MAX(0,IO_TOTAL/(End_Date-StartDate+1)*(MIN(End_Date,EOMONTH(Specified_Month_Average_Is_For,0))-MAX(Start_Date,Specified_Month_Average_Is_For)+1)))

**shg** · 06-13-2012, 09:13 PM

It calculates the number of days from the later of the project start date and the beginning of the month to the earlier of the project end date and the end of the month, and multiplies by the prorated daily amount.