Formula to Cap Funding

[smartpat19]

Hi Mr. Excel Gurus,

I have a series of payments that are being paid for using two sources. One is being used first Up to $2,000,000. and then everything else will be using a different source with unlimited funds (theoretically).

How can I build the formula to be sure the amount will not exceed 6,000,000 and the additional will go to the second source.

I've attached a photo of the simple spreadsheet with the highlighted cell.

For source of funding 2, I'm just taking the difference

Thank you,
Patrick

 

[JamesCanale]

MrExcelPlayground6.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
3	$ 6,000,000.00
4			1/1/2022	2/1/2022	3/1/2022	4/1/2022	5/1/2022	6/1/2022	7/1/2022	8/1/2022	9/1/2022	10/1/2022	11/1/2022	12/1/2022	1/1/2023	2/1/2023	3/1/2023	4/1/2023	5/1/2023	6/1/2023	7/1/2023	8/1/2023	9/1/2023
5		New Charges	$625,000.00	$625,000.00	$625,000.00	$625,000.00	$625,000.00	$625,000.00	$625,000.00	$625,000.00	$625,000.00	$625,000.00	$625,000.00	$625,000.00	$625,000.00	$625,000.00	$625,000.00	$625,000.00	$625,000.00	$625,000.00	$625,000.00	$625,000.00	$625,000.00	$13,125,000.00
6		Source 1	$625,000.00	$625,000.00	$625,000.00	$625,000.00	$625,000.00	$625,000.00	$625,000.00	$625,000.00	$625,000.00	$375,000.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$6,000,000.00
7		Source 2	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$250,000.00	$625,000.00	$625,000.00	$625,000.00	$625,000.00	$625,000.00	$625,000.00	$625,000.00	$625,000.00	$625,000.00	$625,000.00	$625,000.00	$7,125,000.00
8		Total	$625,000.00	$625,000.00	$625,000.00	$625,000.00	$625,000.00	$625,000.00	$625,000.00	$625,000.00	$625,000.00	$625,000.00	$625,000.00	$625,000.00	$625,000.00	$625,000.00	$625,000.00	$625,000.00	$625,000.00	$625,000.00	$625,000.00	$625,000.00	$625,000.00	$13,125,000.00
Sheet30

Cell Formulas
Range	Formula
D4:W4	D4	=EDATE(C4,1)
X5:X8	X5	=SUM(C5:W5)
C6	C6	=IF(C5<A3,C5,A3)
D6:W6	D6	=IF(SUM($C6:C6)+D5<$A$3,D5,MAX(0,$A$3-SUM($C6:C6)))
C7:W7	C7	=C5-C6
C8:W8	C8	=SUM(C6:C7)