Placing an amount invested on a certain day in another cell/column after a certain time

[mike4777]

After an initial investment is made (M7) and it accrues interest to a certain amount (T9 table) you can re-invest it. Different amounts are held for different times until it is then released. I need the amounts released to show on the certain day they are released.

I included a blue table in the worksheet (sheet 1010) that explains more specifically. I'm pretty sure the formula should be sumproduct and offset, but I'm not entirely sure. Any help would be appreciated.

https://drive.google.com/file/d/1qy-IeFSA8p_JjF_OLnJbZRDzZ-tFIzXD/view?usp=sharing

 

[StephenCrump]

C3: =SUMPRODUCT(B$2:B3,--(LOOKUP(B$2:B3,E$3:E$6,F$3:F$6)+A$2:A3=A3))

Excel 2010
	A	B	C	D	E	F
1	Day	Amount	Release
2	1				Amount	Release Days
3	2	7,000	0		0	10
4	3	12,000	0		1,000	7
5	4		0		5,000	3
6	5	500	19,000		10,000	2
7	6		0
8	7		0
9	8	1,200	0
10	9		0
11	10	7,000	0
12	11		0
13	12		0
14	13		7,000
15	14		0
16	15		1,700
17	16		0
18	17		0
Sheet1

 

[mike4777]

C3: =SUMPRODUCT(B$2:B3,--(LOOKUP(B$2:B3,E$3:E$6,F$3:F$6)+A$2:A3=A3))

Excel 2010

	A	B	C	D	E	F

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Day[/TD]
[TD="align: right"]Amount[/TD]
[TD="align: right"]Release[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]Amount[/TD]
[TD="align: right"]Release Days[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: right"]7,000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]3[/TD]
[TD="align: right"]12,000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1,000[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5,000[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]5[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]19,000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10,000[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]8[/TD]
[TD="align: right"]1,200[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]10[/TD]
[TD="align: right"]7,000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7,000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1,700[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

thank you for responding. Unfortunately this formula is useless for me because it cannot be adapted into my worksheet (1010) because I don't think you had in mind what tables I was trying to link. If you read the blue box with the instructions in Z12 you will see what I'm trying to do. I posted the link so you could get an idea how to link the charts I referred to. Could I convince you to adapt the formula to the worksheet?

 

[StephenCrump]

Unfortunately this formula is useless for me because it cannot be adapted into my worksheet (1010) because I don't think you had in mind what tables I was trying to link. If you read the blue box with the instructions in Z12 you will see what I'm trying to do. I posted the link so you could get an idea how to link the charts I referred to. Could I convince you to adapt the formula to the worksheet?

I have followed your instructions exactly, and provided a simplified example, e.g. to to better illustrate the calculation, I have used relatively short capital release periods of 10, 7, 3 and 2 days vs your periods of up to 299 days. Based on your description and my table, the $7,000 on day 2 will be released 3 days later, i.e. on day 5. The $12,000 on day 3 will be released 2 days later, i.e. also on day 5.

If this is correct, all you need do is change the cell references, e.g. my lookup table is E3:F6, whereas yours is T9:U12

 

[mike4777]

I have followed your instructions exactly, and provided a simplified example, e.g. to to better illustrate the calculation, I have used relatively short capital release periods of 10, 7, 3 and 2 days vs your periods of up to 299 days. Based on your description and my table, the $7,000 on day 2 will be released 3 days later, i.e. on day 5. The $12,000 on day 3 will be released 2 days later, i.e. also on day 5.

If this is correct, all you need do is change the cell references, e.g. my lookup table is E3:F6, whereas yours is T9:U12

I think this is what you meant, right? =SUMPRODUCT($Q$8:$Q$19,--(LOOKUP($Q$8:$Q$19,$T$9:$U$12,$U$9:$U$12)+$P$8:$P$19=B38)) Seems to be working, verifying now

 

[mike4777]

it works. Thank you. I'm not very familiar with that stacking of formulas.