+ Reply to Thread
Results 1 to 10 of 10

Monthly Average Based off a start and end date?

  1. #1
    Registered User
    Join Date
    11-20-2007
    Location
    Menlo Park, CA
    MS-Off Ver
    Office 2019 Pro Plus
    Posts
    33

    Monthly Average Based off a start and end date?

    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.
    Attached Files Attached Files
    Last edited by ImpetuousRacer; 06-14-2012 at 12:41 PM.

  2. #2
    Forum Contributor
    Join Date
    08-22-2011
    Location
    Texas, United States
    MS-Off Ver
    Excel 2007 and 2010
    Posts
    516

    Re: Monthly Average Based off a start and end date?

    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?

  3. #3
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Rocester, New York, USA
    MS-Off Ver
    Excel 2007:10
    Posts
    2,019

    Re: Monthly Average Based off a start and end date?

    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.

  4. #4
    Registered User
    Join Date
    11-20-2007
    Location
    Menlo Park, CA
    MS-Off Ver
    Office 2019 Pro Plus
    Posts
    33

    Re: Monthly Average Based off a start and end date?

    Quote Originally Posted by amotto11 View Post
    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!
    Last edited by ImpetuousRacer; 06-12-2012 at 06:08 PM.

  5. #5
    Registered User
    Join Date
    11-20-2007
    Location
    Menlo Park, CA
    MS-Off Ver
    Office 2019 Pro Plus
    Posts
    33

    Re: Monthly Average Based off a start and end date?

    Quote Originally Posted by ben_hensel View Post
    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.
    Attached Files Attached Files

  6. #6
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,642

    Re: Monthly Average Based off a start and end date?

    In J5 and copy across and down,

    = MAX(0, $H5/($E5-$D5+1) * (MIN($E5, EOMONTH(J$4, 0)) - MAX($D5, J$4) + 1))
    Entia non sunt multiplicanda sine necessitate

  7. #7
    Registered User
    Join Date
    11-20-2007
    Location
    Menlo Park, CA
    MS-Off Ver
    Office 2019 Pro Plus
    Posts
    33

    Re: Monthly Average Based off a start and end date?

    Quote Originally Posted by shg View Post
    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!
    Attached Files Attached Files

  8. #8
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,642

    Re: Monthly Average Based off a start and end date?

    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.

  9. #9
    Registered User
    Join Date
    11-20-2007
    Location
    Menlo Park, CA
    MS-Off Ver
    Office 2019 Pro Plus
    Posts
    33

    Re: Monthly Average Based off a start and end date?

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

  10. #10
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,642

    Re: Monthly Average Based off a start and end date?

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1