Complex Formula Value Distribute

[aqeelnokia99]

Hi Experts i try to Distribute Value from Table 1 Until its Reached Value 1000000 if its Reached Write Remaining amount and start from again from Remain amount for Example
if you see Result from Value 1130 to 465001 Total is 979026 to Reach @ 1000000 we need more 1000000-979026 =20974 This amount will come from Table 1 SR # 5 Which is 250000 After That is you see Result Table next value is 229026 which = to 250000-20974 so on in Simple Distribute Value of table 1 Value Until its Reached 1000000 if its Possible with formula or Vba will Very Big Thanks

Table 1				1000000
SR#	Euros		Result
1	1130			1130
2	171361			171361
3	341534			341534
4	465001			465001
5	250000			20974	This Part is Remaing Balance to Reach amount 1000000
6	390854			229026	Start from again from 250000-20974
7	307572			390854
8	710127			307572
9	600000			72548
10	260208			637579
11	500000			362421
12	274438

 

[Sanjeev1976]

Try the following :

Book7 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J
1	Table 1			1,000,000
2	SR#	Euros		Result			Helper1	Helper2	Helper3	Helper4
3	1	1,130		1,130			1,130	1,130		1,130
4	2	171,361		171,361			172,491	171,361
5	3	341,534		341,534			514,025	341,534		171,361
6	4	465,001		465,001			979,026	465,001
7	5	250,000		20,974			1,229,026	20,974	229,026	341,534
8	6	390,854		229,026			390,854	390,854
9	7	307,572		390,854			698,426	307,572		465,001
10	8	710,127		307,572			1,408,553	301,574	408,553
11	9	600,000		301,574			600,000	600,000		20,974
12	10	260,208		408,553			860,208	260,208		229,026
13	11	500,000		600,000			1,360,208	139,792	360,208	390,854
14	12	274,438		260,208			274,438	274,438
15				139,792			274,438	-		307,572
16				360,208			274,438	-
17				274,438			274,438	-		301,574
18							274,438	-		408,553
19							274,438	-		600,000
20							274,438	-
21							274,438	-		260,208
22							274,438	-
23							274,438	-		139,792
24							274,438	-		360,208
25							274,438	-		274,438
26							274,438	-
27							274,438	-		-
28							274,438	-
29							274,438	-		-
30							274,438	-
31							274,438	-		-
Sheet2

Cell Formulas
Range	Formula
G3:G31	G3	=IF(G2>$D$1,B3,G2+B3)
H3	H3	=IF(G3>$D$1,IF($D$1*COUNTIF($G$3:G3,">"&$D$1)-SUM($D2:D$3)=0,B3,$D$1*COUNTIF($G$3:G3,">"&$D$1)-SUM($D2:D$3)),B3)
I3:I31	I3	=IF($D$1*COUNTIF($G$3:G3,">"&$D$1)-SUM($H$3:H3)=0,B3-H3,"")
J3:J31	J3	=IFERROR(INDEX(Data,1+INT((ROW(A1)-1)/COLUMNS(Data)),MOD(ROW(A1)-1+COLUMNS(Data),COLUMNS(Data))+1),"")
H4:H31	H4	=IF(G4>$D$1,IF($D$1*COUNTIF($G$3:G4,">"&$D$1)-SUM($H$3:H3)=0,B4,$D$1*COUNTIF($G$3:G4,">"&$D$1)-SUM($H$3:H3)),B4)
D3:D17	D3	=IFERROR(INDEX(J:J,SMALL(IF(J$3:J$1000<>"",ROW(J$3:J$1000)), ROWS(J$3:J3))), "")

 

[aqeelnokia99]

Amazing it was very complex Task Big Thanks to Crack it

 

[aqeelnokia99]

Helper Column 4 not working What is range of "Data" in formula

 

[aqeelnokia99]

i found 1 issue after result 307572 Row #10 Next Result must 72548 because to Reach on 1000000 we need more 72548

1000000	1130
	171361
	341534
	465001
	20974
1000000	229026
	390854
	307572
	72548
1000000	637579
	362421

 

[Sanjeev1976]

Helper Column 4 not working What is range of "Data" in formula

$H$3:$I$31

 

[Sanjeev1976]

Try the updated version :

