Counting and comparing numbers until condition is met

[jaffar]

Hi all
I'm trying to sum a set of numbers until it is bigger than a given number, then count how many numbers there were until that condition is met.
My example is to count how many trains are required to service a given pedestrian queue at a station, when the profile of boarding demand and boarding capacity of each train is a known value but which varies across the trains over time. The passengers unable to board is after the train is full and departed the station i.e. the size of the queue as soon as the train departs.

I have attached the data where I have manually calculated the correct trains required to clear a given queue associated with a train, but as an example, for station 2 train 7 departs with 826 people unable to board. It requires the next 4 trains to clear the back of this queue, as the next 4 trains have 87, 312, 312, 312 boarding capacity each. Another example is station 2 train 3 departs with 366 people unable to board, and requires the subsequent 6 trains to clear this queue (49,49,49,38,,87,312 boarding capacity from trains 4-9, until the queue associated with train 3 is cleared).

So any help to develop a formula with the smarts to replicate these numbers is what I am trying to figure out how to do. Hopefully I explained the issue and what I am after but happy to help clarify further, thanks.
Note my current formula i have manually dragged the range of board capacity to figure out how many trains were required, a manual process.

Queue test.xlsx
	A	B	C	D	E	F	G	H	I	J	K
3	Passenger Unable to board		Station 1	Station 2		trains required to serve end of queue				Station 1	Station 2
4		Train 1	83	122					Train 1	1	3
5		Train 2	167	244					Train 2	1	6
6		Train 3	250	366					Train 3	1	6
7		Train 4	333	488					Train 4	1	5
8		Train 5	417	610					Train 5	1	5
9		Train 6	500	732					Train 6	1	5
10		Train 7	226	826					Train 7	1	4
11		Train 8	0	872					Train 8	0	3
12		Train 9	0	693					Train 9	0	3
13		Train 10	0	513					Train 10	0	2
14		Train 11	0	334					Train 11	0	2
15		Train 12	0	154					Train 12	0	1
16		Train 13	0	0					Train 13	0	0
17		Train 14	0	0					Train 14	0	0
18		Train 15	0	0					Train 15
19
20	Passenger Board capacity		Station 1	Station 2
21		Train 1	882	49
22		Train 2	882	49
23		Train 3	882	49
24		Train 4	882	49
25		Train 5	882	49
26		Train 6	882	49
27		Train 7	1023	38
28		Train 8	1023	87
29		Train 9	1023	312
30		Train 10	1023	312
31		Train 11	1023	312
32		Train 12	1023	312
33		Train 13	1023	312
34		Train 14	1023	312
35		Train 15	1023	312
Sheet1

Cell Formulas
Range	Formula
J4:J17,K16:K17	J4	=ROUNDUP(C4/C22,0)
K4,K11:K12	K4	=IF((SUM(D22:D24)>D4),COUNT(D22:D24),"FALSE")
K5:K6	K5	=IF((SUM(D23:D28)>D5),COUNT(D23:D28),"FALSE")
K7:K9	K7	=IF((SUM(D25:D29)>D7),COUNT(D25:D29),"FALSE")
K10	K10	=IF((SUM(D28:D31)>D10),COUNT(D28:D31),"FALSE")
K13:K14	K13	=IF((SUM(D31:D32)>D13),COUNT(D31:D32),"FALSE")
K15	K15	=IF((SUM(D33)>D15),COUNT(D33),"FALSE")

 

[JamesCanale]

I couldn't understand the specifics of your problem, so I just looked at the first sentence and tried something for that:

Here I use randarray in A to make some numbers. B1 has the target. B2 is the formula that tells how many numbers in A add up to more than the target.

MrExcelPlayground4.xlsx
	A	B	C
1	Numbers	300	target
2	90	7	to get over the targe
3	52
4	29
5	71
6	21
7	13
8	39
9	12
10	15
11	32
12	40
13	51
14	52
15	81
16	31
17	36
18	55
19	72
20	57
21	51
Sheet17

