Summing Data based on a count of prior month data

[pdbose]

Hey Everyone!

Been following this site for a while now and finally posting something because I have spent the better part of all day today working on this and am complete stumped on how to solve for this one. Hoping someone can help! Screenshot below. I'm building out a forecasting model and I'm trying to come up with a formula that compares prior month sales to current sales by looking at the previous month's sales as a percentage of the total # of sales days for the current v previous month. In the data below. February has 23 business days and January has 19 business days. Since there are 2 business days of data for the current month, February (C2), the calculation should solve accordingly.

2 business days/23 business days (February) * 19 business days (January) = 1.65 days
So the formula should calculate $100 for the first business day in January, then 65% of the 2nd business day ($200) for a total of $230

Where things get tricky is this formula needs to adjust based on the selected month in C2 and find the correct columns in Row 11. It then needs to take the business days in Row 7 to run the calculation above, as well as go into an indirect function that allow me to lookup based off the "Remaining BD - xx" items in B12:B34. Essentially, this formula needs to sum everything within the range of "Remaining BD - 18" and Remaining BD - 17" for the January (since there's only 2 business days in February with populated values), while also applying the 65% calculation to the January value coinciding with "Remaining BD - 17". I'm able to create INDEX(MATCH()) functions and INDIRECT() functions that solve many of the problems individually but I'm unable to tie them all together without it breaking.

 

[KRice]

I have some questions. You mention this is a "fore"casting model, yet your example calculates the first two days of a prior month, January, based on the first two days of sales in February? Don't you want the model to look ahead, rather than back in time?

this formula needs to sum everything within the range of "Remaining BD - 18" and Remaining BD - 17" for the January (since there's only 2 business days in February with populated values)

This wording suggests that the Sales numbers shown are daily sales numbers, not cumulative (i.e., not a running sum). Is this correct? Each cell with a sales number represents either actual or projected sales for that day only?

2 business days/23 business days (February) * 19 business days (January) = 1.65 days
So the formula should calculate $100 for the first business day in January, then 65% of the 2nd business day ($200) for a total of $230

Regarding this description, it appears that you want to generate a weighting factor based on the ratio of business days in one month relative to a baseline month (February in this case). But in your example, why is day 2 weighted at 65% when day 1 is not weighted at all? If I follow your description (and I have 23 business days of actual date for February...more on this below), then the first statement here would result in 23/23*19=19 days. I'm not sure what this is supposed to represent. What is the underlying assumption of this model? Is it that sales per month are expected to be the same, or sales per day? What is it about sales/day that would change depending on the number of days in the month?

Are the number of business days per month (e.g., 19 in January) based on a real schedule? If I use NETWORKDAYS.INTL for January and specify weekends as Saturday/Sunday non-work days, and then allow for two holidays, I get 21 work days. How was 19 determined?

I think the table structure does not lend itself well to the model described. Have you considered making the leftmost column be the Business Work Day number...so 1, 2, 3, etc. rather than counting down BD-22, BD-21, etc.? The advantage is that all months have BD 1, BD 2, etc. and since you want a forecast month's days to be based on the same days in a baseline month, all of the relevant "day" cells would then be on the same row, rather than potentially being offset from each other on different rows.

Could you clarify what you want to do, and offer a table that shows expected values?

 

[pdbose]

Thanks for the response! The model is a forecasting tool. However, this is for part of the tool that generates a snapshot of the actual sales performance for the current month. In this case, current actual sales vs prior month sales.

This wording suggests that the Sales numbers shown are daily sales numbers, not cumulative (i.e., not a running sum). Is this correct? Each cell with a sales number represents either actual or projected sales for that day only?

Yes, this is showing daily sales, not cumulative

Regarding this description, it appears that you want to generate a weighting factor based on the ratio of business days in one month relative to a baseline month (February in this case). But in your example, why is day 2 weighted at 65% when day 1 is not weighted at all? If I follow your description (and I have 23 business days of actual date for February...more on this below), then the first statement here would result in 23/23*19=19 days. I'm not sure what this is supposed to represent. What is the underlying assumption of this model? Is it that sales per month are expected to be the same, or sales per day? What is it about sales/day that would change depending on the number of days in the month?

Are the number of business days per month (e.g., 19 in January) based on a real schedule? If I use NETWORKDAYS.INTL for January and specify weekends as Saturday/Sunday non-work days, and then allow for two holidays, I get 21 work days. How was 19 determined?

For simplicity's sake, I'm going to say to ignore the business days piece of this and the weighted factor piece. It overcomplicates things. BTW, I calculated 19 business days by also removing the two company holidays that we recognize.

I realize my original post may have been unclear. The main challenge I'm facing is I need a formula that will count the numbers of days in February with reported sales, then sum the corresponding number of days of sales in January using that count. If February has 2 days of reported sales, the formula would look for the first populated value between rows 12 and 34 for January and then sum the correct numbers of days worth of sales based on that previous month count. Let me know if that clarifies things a bit and really appreciate your help!

 

[pdbose]

