Depreciation Formula

[MutanGFX]

Hello!

I need help coming out with a formula that does the following:

On F2 it shows the sum of B collumn if the Category and Month matches, and divide the Price by 10.
Then on G2 I want it to show the same calculation + whatever new value there is new for the month also divided by 10.
And so on.

I made it manually for the categories 2.13.01 and 2.13.02 as to explain visually my end goal.

Thank you in advance!

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	Category	Price	Date			01/01/2022	01/02/2022	01/03/2022	01/04/2022	01/05/2022	01/06/2022	01/07/2022	01/08/2022	01/09/2022	01/10/2022	01/11/2022	01/12/2022	01/01/2023	01/02/2023	01/03/2023	01/04/2023	01/05/2023	01/06/2023	01/07/2023
2	2.13.01	450	02/07/2022		2.13.01	60	60	60	60	60	60	105	105	105	105	45	45	45	45	45	45
3	2.13.02	850	02/07/2022		2.13.02				5	5	5	90	90	90	90	90	90	90	85	85	85
4	2.13.03	900	30/09/2022		2.13.03
5	2.13.04	200	30/10/2022		2.13.04
6	2.13.05	950	03/01/2022		2.13.05
7	2.13.06	450	02/02/2022		2.13.06
8	2.13.07	850	03/05/2022		2.13.07
9	2.13.08	750	04/03/2022		2.13.08
10	2.13.09	250	04/03/2022		2.13.09
11	2.13.10	150	31/08/2022		2.13.10
12	2.13.01	600	03/01/2022
13	2.13.02	50	03/04/2022
14	2.13.03	800	04/03/2022
15	2.13.04	850	30/10/2022
16	2.13.05	900	04/03/2022
17	2.13.06	400	04/03/2022
18	2.13.07	600	03/04/2022
19	2.13.08	550	03/01/2022
20	2.13.09	250	31/08/2022
21	2.13.10	300	30/10/2022
Sheet1

 

[JvdV]

One way is to use a boolean structure like:

=SUM((MONTH($C$2:$C$21)=MONTH(F$1))*(YEAR($C$2:$C$21)=YEAR(F$1))*($A$2:$A$21=$E2)*$B$2:$B$21)/10

 

[MutanGFX]

One way is to use a boolean structure like:

=SUM((MONTH($C$2:$C$21)=MONTH(F$1))*(YEAR($C$2:$C$21)=YEAR(F$1))*($A$2:$A$21=$E2)*$B$2:$B$21)/10

That gives me the total value for each month when the price occours, but it doesn't spill the /10 to the next 9 months.

 

[JvdV]

Right, I read that wrong at first. Try below:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	Category	Price	Date			1-1-2022	1-2-2022	1-3-2022	1-4-2022	1-5-2022	1-6-2022	1-7-2022	1-8-2022	1-9-2022	1-10-2022	1-11-2022	1-12-2022	1-1-2023	1-2-2023	1-3-2023	1-4-2023	1-5-2023	1-6-2023	1-7-2023
2	2.13.01	450	2-7-2022		2.13.01	60	60	60	60	60	60	105	105	105	105	45	45	45	45	45	45	0	0	0
3	2.13.02	850	2-7-2022		2.13.02	0	0	0	5	5	5	90	90	90	90	90	90	90	85	85	85	0	0	0
4	2.13.03	900	30-9-2022		2.13.03	0	0	80	80	80	80	80	80	170	170	170	170	90	90	90	90	90	90	0
5	2.13.04	200	30-10-2022		2.13.04	0	0	0	0	0	0	0	0	0	105	105	105	105	105	105	105	105	105	105
6	2.13.05	950	3-1-2022		2.13.05	95	95	185	185	185	185	185	185	185	185	90	90	0	0	0	0	0	0	0
7	2.13.06	450	2-2-2022		2.13.06	0	45	85	85	85	85	85	85	85	85	85	40	0	0	0	0	0	0	0
8	2.13.07	850	3-5-2022		2.13.07	0	0	0	60	145	145	145	145	145	145	145	145	145	85	0	0	0	0	0
9	2.13.08	750	4-3-2022		2.13.08	55	55	130	130	130	130	130	130	130	130	75	75	0	0	0	0	0	0	0
10	2.13.09	250	4-3-2022		2.13.09	0	0	25	25	25	25	25	50	50	50	50	50	25	25	25	25	25	0	0
11	2.13.10	150	31-8-2022		2.13.10	0	0	0	0	0	0	0	15	15	45	45	45	45	45	45	45	45	30	30
12	2.13.01	600	3-1-2022
13	2.13.02	50	3-4-2022
14	2.13.03	800	4-3-2022
15	2.13.04	850	30-10-2022
16	2.13.05	900	4-3-2022
17	2.13.06	400	4-3-2022
18	2.13.07	600	3-4-2022
19	2.13.08	550	3-1-2022
20	2.13.09	250	31-8-2022
21	2.13.10	300	30-10-2022
Sheet9