Book7 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J
1	Table 1			1,000,000
2	SR#	Euros		Result			Helper1	Helper2	Helper3	Helper4
3	1	1,130		1,130			1,130	1,130		1,130
4	2	171,361		171,361			172,491	171,361
5	3	341,534		341,534			514,025	341,534		171,361
6	4	465,001		465,001			979,026	465,001
7	5	250,000		20,974			1,229,026	20,974	229,026	341,534
8	6	390,854		229,026			619,880	390,854
9	7	307,572		390,854			927,452	307,572		465,001
10	8	710,127		307,572			1,637,579	72,548	637,579
11	9	600,000		72,548			1,237,579	362,421	237,579	20,974
12	10	260,208		637,579			497,787	260,208		229,026
13	11	500,000		362,421			997,787	500,000		390,854
14	12	274,438		237,579			1,272,225	2,213	272,225
15				260,208			272,225	-		307,572
16				500,000			272,225	-
17				2,213			272,225	-		72,548
18				272,225			272,225	-		637,579
19				-			272,225	-		362,421
20				-			272,225	-		237,579
21							272,225	-		260,208
22							272,225	-
23							272,225	-		500,000
24							272,225	-
25							272,225	-		2,213
26							272,225	-		272,225
27							272,225	-		-
28							272,225	-
29							272,225	-		-
30							272,225	-
31							272,225	-		-
Sheet2

Cell Formulas
Range	Formula
G3:G7	G3	=IF(G2>$D$1,B3,G2+B3)
H3	H3	=IF(G3>$D$1,IF($D$1*COUNTIF($G$3:G3,">"&$D$1)-SUM($H2:I$3)=0,B3,$D$1*COUNTIF($G$3:G3,">"&$D$1)-SUM($H2:I$3)),B3)
I3	I3	=IF($D$1*COUNTIF($G$3:G3,">"&$D$1)-SUM($H$3:H3)=0,B3-H3,"")
J3:J31	J3	=IFERROR(INDEX(Data,1+INT((ROW(A1)-1)/COLUMNS(Data)),MOD(ROW(A1)-1+COLUMNS(Data),COLUMNS(Data))+1),"")
H4:H31	H4	=IF(G4>$D$1,IF($D$1*COUNTIF($G$3:G4,">"&$D$1)-SUM($H$3:I3)=0,B4,$D$1*COUNTIF($G$3:G4,">"&$D$1)-SUM($H$3:I3)),B4)
I4:I31	I4	=IF($D$1*COUNTIF($G$3:G4,">"&$D$1)-SUM($H$3:H4)-SUM($I$3:I3)=0,B4-H4,"")
G8:G31	G8	=IF(G7>$D$1,B8+I7,G7+B8)
D3:D20	D3	=IFERROR(INDEX(J:J,SMALL(IF(J$3:J$1000<>"",ROW(J$3:J$1000)), ROWS(J$3:J3))), "")

Data refers to Named range = $H$3:$I$31

 

[aqeelnokia99]

Awesome Its working Good Thanks a Lot

 

[aqeelnokia99]

i change Value its give negative Result for some reason

					Apr-23	1000000
SR#	Date	Euros	Exchange Rate	Balance Euros		Result			Helper1	Helper2	Helper3	Helper4
1	08-Nov-22	200000	224			200000		Apr-23	200000	200000		200000
2	31-Jan-23	171361	288			171361		Apr-23	371361	171361
3	31-Jan-23	341534	288			341534		Apr-23	712895	341534		171361
4	09-Feb-23	465001	290.11		1	287105		Apr-23	1177896	287105	177896
5	13-Feb-23	250000	280	€ 229,026		177896		May-23	250000	250000		341534
6	13-Feb-23	390854	288			250000		May-23	640854	390854
7	21-Feb-23	307572	279			390854		May-23	948426	307572		287105
8	27-Feb-23	710127	274	€ 637,579		307572		May-23	1658553	-126322	836449	177896
9	09-Mar-23	600000	297.7	€ 237,579	1	-126322		May-23	1436449	163551	436449	250000
10	13-Mar-23	260208	302.2			836449		Jun-23	696657	260208
11	21-Mar-23	500000	302		1	163551		Jun-23	1196657	303343	196657	390854
12	28-Mar-23	274438	304.81	€ 272,225		436449		Jul-23	471095	274438
						260208		Jul-23	471095	0		307572
					1	303343		Jul-23	471095	0
						196657		Aug-23	471095	0		-126322
						274438		Aug-23	471095	0		836449
						0		Aug-23	471095	0		163551
						0		Aug-23	471095	0		436449
						0		Aug-23	471095	0		260208
					#NUM!	#NUM!		#NUM!	471095	0
					#NUM!	#NUM!		#NUM!	471095	0		303343
					#NUM!	#NUM!		#NUM!	471095	0		196657
									471095	0		274438
									471095	0
									471095	0		0
									471095	0
									471095	0		0
									471095	0
									471095	0		0

 

