Need a formula to calculate Monthly amount if every couple of months Amt is increased

[Kiriko]

Hopefully this is as easy as the title makes it seem:

I have the below data:

Month	Month Number	Total Cost
Mar-19	3.00	71,250.00
Mar-19	3.00	71,250.00
Mar-19	3.00	41,250.00
Mar-19	3.00	41,250.00
Jun-19	6.00	16,988.00
Mar-19	3.00	13,904.21
Oct-19	10.00	3,089.82
Aug-19	8.00	7,506.33
Mar-19	3.00	78,750.00
Mar-19	3.00	78,750.00
Mar-19	3.00	30,000.00
Mar-19	3.00	30,000.00
Oct-19	10.00	8,269.17
Oct-19	10.00	8,269.17
Jun-19	6.00	16,433.69
Jun-19	6.00	35,444.25
Jun-19	6.00	16,210.33
Jun-19	6.00	26,746.78
Mar-19	3.00	76,301.25
Mar-19	3.00	71,250.00
Mar-19	3.00	53,166.38
Mar-19	3.00	40,120.16
Mar-19	3.00	24,650.54
Mar-19	3.00	24,650.54
Total		885,500.59

<tbody>
</tbody>

Starting Month	Month Number	Total Cost to start in:	NETWORKING DAYS
March	3	746,543.06	218
June	6	111,823.04	152
August	8	7,506.33	109
October	10	19,628.16	66
Total		885,500.59

<tbody>
</tbody>

I need to do the below calculations:

Calculation:	Month	NetWorking Days_19	Monthly Cost	Monthly Cost	Monthly Cost	Monthly Cost	Total Montly Cost 2019
	Start in Month		Mar-19	Jun-19	Aug-19	Oct-19
	Total Cost for the Year		746,543.06	111,823.04	7,506.33	19,628.16
	NET WORKING DAYS	261	218	152.00	109.00	66.00
0	Jan-19	23					-
0	Feb-19	20					-
(746/218)*NWD in Month	Mar-19	21	71,914.70				71,914.70
(746/218)*NWD in Month	Apr-19	22	75,339.21				75,339.21
(746/218)*NWD in Month	May-19	23	78,763.72				78,763.72
(746/218)+(111/152)*NWD in Month	Jun-19	20	68,490.19	14,713.56			83,203.75
(746/218)+(111/152)*NWD in Month	Jul-19	23	78,763.72	16,920.59			95,684.31
(746/218)+(111/152)+(7/109)*NWD in Month	Aug-19	22	75,339.21	16,184.91	1,515.04		93,039.16
(746/218)+(111/152)+(7/109)*NWD in Month	Sep-19	21	71,914.70	15,449.24	1,446.17		88,810.11
(746/218)+(111/152)+(7/109)+(19/66)*NWD in Month	Oct-19	23	78,763.72	16,920.59	1,583.91	6,840.12	104,108.33
(746/218)+(111/152)+(7/109)+(19/66)*NWD in Month	Nov-19	21	71,914.70	15,449.24	1,446.17	6,245.32	95,055.43
(746/218)+(111/152)+(7/109)+(19/66)*NWD in Month	Dec-19	22	75,339.21	16,184.91	1,515.04	6,542.72	99,581.88
			746,543.06	111,823.04	7,506.33	19,628.16	885,500.59

<tbody>
</tbody>

To get the result in the below format:

Month 2019	Jan-19	Feb-19	Mar-19	Apr-19	May-19	Jun-19	Jul-19	Aug-19	Sep-19	Oct-19	Nov-19	Dec-19
NETWORKING DAYS	23	20	21	22	23	20	23	22	21	23	21	22		Check
Total Cost	-	-	71,914.70	75,339.21	78,763.72	83,203.75	95,684.31	93,039.16	88,810.11	104,108.33	95,055.43	99,581.88		885,500.59
Thank you to anyone who can and tries to help

<tbody>
</tbody>

 

[RasGhul]

Hi Kiriko,

Maybe you could use this, note that my figures reflect only the first table of data, but it looks like it achieves the right format?

Copy the formulas across your table, note that the SUM($B$29:B$29) formula expands to add each Months Costs as it goes across. Also the Dates in the Months row are the 1st of each month just formatted as "mmm-yy".

You could also 2019 Public Holidays in the [Holidays]criteria in =NETWORKDAYS(B$30,EOMONTH(B$30,0),[holidays]) as a range.

Book1
	A	B	C