Cell Formulas
Range	Formula
F2:X11	F2	=LET(x,E2:E11,y,F1:X1,MAKEARRAY(ROWS(x),COLUMNS(y),LAMBDA(r,c,SUM((A2:A21=INDEX(x,r))*(C2:C21>=EOMONTH(INDEX(y,1,c),-10)+1)*(C2:C21<=EOMONTH(INDEX(y,1,c),0))*B2:B21))))/10
Dynamic array formulas.

 

[MutanGFX]

Right, I read that wrong at first. Try below:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	Category	Price	Date			1-1-2022	1-2-2022	1-3-2022	1-4-2022	1-5-2022	1-6-2022	1-7-2022	1-8-2022	1-9-2022	1-10-2022	1-11-2022	1-12-2022	1-1-2023	1-2-2023	1-3-2023	1-4-2023	1-5-2023	1-6-2023	1-7-2023
2	2.13.01	450	2-7-2022		2.13.01	60	60	60	60	60	60	105	105	105	105	45	45	45	45	45	45	0	0	0
3	2.13.02	850	2-7-2022		2.13.02	0	0	0	5	5	5	90	90	90	90	90	90	90	85	85	85	0	0	0
4	2.13.03	900	30-9-2022		2.13.03	0	0	80	80	80	80	80	80	170	170	170	170	90	90	90	90	90	90	0
5	2.13.04	200	30-10-2022		2.13.04	0	0	0	0	0	0	0	0	0	105	105	105	105	105	105	105	105	105	105
6	2.13.05	950	3-1-2022		2.13.05	95	95	185	185	185	185	185	185	185	185	90	90	0	0	0	0	0	0	0
7	2.13.06	450	2-2-2022		2.13.06	0	45	85	85	85	85	85	85	85	85	85	40	0	0	0	0	0	0	0
8	2.13.07	850	3-5-2022		2.13.07	0	0	0	60	145	145	145	145	145	145	145	145	145	85	0	0	0	0	0
9	2.13.08	750	4-3-2022		2.13.08	55	55	130	130	130	130	130	130	130	130	75	75	0	0	0	0	0	0	0
10	2.13.09	250	4-3-2022		2.13.09	0	0	25	25	25	25	25	50	50	50	50	50	25	25	25	25	25	0	0
11	2.13.10	150	31-8-2022		2.13.10	0	0	0	0	0	0	0	15	15	45	45	45	45	45	45	45	45	30	30
12	2.13.01	600	3-1-2022
13	2.13.02	50	3-4-2022
14	2.13.03	800	4-3-2022
15	2.13.04	850	30-10-2022
16	2.13.05	900	4-3-2022
17	2.13.06	400	4-3-2022
18	2.13.07	600	3-4-2022
19	2.13.08	550	3-1-2022
20	2.13.09	250	31-8-2022
21	2.13.10	300	30-10-2022
Sheet9

