+ Reply to Thread
Results 1 to 8 of 8

Month to Date Calculation - Driving Me Crazy!

  1. #1
    Registered User
    Join Date
    10-13-2010
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    59

    Month to Date Calculation - Driving Me Crazy!

    Hey guys,

    Need some help. I'm trying to create an end of the month projection for some sales data.

    For example,I have some sales data for each day for multiple products in a table. I also have a column called monthly projection that I would like to be populated with an end of month projection based on the running average up until that day for each product.

    Take a look at the sheet attached for more details.

    Thanks guys
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,177

    Re: Month to Date Calculation - Driving Me Crazy!

    If I understand it correctly, in Y2 enter =[@[Total Profit/Loss]]/DAY(DATE(YEAR($B$2),MONTH($B$2)+1,1)-1)

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

    Re: Month to Date Calculation - Driving Me Crazy!

    If your data varies as wildly as your example, there isn't much value in doing a projection.

    Please Login or Register  to view this content.
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Registered User
    Join Date
    10-13-2010
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    59

    Re: Month to Date Calculation - Driving Me Crazy!

    Almost works but

    right now its taking the sum of the total Profit/Loss for that day and dividing it by 31 days. My mistake for not explaining it clearly.

    where I would like it to be running average for up until that day and multiplied by the number of days for that month to project the monthly income.

    See sheet for clearer explanation
    Attached Files Attached Files

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

    Re: Month to Date Calculation - Driving Me Crazy!

    Did you notice that my results are the same as yours?

  6. #6
    Registered User
    Join Date
    10-13-2010
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    59

    Re: Month to Date Calculation - Driving Me Crazy!

    Sorry didn't even see your formula. Cant seem to get it to work. There are multiple products in column A by the way, so I need to separate it by product as well.

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

    Re: Month to Date Calculation - Driving Me Crazy!

    Sort by product.

  8. #8
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Month to Date Calculation - Driving Me Crazy!

    Not sure about the results in Y2 and Y3, shouldn't they be higher? Try this formula in Y2 copied down

    =AVERAGEIFS(X$2:X2,A$2:A2,A2,B$2:B2,">"&EOMONTH(B2,-1),B$2:B2,"<"&EOMONTH(B2,0)+1)*DAY(EOMONTH(B2,0))
    Audere est facere

+ 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