Month to Date Calculation - Driving Me Crazy!

**Peeekay** · 03-29-2012, 03:30 PM

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

**JieJenn** · 03-29-2012, 03:36 PM

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

**shg** · 03-29-2012, 05:11 PM

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.

**Peeekay** · 03-29-2012, 08:38 PM

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

**shg** · 03-30-2012, 09:22 AM

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

**Peeekay** · 03-30-2012, 02:53 PM

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.

**shg** · 03-30-2012, 03:21 PM

Sort by product.

**daddylonglegs** · 03-30-2012, 03:26 PM

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