Cell Formulas
Range	Formula
F2:X11	F2	=LET(x,E2:E11,y,F1:X1,MAKEARRAY(ROWS(x),COLUMNS(y),LAMBDA(r,c,SUM((A2:A21=INDEX(x,r))*(C2:C21>=EOMONTH(INDEX(y,1,c),-10)+1)*(C2:C21<=EOMONTH(INDEX(y,1,c),0))*B2:B21))))/10
Dynamic array formulas.

Thank you! That seems to work perfectly.

Do you mind explaining how did you came up with that solution? Specially how the lambda function works.

 

[JvdV]

Thank you! That seems to work perfectly.

Do you mind explaining how did you came up with that solution? Specially how the lambda function works.

MAKEARRAY() is an lambda helper function that will take two arguments; any amount of rows and any amount of columns. Therefor I first initialized an x and y variable and used their respective rows and column count as input for these two parameters. The third parameter is the actual lambda that will iterate each junction in the imaginary table of rows and columns. We can use this to reference the right row and column from the initial x and y variables.

 

[MutanGFX]

MAKEARRAY() is an lambda helper function that will take two arguments; any amount of rows and any amount of columns. Therefor I first initialized an x and y variable and used their respective rows and column count as input for these two parameters. The third parameter is the actual lambda that will iterate each junction in the imaginary table of rows and columns. We can use this to reference the right row and column from the initial x and y variables.

Got it! Thank you again.
Just one more question, using your solution, if I added another collumn called "Paid" for each payment in the database with valuer "Yes/No", how can I make it such as it only considers the "Yes" values?

Book2
	A	B	C	D
1	Category	Price	Date	Paid
2	2.13.01	450	44744	Yes
3	2.13.02	850	44744	Yes
4	2.13.03	900	44834	No
5	2.13.04	200	44864	Yes
6	2.13.05	950	44564	Yes
7	2.13.06	450	44594	Yes
8	2.13.07	850	44684	No
9	2.13.08	750	44624	Yes
10	2.13.09	250	44624	Yes
11	2.13.10	150	44804	No
12	2.13.01	600	44564	Yes
13	2.13.02	50	44654	Yes
14	2.13.03	800	44624	Yes
15	2.13.04	850	44864	No
16	2.13.05	900	44624	Yes
17	2.13.06	400	44624	Yes
18	2.13.07	600	44654	No
19	2.13.08	550	44564	Yes
20	2.13.09	250	44804	Yes
21	2.13.10	300	44864	Yes
Sheet1

 

[JvdV]

Got it! Thank you again.
Just one more question, using your solution, if I added another collumn called "Paid" for each payment in the database with valuer "Yes/No", how can I make it such as it only considers the "Yes" values?

Book2
	A	B	C	D
1	Category	Price	Date	Paid
2	2.13.01	450	44744	Yes
3	2.13.02	850	44744	Yes
4	2.13.03	900	44834	No
5	2.13.04	200	44864	Yes
6	2.13.05	950	44564	Yes
7	2.13.06	450	44594	Yes
8	2.13.07	850	44684	No
9	2.13.08	750	44624	Yes
10	2.13.09	250	44624	Yes
11	2.13.10	150	44804	No
12	2.13.01	600	44564	Yes
13	2.13.02	50	44654	Yes
14	2.13.03	800	44624	Yes
15	2.13.04	850	44864	No
16	2.13.05	900	44624	Yes
17	2.13.06	400	44624	Yes
18	2.13.07	600	44654	No
19	2.13.08	550	44564	Yes
20	2.13.09	250	44804	Yes
21	2.13.10	300	44864	Yes
Sheet1

You'd include this in the boolean structure:

=LET(x,E2:E11,y,F1:X1,MAKEARRAY(ROWS(x),COLUMNS(y),LAMBDA(r,c,SUM((A2:A21=INDEX(x,r))*(C2:C21>=EOMONTH(INDEX(y,1,c),-10)+1)*(C2:C21<=EOMONTH(INDEX(y,1,c),0))*(D2:D21="Yes")*B2:B21))))/10