Array Formula

[Frankroger]

I'm trying to figure out an array formula for this problem.

The two yellow cells are my inputs, F2 and G2

G6 is the answer cell that I need to create an array formula for

I've entered the date 15/01/2000 into "F2"
and the unit number 10 into "G2"

I want to return into cell "G6"
the date that is closest to 10 units from my specified start date, using columns "A" and "B" to figure from.

Column D is just an example,
I'm summing the cells in column "B" that are greater than the 15/01/2000, then ive highlighted in red the date that was closest to 10 units, so cell "G6" should return the date 21/01/2000

If I entered 17 into "G2", then "G6" should return 25/01/2000

But I want to figure this date via a single array formula rather using thousands of figuring helper cells

Any assistance would be greatly appreciated! Thank you

Edit: I didn't enter the title to my thread properly when I posted, but it won't allow me to edit to make the title more specific to my question

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Date	Unit				Start Date	Unit
2	10/01/2000	1.1				15/01/2000	10
3	11/01/2000	1.3
4	12/01/2000	1.6
5	13/01/2000	1.4					Anwser
6	14/01/2000	1.5					21/01/2000		[0]0[/FORMULA]
7	15/01/2000	1.4		1.4
8	16/01/2000	1.7		3.1
9	17/01/2000	1.8		4.9
10	18/01/2000	1.2		6.1
11	19/01/2000	1.5		7.6
12	20/01/2000	1.3		8.9
13	21/01/2000	1.7		10.6
14	22/01/2000	1.8		12.4
15	23/01/2000	1.5		13.9
16	24/01/2000	1.9		15.8
17	25/01/2000	1.2		17
18	26/01/2000	1.1		18.1
19	27/01/2000	1.8		19.9
20	28/01/2000	1.9		21.8
21	29/01/2000	1.2		23
22	30/01/2000	1.6		24.6
23	31/01/2000	1.3		25.9
24	01/02/2000	1.2		27.1
25	02/02/2000	1.6		28.7
26	03/02/2000	1.8		30.5
27
28
29
Sheet3

Cell Formulas
Range	Formula
I6	I6
A3:A26	A3	=A2+1
D7:D26	D7	=SUM(B$7:$C7)

 

[Cubist]

Are you still using XL2013?

 

[Frankroger]

Are you still using XL2013?