1	Month	Month Number	Total Cost
2	Mar-19	3	71,250.00
3	Mar-19	3	71,250.00
4	Mar-19	3	41,250.00
5	Mar-19	3	41,250.00
6	Jun-19	6	16,988.00
7	Mar-19	3	13,904.21
8	Oct-19	10	3,089.82
9	Aug-19	8	7,506.33
10	Mar-19	3	78,750.00
11	Mar-19	3	78,750.00
12	Mar-19	3	30,000.00
13	Mar-19	3	30,000.00
14	Oct-19	10	8,269.17
15	Oct-19	10	8,269.17
16	Jun-19	6	16,433.69
17	Jun-19	6	35,444.25
18	Jun-19	6	16,210.33
19	Jun-19	6	26,746.78
20	Mar-19	3	76,301.25
21	Mar-19	3	71,250.00
22	Mar-19	3	53,166.38
23	Mar-19	3	40,120.16
24	Mar-19	3	24,650.54
25	Mar-19	3	24,650.54
Sheet1

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
27	NETWORK MONTH	23	20	21	22	23	20	23	22	21	23	21	22
28	NETWORK YR	261	238	218	197	175	152	132	109	87	66	43	22
29	Monthly Costs			71,914.70			14,713.56		1,515.04		6,840.12
30	Months	01-01-19	Feb-19	Mar-19	Apr-19	May-19	Jun-19	Jul-19	Aug-19	Sep-19	Oct-19	Nov-19	Dec-19	Check
31	Total Costs			71,914.70	71,914.70	71,914.70	86,628.26	86,628.26	88,143.30	88,143.30	94,983.42	94,983.42	94,983.42	850,237.46
Sheet1

Cell Formulas
Range	Formula
B27	=NETWORKDAYS(B$30,EOMONTH(B$30,0))
B28	=NETWORKDAYS(B$30,EOMONTH($M$30,0))
B29	=B$27*(SUMIFS($C$2:$C$25,$A$2:$A$25,">="&B$30,$A$2:$A$25,"<="&EOMONTH(B$30,0))/B$28)
B31	=SUM($B$29:B$29)
C30	=EOMONTH(B30,0)+1
N31	=SUM($B$31:$M$31)

 

[Kiriko]

Thanks RasGhul, your suggestion was easy to follow, however, I still need to calculate by the Networking Days per month. Is there a way to get excel to recognize when there is change to the monthly cost and then do the divisions by Network yr and add that to the original monthly total amount.....

 

[RasGhul]

Ok so my formula is doing the following for the March figure;

NetworkDays(MARCH(21))*SUMIFS(March Total Cost(746,543.08))/NetworkDays(MARCH-DECEMBER 31ST(218))=71,914.70

Is this syntax correct?

 

[Kiriko]

Yes, it works for the months that are the starting month, but the months in between become the problem. I'm sorry I don't really speak excel language and I struggle to put thoughts into comprehensive sentences (see title of thread as example). I want a formula to work out that from march till dec it's 746,543.08/218 and multiply that by Networking days in each month to get the monthly total. But then in June, it somehow should pick up that it should be (746,543.08/218 + 111,823.04/152) and multiply it by net working days for each month.

I tried to use IF: If current month is greater than previous month, return current month (SUMPRODUCT for anything equal to or greater than the date which should give for e.g 746,543.08 in March), if it's less return the difference. But then I couldn't figure out how to keep the previous months result and add it with the difference (Note the difference has to be divided by a different total of Networking days for the year....) anyway, I accidently deleted the data so I can't show my work and I can't recreate it......

 

[RasGhul]

Got it with good ol' IF,

Copy each of the formulas across, except for the last sum formula which is just the "Check" total;

Here is the link to my mock up if you needed a copy of the data;

https://docs.google.com/spreadsheets/d/1Ym53CVT9lmRxlrPkrA7wbF-W8YFXYQeIA1a608qoHg4/edit?usp=sharing

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
27	Months	01-01-19	Feb-19	Mar-19	Apr-19	May-19	Jun-19	Jul-19	Aug-19	Sep-19	Oct-19	Nov-19	Dec-19	Check
28	NETWORK MONTH	23	20	21	22	23	20	23	22	21	23	21	22
29	NETWORK YR	261	238	218	197	175	152	132	109	87	66	43	22
30	Monthly Costs			746,543.08			111,823.05		7,506.33		19,628.16
31				3,424.51	3,424.51	3,424.51	4,160.19	4,160.19	4,229.05	4,229.05	4,526.45	4,526.45	4,526.45	Check
32	Total Cost			71,914.70	75,339.21	78,763.72	83,203.75	95,684.31	93,039.16	88,810.11	104,108.33	95,055.43	99,581.88	885,500.62
Sheet1

Cell Formulas
Range	Formula
C27	=EOMONTH(B27,0)+1
B28	=NETWORKDAYS(B$27,EOMONTH(B$27,0))
B29	=NETWORKDAYS(B$27,EOMONTH($M$27,0))
B30	=IF(SUMIFS($C$2:$C$25,$A$2:$A$25,">="&B$27,$A$2:$A$25,"<="&EOMONTH(B$27,0))=0,"",SUMIFS($C$2:$C$25,$A$2:$A$25,">="&B$27,$A$2:$A$25,"<="&EOMONTH(B$27,0)))
B31	=IF(ISNUMBER(B30),(B30/B29)+A31,A31)
B32	=B31*B28
N32	=SUM(B32:M32)

 

[Kiriko]

Yes thank you this works perfectly!! Thank you so much