Q: Formula for Aging

[Joeun]

Hi Guys,

I need help for completing my formula on cell M3:M9 by filling in the Aging days. The data will take from identify the "lot number" on cell I3:I9, search value on my stock table and return with the latest incoming date.

Thanks for any suggestions.

Stock.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	DATE	LOT NO.	IN KG	OUT KG	BALANCE
2	DATE	LOT NO.	IN KG	OUT KG	BALANCE				Lot No	In	Out	Balance	Aging		Last Incoming Date	Aging (day)
3	26/03/20	18S0165W	23,529.00		23,529.00				18S0165W	85395.71	82953.71	2442			14/05/23	115
4	30/03/20	18S0165W		1,650.00	21,879.00				18S0218W	16467	15843	624			08/03/21	912
5	03/04/20	18S0165W		3,300.00	18,579.00				18S0282W	16929	16907	22			05/06/21	823
6	07/04/20	18S0165W		3,300.00	15,279.00				18S0300W	25794.48	25260.48	534			22/05/23	107
7	09/04/20	18S0165W		3,300.00	11,979.00					0	0	0
8	15/04/20	18S0165W		3,300.00	8,679.00					0	0	0
9	30/04/20	18S0165W	23,529.00		32,208.00					0	0	0
10	27/05/20	18S0165W		3,300.00	28,908.00
11	05/06/20	18S0165W		1,980.00	26,928.00
12	09/06/20	18S0165W		726.00	26,202.00
13	11/06/20	18S0165W		1,320.00	24,882.00
14	27/07/20	18S0165W	1,377.56		26,259.56
15	11/08/20	18S0165W		816.56	25,443.00
16	25/08/20	18S0165W		660.00	24,783.00
17	07/09/20	18S0165W		2,244.00	22,539.00
18	24/09/20	18S0165W		1,980.00	20,559.00
19	02/10/20	18S0165W		1,089.00	19,470.00
20	05/10/20	18S0165W		1,320.00	18,150.00
21	19/10/20	18S0165W		858.00	17,292.00
22	11/11/20	18S0165W		693.00	16,599.00
23	24/11/20	18S0165W		660.00	15,939.00
24	08/12/20	18S0165W		1,320.00	14,619.00
25	15/12/20	18S0165W		4,620.00	9,999.00
26	05/01/21	18S0165W		6,600.00	3,399.00
27	04/02/21	18S0165W		3,399.00	-
28	08/03/21	18S0218W	16,467.00		16,467.00
29	08/03/21	18S0218W		1,980.00	14,487.00
30	10/03/21	18S0218W		528.00	13,959.00
31	11/03/21	18S0218W		726.00	13,233.00
32	01/04/21	18S0218W		1,320.00	11,913.00
33	07/04/21	18S0218W		1,386.00	10,527.00
34	06/04/21	18S0218W		660.00	9,867.00
35	13/04/21	18S0218W		1,188.00	8,679.00
36	15/04/21	18S0218W		1,320.00	7,359.00
37	16/04/21	18S0218W		1,320.00	6,039.00
38	21/04/21	18S0218W		660.00	5,379.00
39	22/04/21	18S0218W		726.00	4,653.00
40	05/06/21	18S0282W	16,929.00		16,929.00
41	15/07/21	18S0218W		2,970.00	1,683.00
42	21/07/21	18S0218W		396.00	1,287.00
43	24/07/21	18S0218W		363.00	924.00
44	30/07/21	18S0218W		300.00	624.00
45	30/07/21	18S0282W		396.00	16,533.00
46	23/08/21	18S0282W		1,980.00	14,553.00
47	22/09/21	18S0282W		2,112.00	12,441.00
48	24/09/21	18S0282W		1,584.00	10,857.00
49	18/10/21	18S0282W		528.00	10,329.00
50	19/10/21	18S0282W		792.00	9,537.00
51	20/10/21	18S0282W		1,848.00	7,689.00
52	27/10/21	18S0282W		1,056.00	6,633.00
53	01/11/21	18S0282W		6,303.00	330.00
54	25/11/21	18S0165W	5,907.00		5,907.00
55	29/11/21	18S0165W		1,452.00	4,455.00
56	29/11/21	18S0282W		297.00	33.00
57	29/11/21	18S0165W		3,300.00	1,155.00
58	30/11/21	18S0282W		11.00	22.00
59	06/12/21	18S0165W		1,155.00	-
60	20/12/21	18S0165W	37.30		37.30
61	24/12/21	18S0300W	23,529.00		23,529.00
62	29/12/21	18S0300W		3,300.00	20,229.00
63	31/12/21	18S0300W		891.00	19,338.00
64	04/01/22	18S0300W		6,600.00	12,738.00
65	18/01/22	18S0300W	15.48		12,753.48
66	24/01/22	18S0300W		3,300.00	9,453.48
67	11/02/22	18S0300W		1,980.00	7,473.48
68	11/02/22	18S0165W	23,529.00		23,566.30
69	16/02/22	18S0300W		1,056.00	6,417.48
70	23/02/22	18S0300W		660.00	5,757.48
71	24/02/22	18S0300W		528.00	5,229.48
72	01/07/22	18S0300W		33.00	5,196.48
73	04/07/22	18S0300W		5,148.00	48.48
74	03/08/22	18S0165W		2,244.00	21,322.30
75	08/08/22	18S0165W		2,508.00	18,814.30
76	24/08/22	18S0165W		726.00	18,088.30
77	03/09/22	18S0165W		1,980.00	16,108.30
78	07/09/22	18S0165W		5,016.00	11,092.30
79	07/09/22	18S0165W		165.00	10,927.30
80	12/09/22	18S0165W		7,491.00	3,436.30
81	14/09/22	18S0165W		2,145.00	1,291.30
82	11/10/22	18S0165W		1,254.00	37.30
83	28/10/22	18S0165W	226.85		264.15
84	19/04/23	18S0165W		264.15	- 0.00
85	19/04/23	18S0300W		8.48	40.00
86	14/05/23	18S0165W	7,260.00		7,260.00
87	22/05/23	18S0300W	2,250.00		2,290.00
88	18/06/23	18S0300W		1,756.00	534.00
89	06/07/23	18S0165W		1,419.00	5,841.00
90	07/07/23	18S0165W		2,013.00	3,828.00
91	15/08/23	18S0165W		792.00	3,036.00
92	21/08/23	18S0165W		594.00	2,442.00
Sheet3

