=small

[JOEE1979]

Hello,
I'm having an issue with a formula that I found on the MS page but I'm trying to change it
The formula is in cell "C2"
The original formula was =SMALL(array,COUNTIF(array,">"&K)+1)
I want to change "1" to cell "D2"
The reason is so the formula can start to count from where ever I tell it to start.
cell "D2" is a variable that I manually input

Thank you

Book1
	B	C	D
1		10:00:00 AM	10
2		#NUM!	4:00:00 AM
3	1	1:00:00 AM
4	2	2:00:00 AM
5	3	3:00:00 AM
6	4	4:00:00 AM
7	5	5:00:00 AM
8	6	6:00:00 AM
9	7	7:00:00 AM
10	8	8:00:00 AM
11	9	9:00:00 AM
12	10	10:00:00 AM
13	11	11:00:00 AM
14	12	12:00:00 PM
15	13	1:00:00 PM
16	14	2:00:00 PM
17	15	3:00:00 PM
18	16	4:00:00 PM
19	17	5:00:00 PM
Sheet1

Cell Formulas
Range	Formula
C1	C1	=SMALL(C3:C19,D1)
C2	C2	=SMALL(C3:C19,COUNTIF(C3:C19,">"&D1)+D2)

 

[JOEE1979]

I copied the multiple columns into 1 column and deleted the blanks.
But now the original formula dont work anymore for some reason

Weekday Bus Cycling - Copy.xlsx
	A
1	Steeprock
2	5:00:00 AM
3	10
4	4:55:00 AM
5	2:25:00 AM
6	2:25:00 AM
7	2:25:00 AM
8	2:51:00 AM
9	3:15:00 AM
10	3:25:00 AM
11	4:05:00 AM
12	4:05:00 AM
13	4:20:00 AM
14	4:20:00 AM
15	4:25:00 AM
16	4:39:00 AM
17	4:50:00 AM
18	4:55:00 AM
19	4:55:00 AM
20	5:05:00 AM
21	5:05:00 AM
22	5:05:00 AM
23	5:10:00 AM
24	5:20:00 AM
25	5:25:00 AM
26	5:25:00 AM
27	5:25:00 AM
28	5:40:00 AM
29	5:40:00 AM
30	5:55:00 AM
31	6:00:00 AM
32	6:00:00 AM
33	6:10:00 AM
34	6:20:00 AM
35	6:25:00 AM
36	6:25:00 AM
37	6:30:00 AM
38	6:30:00 AM
39	6:35:00 AM
40	6:45:00 AM
41	6:52:00 AM
42	6:55:00 AM
43	7:05:00 AM
44	7:05:00 AM
45	7:25:00 AM
46	7:35:00 AM
47	9:05:00 AM
48	10:05:00 AM
49	10:40:00 AM
50	10:45:00 AM
51	11:35:00 AM
52	11:50:00 AM
53	12:00:00 PM
54	12:02:00 PM
55	12:05:00 PM
56	12:19:00 PM
57	12:20:00 PM
58	12:25:00 PM
59	12:50:00 PM
60	12:55:00 PM
61	1:10:00 PM
62	1:20:00 PM
63	1:30:00 PM
64	1:35:00 PM
65	1:55:00 PM
66	2:00:00 PM
67	2:05:00 PM
68	2:10:00 PM
69	2:15:00 PM
70	2:15:00 PM
71	2:20:00 PM
72	2:20:00 PM
73	2:30:00 PM
74	2:30:00 PM
75	2:35:00 PM
76	2:35:00 PM
77	2:45:00 PM
78	2:45:00 PM
79	3:05:00 PM
80	3:10:00 PM
81	3:15:00 PM
82	3:15:00 PM
83	3:40:00 PM
84	4:00:00 PM
85	4:10:00 PM
86	4:15:00 PM
87	4:15:00 PM
88	4:15:00 PM
89	4:25:00 PM
90	4:30:00 PM
91	4:50:00 PM
92	5:30:00 PM
93	7:00:00 PM
Sheet2

Cell Formulas
Range	Formula
A2	A2	=Sheet1!B1
A3	A3	=Steeprock!D1
A4	A4	=SMALL(A5:A93,A2*24 + A3)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A66:A93	Cell Value	>Sheet1!$B$1	text	NO
A5:A65	Cell Value	>Sheet1!$B$1	text	NO

 

[awoohaw]

I was thinking, what If I figure out how to copy the columns all into 1 (probably on Sheet2) and then run the original formula you gave me

i'm not sure why you would not have that function. its been in 365 for 2 years at least. can you run a program update?

putting all on one sheet would work, i think. the problem with my original proposal was twofold. a blank resolves to 0, and in the small funtion that is one of the nth smallest values. (there is a workaround but the second problem complicates that). The second problem is the "starting point" time. I never could figure out how to get that to work when after that is started to be sure that the "requested number of employees" (cell D1) is accounted for - I could not get the calculation to use what remains when there was less than the number in cell D1. So by being sure you have LOTS of records, and no blanks my original should work. but, try it out.

ahi... I see you tried it out. Let me try to debug it.

 

[JOEE1979]

So I put a conditional format of the start times, is there a way to maybe work with that to make it easier?

Weekday Bus Cycling - Copy.xlsx
	B
1	Steeprock
2	10:05
3	31
4	7:25
5	2:25
6	2:25
7	2:25
8	2:51
9	3:15
10	3:25
11	4:05
12	4:05
13	4:20
14	4:20
15	4:25
16	4:39
17	4:50
18	4:55
19	4:55
20	5:05
21	5:05
22	5:05
23	5:10
24	5:20
25	5:25
26	5:25
27	5:25
28	5:40
29	5:40
30	5:55
31	6:00
32	6:00
33	6:10
34	6:20
35	6:25
36	6:25
37	6:30
38	6:30
39	6:35
40	6:45
41	6:52
42	6:55
43	7:05
44	7:05
45	7:25
46	7:35
47	9:05
48	10:05
49	10:40
50	10:45
51	11:35
52	11:50
53	12:00
54	12:02
55	12:05
56	12:19
57	12:20
58	12:25
59	12:50
60	12:55
61	13:10
62	13:20
63	13:30
64	13:35
65	13:55
66	14:00
67	14:05
68	14:10
69	14:15
70	14:15
71	14:20
72	14:20
73	14:30
74	14:30
75	14:35
76	14:35
77	14:45
78	14:45
79	15:05
80	15:10
81	15:15
82	15:15
83	15:40
84	16:00
85	16:10
86	16:15
87	16:15
88	16:15
89	16:25
90	16:30
91	16:50
92	17:30
93	19:00
Sheet2

Cell Formulas
Range	Formula
B2	B2	=MOD(NOW(),1)
B3	B3	=Steeprock!$L$1
B4	B4	=SMALL(B5:B93,B2*24 +B3)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B5:B104	Cell Value	>$B$2	text	NO