Yes, unfortunately =(

I've created another minisheet with an updated breakdown of the figuring by using helper cells.
Just to provide clarity of what I was hoping an array formula could solve without the helper cells

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Date	Unit				Start Date	Unit
2	10/01/2000	1.1				15/01/2000	11
3	11/01/2000	1.3
4	12/01/2000	1.6
5	13/01/2000	1.4					Anwser
6	14/01/2000	1.5					21/01/2000
7	15/01/2000	1.4		1.4
8	16/01/2000	1.7		3.1					Breakdown
9	17/01/2000	1.8		4.9					Index/Match	Date		Anwser
10	18/01/2000	1.2		6.1				1.4	1.4	15/01/2000		21/01/2000
11	19/01/2000	1.5		7.6				3.1	1.7	16/01/2000
12	20/01/2000	1.3		8.9				4.9	1.8	17/01/2000
13	21/01/2000	1.7		10.6				6.1	1.2	18/01/2000
14	22/01/2000	1.8		12.4				7.6	1.5	19/01/2000
15	23/01/2000	1.5		13.9				8.9	1.3	20/01/2000
16	24/01/2000	1.9		15.8				10.6	1.7	21/01/2000
17	25/01/2000	1.2		17				12.4	1.8	22/01/2000
18	26/01/2000	1.1		18.1				13.9	1.5	23/01/2000
19	27/01/2000	1.8		19.9				15.8	1.9	24/01/2000
20	28/01/2000	1.9		21.8				17	1.2	25/01/2000
21	29/01/2000	1.2		23				18.1	1.1	26/01/2000
22	30/01/2000	1.6		24.6				19.9	1.8	27/01/2000
23	31/01/2000	1.3		25.9				21.8	1.9	28/01/2000
24	01/02/2000	1.2		27.1				23	1.2	29/01/2000
25	02/02/2000	1.6		28.7				24.6	1.6	30/01/2000
26	03/02/2000	1.8		30.5
27
28
Sheet3

Cell Formulas
Range	Formula
H10:H25	H10	=SUM($I$10:I10)
I10:I25	I10	=INDEX(B2:B26,MATCH(J10,A2:A26,0))
J10	J10	=F2
A3:A26,J11:J25	J11	=J10+1
L10	L10	=VLOOKUP(G2,H10:J25,3,TRUE)
D7:D26	D7	=SUM(B$7:$C7)

 

[Cubist]

Microsoft no longer supports XL2013 so you're at risk. Anyhow, try this with one helper column. I don't have 2013 to test. (Note: date format in the US).

loop50.xlsm
	A	B	C	D	E	F	G
1	Date	Unit	Helper			Start Date	Unit
2	1/10/2000	1.1	0			1/15/2000	10
3	1/11/2000	1.3	0
4	1/12/2000	1.6	0
5	1/13/2000	1.4	0				Anwser
6	1/14/2000	1.5	0				1/21/2000
7	1/15/2000	1.4	1.4
8	1/16/2000	1.7	3.1
9	1/17/2000	1.8	4.9
10	1/18/2000	1.2	6.1
11	1/19/2000	1.5	7.6
12	1/20/2000	1.3	8.9
13	1/21/2000	1.7	10.6
14	1/22/2000	1.8	12.4
15	1/23/2000	1.5	13.9
16	1/24/2000	1.9	15.8
17	1/25/2000	1.2	17
18	1/26/2000	1.1	18.1
19	1/27/2000	1.8	19.9
20	1/28/2000	1.9	21.8
21	1/29/2000	1.2	23
22	1/30/2000	1.6	24.6
23	1/31/2000	1.3	25.9
24	2/1/2000	1.2	27.1
25	2/2/2000	1.6	28.7
26	2/3/2000	1.8	30.5
Sheet3

Cell Formulas
Range	Formula
G6	G6	=INDEX($A$2:$A$26,MATCH(TRUE,$C$2:$C$26>=G2,0))
A3:A26	A3	=A2+1
C2:C26	C2	=SUM(IF(A2>=$F$2,B2,0),C1)

 

[Phuoc]

Another option, don't use the helper column:

Book1
	A	B	C	D	E	F	G
1	Date	Unit				Start Date	Unit
2	10/1/2000	1.1				15/1/2000	11
3	11/1/2000	1.3
4	12/1/2000	1.6
5	13/1/2000	1.4					Anwser
6	14/1/2000	1.5				21/1/2000	21/1/2000
7	15/1/2000	1.4		1.4
8	16/1/2000	1.7		3.1
9	17/1/2000	1.8		4.9
10	18/1/2000	1.2		6.1
11	19/1/2000	1.5		7.6
12	20/1/2000	1.3		8.9
13	21/1/2000	1.7		10.6
14	22/1/2000	1.8		12.4
15	23/1/2000	1.5		13.9
16	24/1/2000	1.9		15.8
17	25/1/2000	1.2		17
18	26/1/2000	1.1		18.1
19	27/1/2000	1.8		19.9
20	28/1/2000	1.9		21.8
21	29/1/2000	1.2		23
22	30/1/2000	1.6		24.6
23	31/1/2000	1.3		25.9
24	1/2/2000	1.2		27.1
25	2/2/2000	1.6		28.7
26	3/2/2000	1.8		30.5
Sheet1

Cell Formulas
Range	Formula
F6	F6	=LOOKUP(G2,SUMIFS($B$2:$B$26,$A$2:$A$26,">="&F2,$A$2:$A$26,"<="&$A$2:$A$26),$A$2:$A$26)
A3:A26	A3	=A2+1
D7:D26	D7	=SUM(B$7:$C7)

 

[Frankroger]

Perfect, thank you so much, it has helped me greatly!

 

[Frankroger]

5th

Another option, don't use the helper column:

Book1
	A	B	C	D	E	F	G
1	Date	Unit				Start Date	Unit
2	10/1/2000	1.1				15/1/2000	11
3	11/1/2000	1.3
4	12/1/2000	1.6
5	13/1/2000	1.4					Anwser
6	14/1/2000	1.5				21/1/2000	21/1/2000
7	15/1/2000	1.4		1.4
8	16/1/2000	1.7		3.1
9	17/1/2000	1.8		4.9
10	18/1/2000	1.2		6.1
11	19/1/2000	1.5		7.6
12	20/1/2000	1.3		8.9
13	21/1/2000	1.7		10.6
14	22/1/2000	1.8		12.4
15	23/1/2000	1.5		13.9
16	24/1/2000	1.9		15.8
17	25/1/2000	1.2		17
18	26/1/2000	1.1		18.1
19	27/1/2000	1.8		19.9
20	28/1/2000	1.9		21.8
21	29/1/2000	1.2		23
22	30/1/2000	1.6		24.6
23	31/1/2000	1.3		25.9
24	1/2/2000	1.2		27.1
25	2/2/2000	1.6		28.7
26	3/2/2000	1.8		30.5
Sheet1

Cell Formulas
Range	Formula
F6	F6	=LOOKUP(G2,SUMIFS($B$2:$B$26,$A$2:$A$26,">="&F2,$A$2:$A$26,"<="&$A$2:$A$26),$A$2:$A$26)
A3:A26	A3	=A2+1
D7:D26	D7	=SUM(B$7:$C7)

Another option, don't use the helper column:

Book1
	A	B	C	D	E	F	G
1	Date	Unit				Start Date	Unit
2	10/1/2000	1.1				15/1/2000	11
3	11/1/2000	1.3
4	12/1/2000	1.6
5	13/1/2000	1.4					Anwser
6	14/1/2000	1.5				21/1/2000	21/1/2000
7	15/1/2000	1.4		1.4
8	16/1/2000	1.7		3.1
9	17/1/2000	1.8		4.9
10	18/1/2000	1.2		6.1
11	19/1/2000	1.5		7.6
12	20/1/2000	1.3		8.9
13	21/1/2000	1.7		10.6
14	22/1/2000	1.8		12.4
15	23/1/2000	1.5		13.9
16	24/1/2000	1.9		15.8
17	25/1/2000	1.2		17
18	26/1/2000	1.1		18.1
19	27/1/2000	1.8		19.9
20	28/1/2000	1.9		21.8
21	29/1/2000	1.2		23
22	30/1/2000	1.6		24.6
23	31/1/2000	1.3		25.9
24	1/2/2000	1.2		27.1
25	2/2/2000	1.6		28.7
26	3/2/2000	1.8		30.5
Sheet1

Cell Formulas
Range	Formula
F6	F6	=LOOKUP(G2,SUMIFS($B$2:$B$26,$A$2:$A$26,">="&F2,$A$2:$A$26,"<="&$A$2:$A$26),$A$2:$A$26)
A3:A26	A3	=A2+1
D7:D26	D7	=SUM(B$7:$C7)

Thank you!

It works, but it unfortunately freezes excel in calculation when using ranges of 25000 cells, that I'm waiting a few minutes for it to unfreeze.

It's interesting how the helper column method, which adds an extra 25000 cells of individual calculations to be made,
is more efficent at getting the answer from a large dataset than via the single formula method and doesn't freeze in the process

Is one big number crunch within a single cell more intensive on the cpu than a problem being divided out amongst many helper cells?

 

[Phuoc]

If the data is large, you can use a helper column:

Book1
	A	B	C	D	E	F	G	H
1	Date	Unit	Helper			Start Date	Unit	Anwser
2	10/1/2000	1.1	1.1			11/1/2000	19	22/1/2000
3	11/1/2000	1.3	2.4			15/1/2000	12	21/1/2000
4	12/1/2000	1.6	4			18/1/2000	15	27/1/2000
5	13/1/2000	1.4	5.4
6	14/1/2000	1.5	6.9
7	15/1/2000	1.4	8.3
8	16/1/2000	1.7	10
9	17/1/2000	1.8	11.8
10	18/1/2000	1.2	13
11	19/1/2000	1.5	14.5
12	20/1/2000	1.3	15.8
13	21/1/2000	1.7	17.5
14	22/1/2000	1.8	19.3
15	23/1/2000	1.5	20.8
16	24/1/2000	1.9	22.7
17	25/1/2000	1.2	23.9
18	26/1/2000	1.1	25
19	27/1/2000	1.8	26.8
20	28/1/2000	1.9	28.7
21	29/1/2000	1.2	29.9
22	30/1/2000	1.6	31.5
23	31/1/2000	1.3	32.8
24	1/2/2000	1.2	34
25	2/2/2000	1.6	35.6
26	3/2/2000	1.8	37.4
Sheet1

Cell Formulas
Range	Formula
H2:H4	H2	=INDEX($A$2:$A$26,MATCH(G2+VLOOKUP(F2-1,$A$2:$C$26,3,1),$C$2:$C$26,1))
C2:C26	C2	=SUM(B2,C1)

 

[Cubist]

@Phuoc
For 11/1/2000 & 19 units, I'd expect 23/1/2000 (19.7). Where 22/1/2000 is only 18.2
Similar issue for the next row.