# Month to Date Calculation - Driving Me Crazy!

1. ## 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

2. ## 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. ## 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.``

4. ## 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

5. ## Re: Month to Date Calculation - Driving Me Crazy!

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

6. ## 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. ## Re: Month to Date Calculation - Driving Me Crazy!

Sort by product.

8. ## 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))

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

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