Cell Formulas
Range	Formula
I3:I9	I3	=IF(SUMIF($B$3:$B$107,INDEX(B3:B107,MATCH(0,INDEX(COUNTIF($I$2:I2,B3:B107),),0)),$C$3:$C$107)-SUMIF($B$3:$B$107,INDEX(B3:B107,MATCH(0,INDEX(COUNTIF($I$2:I2,B3:B107),),0)),$D$3:$D$107)>0,INDEX(B3:B107,MATCH(0,INDEX(COUNTIF($I$2:I2,B3:B107),),0)),"")
J3:J9	J3	=SUMIF($B$3:$B$107,I3,$C$3:$C$107)
K3:K9	K3	=SUMIF($B$3:$B$107,I3,$D$3:$D$107)
L3:L9	L3	=J3-K3
P3:P6	P3	=TODAY()-O3
E90:E92,E3:E88	E3	=IFNA(LOOKUP(2,1/($B$1:B2=B3),$E$1:E2),0)+C3-D3
E89	E89	=IFNA(LOOKUP(2,1/($B$1:B86=B89),$E$1:E86),0)+C89-D89

 

[duggie33]

I am not sure if you have the MAXIFS function available. If yes, you could try the following formula in cell M3:

=TODAY()-MAXIFS($A$3:$A$92,$B$3:$B$92,I3,$C$3:$C$92,">0")

Hope that helps,

Doug

 

[Fluff]

I am not sure if you have the MAXIFS function available

Not with xl 2016, it appeared with 2019

 

[duggie33]

Joy, so it will probably require a combination of IF and MAX functions, an array formula with Ctrl-Shift-Enter. Right Fluff? I always struggled writing those...LOL.

 

[Joeun]

I am not sure if you have the MAXIFS function available. If yes, you could try the following formula in cell M3:

Thanks for suggestion. Need more luck for older version

 

[duggie33]

Hi Joeun,

A quick search for "excel maxifs equivalent" yielded results that showed how to write it with a MAX and IF combined function or using the AGGREGATE function.

Give one or both of those methods a shot.

Doug

 

[Joeun]

Is anyone might be able to help with
Appreciate it!

 

[kevin9999]

Is this it? (formulas in O3:O5)

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	DATE	LOT NO.	IN KG	OUT KG	BALANCE
2	DATE	LOT NO.	IN KG	OUT KG	BALANCE				Lot No	In	Out	Balance	Aging		Last Incoming Date	Aging (day)
3	26/03/2020	18S0165W	23529		23529				18S0165W	85395.71	82953.71	2442			14/05/2023	117
4	30/03/2020	18S0165W		1650	21879				18S0218W	16467	15843	624			8/03/2021	914
5	3/04/2020	18S0165W		3300	18579				18S0282W	16929	16907	22			5/06/2021	825
6	7/04/2020	18S0165W		3300	15279				18S0300W	25794.48	25260.48	534			22/05/2023	109
7	9/04/2020	18S0165W		3300	11979					0	0	0
8	15/04/2020	18S0165W		3300	8679					0	0	0
9	30/04/2020	18S0165W	23529		32208					0	0	0
10	27/05/2020	18S0165W		3300	28908
11	5/06/2020	18S0165W		1980	26928
12	9/06/2020	18S0165W		726	26202
13	11/06/2020	18S0165W		1320	24882
14	27/07/2020	18S0165W	1377.56		26259.56
Sheet1

Cell Formulas
Range	Formula
I3:I9	I3	=IF(SUMIF($B$3:$B$107,INDEX(B3:B107,MATCH(0,INDEX(COUNTIF($I$2:I2,B3:B107),),0)),$C$3:$C$107)-SUMIF($B$3:$B$107,INDEX(B3:B107,MATCH(0,INDEX(COUNTIF($I$2:I2,B3:B107),),0)),$D$3:$D$107)>0,INDEX(B3:B107,MATCH(0,INDEX(COUNTIF($I$2:I2,B3:B107),),0)),"")
J3:J9	J3	=SUMIF($B$3:$B$107,I3,$C$3:$C$107)
K3:K9	K3	=SUMIF($B$3:$B$107,I3,$D$3:$D$107)
L3:L9	L3	=J3-K3
O3:O6	O3	=AGGREGATE(14,6,$A:$A/(($B:$B=I3)*($C:$C>0)),1)
P3:P6	P3	=TODAY()-O3
E3:E14	E3	=IFNA(LOOKUP(2,1/($B$1:B2=B3),$E$1:E2),0)+C3-D3

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet1!$A$2:$E$92	O3:O6

 

[Joeun]

Is this it? (formulas in O3:O5)

Thanks kevin9999. My problem solved
Really appreciate it so much.

 

[kevin9999]

Glad we could help & thanks for the feedback