=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]

if you need this per location then you need to build it one column at a time and not on the entire matrix.
=SMALL(D$4:D$18, $F$2*24 + $F$1) and drag the formula across.

if that doesn't work, please mock up an example of what you want to see (just one or two columns).

if you need the calculation to work for the entire matrix, please tell what is wrong with the calculation?
(it may be wrong because a blank gets resolved to 0 which would be part of the small counts. if this is the case it is VERY important you give a complete description of what the matrix looks like and some examples of expected results.

I wanted it to work on multiple locations but 1 cell would do all the calculation, this way I can spread out the employees

 

[awoohaw]

to do for each location, insert a row below row 2
Use the first formula above and drag it over (make sure your cell references are proper).

 

[awoohaw]

to do for each location, insert a row below row 2
Use the first formula above and drag it over (make sure your cell references are proper).

Insert a new row 3.
move formula from D2 down to D3
(it should be: =SMALL(D$4:D$18,$F$2*24 +$F$1))
then drag D3 accros to the last column.
But, again. need to fix the blanks. I'll post something for that in a few minutes.

 

[JOEE1979]

Insert a new row 3.
move formula from D2 down to D3
(it should be: =SMALL(D$4:D$18,$F$2*24 +$F$1))
then drag D3 accros to the last column.
But, again. need to fix the blanks. I'll post something for that in a few minutes.

Ok I will wait for your response, thank you kindly

 

[awoohaw]

it is a little more wacky than I imagined. Selecting the start time and using small to figure things out is using two different processes to get data, I'm crunching on it!

 

[JOEE1979]

it is a little more wacky than I imagined. Selecting the start time and using small to figure things out is using two different processes to get data, I'm crunching o

I've been trying to figure it out myself, from what I've been seeing is that if the cells are blank, the formula will not calculate properly.
Problem is, for some reason, this is how HR has given me the schedule. It has a reason, but not for my department.

 

[JOEE1979]

Nope I'm wrong, It dont work even with the empty boxes filled

 

[awoohaw]

okay, try this. It requires Excel 365:

mr excel questions 12.xlsm
	A	B	C	D	E	F	G	H
1			Employees	6
2			Start	02:00:00
3		10:00:00	10:30:00	13:45:00	13:15:00	05:00:00	13:30:00
4		Location 1	Location 2	Location 3	Location 4	Location 5	Location 6
5	1	01:00:00	07:45:00	01:45:00	01:15:00	01:30:00	08:15:00
6	2	02:00:00	08:45:00	02:45:00	02:15:00	02:30:00
7	3	03:00:00	09:45:00			03:30:00	14:00:00
8	4	04:00:00		11:00:00		04:30:00	15:00:00
9	5	05:00:00			05:15:00	05:30:00	16:00:00
10	6	06:00:00	12:45:00	06:45:00	06:15:00	06:30:00
11	7		07:30:00	13:00:00	15:45:00
12	8		08:30:00		16:45:00		03:15:00
13	9						04:15:00
14	10	10:00:00	10:30:00			03:00:00
15	11	11:00:00		17:00:00	11:15:00	04:00:00
16	12	12:00:00		09:30:00	12:15:00	05:00:00	11:30:00
17	13	13:00:00		13:45:00	13:15:00	06:00:00	12:30:00
18	14	14:00:00	14:30:00	14:45:00	14:15:00	07:00:00	13:30:00
19	15	15:00:00	15:30:00		15:15:00
Sheet8 (2)

Cell Formulas
Range	Formula
B3:G3	B3	=SMALL(FILTER(B$5:B$19,B$5:B$19>=$D$2,""),MIN(COUNT(FILTER(B$5:B$19,B$5:B$19>=$D$2,"")),$D$1))
B5:B10,B14:B19	B5	=TIME(A5,0,0)

 

[JOEE1979]

okay, try this. It requires Excel 365:

mr excel questions 12.xlsm
	A	B	C	D	E	F	G	H
1			Employees	6
2			Start	02:00:00
3		10:00:00	10:30:00	13:45:00	13:15:00	05:00:00	13:30:00
4		Location 1	Location 2	Location 3	Location 4	Location 5	Location 6
5	1	01:00:00	07:45:00	01:45:00	01:15:00	01:30:00	08:15:00
6	2	02:00:00	08:45:00	02:45:00	02:15:00	02:30:00
7	3	03:00:00	09:45:00			03:30:00	14:00:00
8	4	04:00:00		11:00:00		04:30:00	15:00:00
9	5	05:00:00			05:15:00	05:30:00	16:00:00
10	6	06:00:00	12:45:00	06:45:00	06:15:00	06:30:00
11	7		07:30:00	13:00:00	15:45:00
12	8		08:30:00		16:45:00		03:15:00
13	9						04:15:00
14	10	10:00:00	10:30:00			03:00:00
15	11	11:00:00		17:00:00	11:15:00	04:00:00
16	12	12:00:00		09:30:00	12:15:00	05:00:00	11:30:00
17	13	13:00:00		13:45:00	13:15:00	06:00:00	12:30:00
18	14	14:00:00	14:30:00	14:45:00	14:15:00	07:00:00	13:30:00
19	15	15:00:00	15:30:00		15:15:00
Sheet8 (2)

Cell Formulas
Range	Formula
B3:G3	B3	=SMALL(FILTER(B$5:B$19,B$5:B$19>=$D$2,""),MIN(COUNT(FILTER(B$5:B$19,B$5:B$19>=$D$2,"")),$D$1))
B5:B10,B14:B19	B5	=TIME(A5,0,0)

For some reason, my 365 dont have "FILTER" function

 

[JOEE1979]

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