@KRice after doing some digging, I was able to put together this template formula which essentially calculates what I'm trying to solve.

=SUM(INDEX($C$12:$N$12,MATCH(DATE(YEAR($C$2),MONTH($C$2)-1,1),$C$11:$N$11,0)):INDEX(C12:C34,COUNTA(D12:D34)+(D7-C7),1))

However, the problem with this is that I have to find some way to get the part of the formula in red to adjust based on the value entered in C2. Because it is already using an index function, I can't use INDEX(MATCH()) functions to find C12:C34, D12:D34, D7 or C7.

 

[KRice]

Thanks, that clarifies some questions, but I'm still not sure about several things. You are inputting data for February and then attempting to calculate values for the prior month, so why are you summing down the prior month (January) column in your last post? (see question below)

As for your last paragraph, if you only want the count of business days in February that have a non-0/non-blank value (let's say that number is n), and then you want the first n business days in January, you could find that readily using some Excel 365 functions. Are there two formula involved?...one to calculate daily sales for each business day in January, and another to sum only the first n business days in the prior month?

 

[KRice]

See the yellow cell for a formula that counts the number of non-empty cells in the column to the right (again, let's call than number n), and then it looks down the range of values in rows 12:34 in that same column, filters out any blanks (which brings the 1st sale value to the top of the array), and then the TAKE function takes only the top n elements in the array and sums them.

In this case, the monthly forecast values are placed in each column using a formula that initially maps their locations...see that formula standing alone as an example in cell E12. That formula inserts blanks as necessary to offset the values, depending on how many business days are in that month. Jumping over to the C12 formula, you'll see that initial skeleton formula inside a MAP function. The MAP function walks along the skeleton array, element by element, and performs some type of calculation to generate a new value for that array location. That calculation is shown to the right in the formula, beginning with IF. This is the part that I do not understand. Right now the formula simply takes the ratios of the number of BDs for the two months and multiplies by the COUNTA results (so we get 1.65)...but I don't understand what you really want here.

MrExcel_20240131_C (version 1).xlsx
	A	B	C	D	E
1
2		Current Month	February
3
4
5
6
7		Business Days	19	23	21
8
9			3.3043478
10
11		Business Work Day	January	February	March
12		Remaining BD - 22		100
13		Remaining BD - 21		200
14		Remaining BD - 20			1
15		Remaining BD - 19			2
16		Remaining BD - 18	1.6521739		3
17		Remaining BD - 17	1.6521739		4
18		Remaining BD - 16	1.6521739		5
19		Remaining BD - 15	1.6521739		6
20		Remaining BD - 14	1.6521739		7
21		Remaining BD - 13	1.6521739		8
22		Remaining BD - 12	1.6521739		9
23		Remaining BD - 11	1.6521739		10
24		Remaining BD - 10	1.6521739		11
25		Remaining BD - 9	1.6521739		12
26		Remaining BD - 8	1.6521739		13
27		Remaining BD - 7	1.6521739		14
28		Remaining BD - 6	1.6521739		15
29		Remaining BD - 5	1.6521739		16
30		Remaining BD - 4	1.6521739		17
31		Remaining BD - 3	1.6521739		18
32		Remaining BD - 2	1.6521739		19
33		Remaining BD - 1	1.6521739		20
34		Remaining BD - 0	1.6521739		21
Forecast

Cell Formulas
Range	Formula
C7	C7	=NETWORKDAYS.INTL(C11,EOMONTH(C11,0),1,$Q$2:$Q$15)
C9	C9	=SUM(TAKE(FILTER(C$12:C$34,C$12:C$34<>""),COUNTA(D$12:D$34)))
B12:B34	B12	="Remaining BD - "&SEQUENCE(23,,22,-1)
C12:C34	C12	=MAP(IF(C$7<23,VSTACK(EXPAND("",23-C$7,,""),SEQUENCE(C$7)),SEQUENCE(C$7)),LAMBDA(a,IF(a="","",C$7/D$7*COUNTA(D$12:D$34))))
E12:E34	E12	=IF(E$7<23,VSTACK(EXPAND("",23-E$7,,""),SEQUENCE(E$7)),SEQUENCE(E$7))
Dynamic array formulas.

Here is what prompted me to ask about the number of work days in the month. You can maintain a table of recognized holidays (national, local, company-only...doesn't matter) and that list will be ignored. And if the particular days of the week are not worked, that can be specified in the NETWORKDAYS.INTL formula. Here is a notional holiday reference table:

MrExcel_20240131_C (version 1).xlsx
	Q	R	S
1	Days Off
2	1/1/2024	Monday, January 01	New Year’s Day
3	1/10/2024		Company holiday
4	1/15/2024	Monday, January 15	MLK Day
5	1/19/2024		Company holiday
6	2/19/2024	Monday, February 19 *	President’s Birthday
7	5/27/2024	Monday, May 27	Memorial Day
8	7/4/2024	Thursday, July 04	Independence Day
9	9/2/2024	Monday, September 02	Labor Day
10	10/14/2024	Monday, October 14	Indigenous Peoples' Day
11	11/5/2024		Election Day
12	11/28/2024	Thursday, November 28	Thanksgiving Day
13	12/25/2024	Wednesday, December 25	Christmas Day
14	1/1/2025	Wednesday, January 01	New Year’s Day
15	1/20/2025		MLK Day
Forecast

 

[KRice]

2 business days/23 business days (February) * 19 business days (January) = 1.65 days
So the formula should calculate $100 for the first business day in January, then 65% of the 2nd business day ($200) for a total of $230

In post #2, I asked for more detail about expected results. Would you please show a few columns and 5 or 6 rows of results showing what the expected results are, and which values are already present in the table, and where formulas are needed? The image in the original post does not explain these things. The explanation quoted above from the original post is unclear. My initial interpretation was that you wanted a formula to populate values in a prior month while you manually enter data in the current month (whichever month is shown in C2)...but when you advance to the following month (say March), the February column would not have a formula (as it contains the manually entered data). So I suspect this understanding is not correct. My second guess is that rows 12:34 contain data that is manually entered, with no formulas involved...and you want a formula that delivers a single result showing some type of sum based on the prior month's daily sales data, the current month's developing sales data, and the number of days populated with data in the current month.

Generally, it is advisable to use the XL2BB add-in so that you can post a small working example of your worksheet, which helps others understand the problem, where data are found and structured, and where formulas are needed...and this greatly facilitates developing solutions.

 

[pdbose]

Thanks for taking so much time to help out with this! I'll see if I can add the XL2BB add-in. I may have to get permission from my org as they are extremely strict on what we can and can't download on these computers. In the meantime, to help make this a little more clear, here is the "summary" that I present to my teams. Obviously, the values are not real so ignore that none of the percentages make sense.

What we're solving for is circled in red. This is showing how current sales aligns to the Last Month sales to date. Currently, it is using an insanely long formula that I don't want to post because I think it would cause more confusion. However, the issue with this current formula is that, until sales for February hit row 16 (because that's when January started on the sheet), it shows N/A. Once it hits row 16, it shows results like this (900%) because it's comparing the 1st 5 days of February sales to the first day of January sales. For months that have the same business days, this obviously isn't an issue. However, when there's a significant variation, in the case of January and February of this year, it skews values for weeks. This is what I'm ultimately trying to correct.

 

[pdbose]

So far, I have this as my formula and it is giving me the correct sum. However, I still can't figure out how to get the part highlighted in red to dynamically adjust to the value in C2.

=SUM(INDEX($C$12:$N$12,MATCH(DATE(YEAR($C$2),MONTH($C$2)-1,1),$C$11:$N$11,0)):INDEX($C$12:$N$34,COUNTA(D12:D34)+(D7-C7),MATCH(DATE(YEAR($C$2),MONTH($C$2)-1,1),$C$11:$N$11,0)))

 

[pdbose]

I think I may have figure it out!

=SUM(INDEX($C$12:$N$12,MATCH(DATE(YEAR($C$2),MONTH($C$2)-1,1),$C$11:$N$11,0)):INDEX($C$12:$N$34,COUNTA(INDEX($C$12:$N$34,0,MATCH($C$2,$C$11:$N$11,0)))+(INDEX($C$7:$N$7,1,MATCH($C$2,$C$11:$N$11,0))-INDEX($C$7:$N$7,1,MATCH(DATE(YEAR($C$2),MONTH($C$2)-1,1),$C$11:$N$11,0))),MATCH(DATE(YEAR($C$2),MONTH($C$2)-1,1),$C$11:$N$11,0)))


Then, in order to get the comparison to last month, I would use

=(INDEX($C$35:$N$35,1,MATCH($C$2,$C$11:$N$11,0))-SUM(INDEX($C$12:$N$12,MATCH(DATE(YEAR($C$2),MONTH($C$2)-1,1),$C$11:$N$11,0)):INDEX($C$12:$N$34,COUNTA(INDEX($C$12:$N$34,0,MATCH($C$2,$C$11:$N$11,0)))+(INDEX($C$7:$N$7,1,MATCH($C$2,$C$11:$N$11,0))-INDEX($C$7:$N$7,1,MATCH(DATE(YEAR($C$2),MONTH($C$2)-1,1),$C$11:$N$11,0))),MATCH(DATE(YEAR($C$2),MONTH($C$2)-1,1),$C$11:$N$11,0))))/SUM(INDEX($C$12:$N$12,MATCH(DATE(YEAR($C$2),MONTH($C$2)-1,1),$C$11:$N$11,0)):INDEX($C$12:$N$34,COUNTA(INDEX($C$12:$N$34,0,MATCH($C$2,$C$11:$N$11,0)))+(INDEX($C$7:$N$7,1,MATCH($C$2,$C$11:$N$11,0))-INDEX($C$7:$N$7,1,MATCH(DATE(YEAR($C$2),MONTH($C$2)-1,1),$C$11:$N$11,0))),MATCH(DATE(YEAR($C$2),MONTH($C$2)-1,1),$C$11:$N$11,0)))