Create cash flow from list of regular payments due

[El Rebelde]

I have a spreadsheet with rows showing payments and the day in the month that they are due. There are also columns showing the weeks of the year, identified by their start dates. I would like to add the payment due for that row in any week where the date due falls between the start date and end date for that week.

The file attached has been populated manually to show what I'm trying to achieve. Can anyone help, please?

 

[hagia_sofia]

Hello, If I understand it correctly then maybe:

=DROP(REDUCE("",G1:K1,LAMBDA(a,b,HSTACK(a,IF(BYROW(--(C2:C14=DAY(SEQUENCE(,7,b))),LAMBDA(x,SUM(x))),E2:E14,"")))),,1)

 

[El Rebelde]

Thank you, but what are the elements a, b and x? The trouble is that I've no knowledge or experience of these functions to work out why I get a #SPILL error!!

 

[hagia_sofia]

Hello, a, b, x are custom variable names which represent every element of an array (e.g. x stands for each row that is to be summed). #SPILL error probably results from the fact that there are some data within the range which prevent the formula from providing the result - you could either delete the data or copy the inputs elsewhere to check what the formula returns. Or if you would prefer a formula that returns results for each cell individually it is possible to adjust the formula...

 

[El Rebelde]

Thanks for your help but I couldn't make that work. Having rested my brain over the weekend I've come up with a formula that I understand and that works! It looks for the day that the payment is due within a string made up of the DAY() values of the week start date and the following six days. If found it returns the payment value, otherwise a blank. (The slashes are to prevent single digits being found in double digit DAY() values.)

=IF(ISERROR(FIND(("/"&TEXT($C9,0)&"/"),"/"&DAY(I$1)&"/"&DAY(I$1+1)&"/"&DAY(I$1+2)&"/"&DAY(I$1+3)&"/"&DAY(I$1+4)&"/"&DAY(I$1+5)&"/"&DAY(I$1+6))&"/"),"",$E9)

 

[HighAndWilder]

Thanks for your help but I couldn't make that work. Having rested my brain over the weekend I've come up with a formula that I understand and that works! It looks for the day that the payment is due within a string made up of the DAY() values of the week start date and the following six days. If found it returns the payment value, otherwise a blank. (The slashes are to prevent single digits being found in double digit DAY() values.)

=IF(ISERROR(FIND(("/"&TEXT($C9,0)&"/"),"/"&DAY(I$1)&"/"&DAY(I$1+1)&"/"&DAY(I$1+2)&"/"&DAY(I$1+3)&"/"&DAY(I$1+4)&"/"&DAY(I$1+5)&"/"&DAY(I$1+6))&"/"),"",$E9)

To avoid the string of days I have used SEQUENCE and MATCH.

Copy the formula from G2 along and down.

Blue cells contain formula so that F9 will generate random data for testing,

Create cash flow from list of regular payments due.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1		Name	Day Of Month	Last Payment	Value		01/01/2024	08/01/2024	15/01/2024	22/01/2024	29/01/2024
2		Supplier 1	2	02/01/2024	£265.00		£265.00
3		Supplier 2	12	12/01/2024	£489.00			£489.00
4		Supplier 3	6	06/01/2024	£438.00		£438.00
5		Supplier 4	1	01/01/2024	£605.00		£605.00
6		Supplier 5	23	23/01/2024	£538.00					£538.00
7		Supplier 6	13	13/01/2024	£120.00			£120.00
8		Supplier 7	22	22/01/2024	£684.00					£684.00
9		Supplier 8	1	01/01/2024	£448.00		£448.00
10		Supplier 9	18	18/01/2024	£785.00				£785.00
11		Supplier 10	14	14/01/2024	£231.00			£231.00
12		Supplier 11	23	23/01/2024	£105.00					£105.00
13		Supplier 12	21	21/01/2024	£379.00				£379.00
14		Supplier 13	8	08/01/2024	£726.00			£726.00
Sheet1

Cell Formulas
Range	Formula
C2:C14	C2	=RANDBETWEEN(1,28)
D2:D14	D2	=DATE(2024,1,C2)
E2:E14	E2	=RANDBETWEEN(100,1000)
G2:K14	G2	=LET(f,ISNUMBER(MATCH($C2,SEQUENCE(1,7,DAY(G$1),1),0))*$E2,IF(f<>0,f,""))

 

[El Rebelde]

That looks great; I look forward to trying that out on my actual data later. Thank you so much!

 

[HighAndWilder]

That looks great; I look forward to trying that out on my actual data later. Thank you so much!

I look forward to your response.

When a post relies on having some data to put together a solution use XL2BB as it avoids others having to create some realistic data.

XL2BB - Excel Range to BBCode

Convert selected range to BBCode to include as a snapshot in your question.

www.mrexcel.com