Cell Formulas
Range	Formula
A2:A21	A2	=RANDARRAY(20,1,10,90,TRUE)
B2	B2	=SUMPRODUCT(--(SUBTOTAL(9,OFFSET(A2#,,,ROW(A2#)-ROW(INDEX(A2#,1))+1))<B1))+1
Dynamic array formulas.

 

[jaffar]

Hi James, thanks for taking the time to help.
That seems perfect, I was able to use your formula on my example and produce the same numbers.
Are you able to give a bit of a explanation as to how it works/how you came up with it? I will have to study those formulas used a bit more but hearing how you broke down the problem would be of great value to me.

 

[JamesCanale]

This needs a lot of unpacking. I find it's helpful to take bits out of the formula and put them in their own cells.

The OFFSET part gives you an increasing large column of numbers, starting from row 2, then 2:3, then 2:4,... So it goes through each row, and subtracts the row number of the first row and constructs a range. The a2# represents a range like a2:a21 might have done - it works with dynamic arrays - a new feature of 365 I think.

Subtotal(9...) works where SUM does not in these kinds of calculations - it maintains the array.

You see it check that it is less than B1.

So that gives you a load of true, true, true,... false, false, false.

Doing 'identity math' on a true/false, gives you a 1,0. Meaning that you add 0, or multiply by 1, or multiply by -1 and then -1 again (--).

Then summing it up.

MrExcelPlayground4.xlsx
	A	B	C	D	E	F	G
1	Numbers	300	target
2	36	8	to get over the targe	36	TRUE	1	8
3	15			51	TRUE	1
4	48			99	TRUE	1
5	43			142	TRUE	1
6	22			164	TRUE	1
7	14			178	TRUE	1
8	75			253	TRUE	1
9	61			314	FALSE	0
10	74			388	FALSE	0
11	47			435	FALSE	0
12	58			493	FALSE	0
13	40			533	FALSE	0
14	71			604	FALSE	0
15	64			668	FALSE	0
16	43			711	FALSE	0
17	27			738	FALSE	0
18	72			810	FALSE	0
19	17			827	FALSE	0
20	31			858	FALSE	0
21	62			920	FALSE	0
Sheet17

Cell Formulas
Range	Formula
A2:A21	A2	=RANDARRAY(20,1,10,90,TRUE)
B2	B2	=SUMPRODUCT(--(SUBTOTAL(9,OFFSET(A2#,,,ROW(A2#)-ROW(INDEX(A2#,1))+1))<B1))+1
D2:D21	D2	=SUBTOTAL(9,OFFSET(A2#,,,ROW(A2#)-ROW(INDEX(A2#,1))+1))
E2:E21	E2	=D2#<B1
F2:F21	F2	=--(E2#)
G2	G2	=SUMPRODUCT(F2#)+1
Dynamic array formulas.

 

[jaffar]

Really brilliant solution! Even after doing a bit of study on this one, still wrapping my head around it. For the OFFSET reference why use A2# instead of A2 - haven't quite figured out why this one works yet. And why use SUMPRODUCT instead of SUM?

Also do you have any recommended recent books on excel dynamic arrays? Been out of the loop for a bit excel has got quite a few new features.

 

[JamesCanale]

A2# - is a reference to a dynamic array. It replaces A2:A21. A new feature of 365. A handy way of handling variable array sizes if they are generated as a dynamic array.

Sum would work fine too. you get into the habit of sumproduct because you can have multiple conditions. In this case there is just one condition - that the running sum is less than the target.

No books. Here. Here is the place to learn about dynamic arrays. I've probably doubled my skill in excel (which I had deemed pretty good already) in the last year. Some of the real masters here demonstrate the elegance of brevity so well. You'll come to know them.

I'd recommend solving peoples problems yourself on this site when you can (post the answers or not), but see how some of the masters do it. You'll be very sharp in a short time.