[Sanjeev1976]

Hopefully now you should not have any problem :

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M
1						01/04/2023	1,000,000
2	SR#	Date	Euros	Exchange Rate	Balance Euros		Result			Helper1	Helper2	Helper3	Helper4
3	1	08/11/2022	200,000	223.94			200,000		Apr-23	200,000	200,000		200,000
4	2	31/01/2023	171,361	288.29			171,361		Apr-23	371,361	171,361
5	3	31/01/2023	341,534	288.29			341,534		Apr-23	712,895	341,534		171,361
6	4	09/02/2023	465,001	290.11	€ 177,896		287,105		Apr-23	1,177,896	287,105	177,896
7	5	13/02/2023	250,000	279.98			177,896		May-23	427,896	250,000		341,534
8	6	13/02/2023	390,854	288.36			250,000		May-23	818,750	390,854
9	7	21/02/2023	307,572	279.43	€ 126,322		390,854		May-23	1,126,322	181,250	126,322	287,105
10	8	27/02/2023	710,127	274.01			181,250		May-23	836,449	710,127		177,896
11	9	09/03/2023	600,000	297.7	€ 436,449		126,322		May-23	1,436,449	163,551	436,449	250,000
12	10	13/03/2023	260,208	302.2			710,127		Jun-23	696,657	260,208
13	11	21/03/2023	500,000	302	€ 196,657		163,551		Jun-23	1,196,657	303,343	196,657	390,854
14	12	28/03/2023	274,438	304.81			436,449		Jul-23	471,095	274,438
15							260,208		Jul-23	471,095	-		181,250
16							303,343		Jul-23	471,095	-		126,322
17							196,657		Aug-23	471,095	-		710,127
18							274,438		Aug-23	471,095	-
19							-		Aug-23	471,095	-		163,551
20							-		Aug-23	471,095	-		436,449
21							-		Aug-23	471,095	-		260,208
22										471,095	-
23										471,095	-		303,343
24										471,095	-		196,657
25										471,095	-		274,438
26										471,095	-
27										471,095	-		-
28										471,095	-
29										471,095	-		-
30										471,095	-
31										471,095	-		-
Sheet1

Cell Formulas
Range	Formula
J3	J3	=IF(J2>$G$1,C3,J2+C3)
K3	K3	=IF(J3>$G$1,IF($G$1*COUNTIF($J$3:J3,">"&$G$1)-SUM($K2:L$3)=0,C3,$G$1*COUNTIF($J$3:J3,">"&$G$1)-SUM($K2:L$3)),C3)
L3	L3	=IF($G$1*COUNTIF($J$3:J3,">"&$G$1)-SUM($K$3:K3)=0,C3-K3,"")
M3:M31	M3	=IFERROR(INDEX($K$3:$L$31,1+INT((ROW(A1)-1)/2),MOD(ROW(A1)-1+2,2)+1),"")
J4:J31	J4	=IF(J3>$G$1,C4+L3,J3+C4)
K4:K31	K4	=IF(J4>$G$1,IF($G$1*COUNTIF($J$3:J4,">"&$G$1)-SUM($K$3:L3)=0,C4,$G$1*COUNTIF($J$3:J4,">"&$G$1)-SUM($K$3:L3)),C4)
L4:L31	L4	=IF($G$1*COUNTIF($J$3:J4,">"&$G$1)-SUM($K$3:K4)-SUM($L$3:L3)=0,C4-K4,"")
E3:E14	E3	=L3
G3:G24	G3	=IFERROR(INDEX(M:M,SMALL(IF(M$3:M$1000<>"",ROW(M$3:M$1000)), ROWS(M$3:M3))), "")