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

 

[awoohaw]

what is the value in cell D1 supposed to be? If you want it to be Ten Minutes, you need to recalculate it. As the value is 10 days (Jan 10, 1900). So your comparison of C3:C19,">"&D1 is comparing time values in day one with day 10.

Or, if D1 is the [k] for the small function, why are you adding D2 (hours) to it in C2?

 

[JOEE1979]

what is the value in cell D1 supposed to be? If you want it to be Ten Minutes, you need to recalculate it. As the value is 10 days (Jan 10, 1900). So your comparison of C3:C19,">"&D1 is comparing time values in day one with day 10.

Or, if D1 is the [k] for the small function, why are you adding D2 (hours) to it in C2?

"D1" is the number of workers I have that day.
"D2" is the time I need the workers to go on shift that day.
Each worker works for 1 hour
So I need "C2" to tell me the time I have workers until.

To go back to the example I gave in post 1, the shift that day starts at 4AM, I have 10 workers, I'm good until 1PM

"D1" and "D2" are variables and change every day

Hope that helps

Thanks

 

[awoohaw]

Yes, it explains the requirements better. Although I don't know why you are putting the time component variable into your [k] value of the small operation.

 

[JOEE1979]

Yes, it explains the requirements better. Although I don't know why you are putting the time component variable into your [k] value of the small operation.

thats where my excel knowledge is limited, and I need help pleaase

 

[awoohaw]

I don't think you need to do the count function at all. If you convert your start time to a decimal (in this case 4:00 am) and add your number of employees you get the nth smallest hour you seek. I think. 4:00 am * 24 = 4, add 10 to that and you get the 14th as smallest.

=SMALL(C3:C19,D2*24 +10)
=SMALL(C3:C19,D2*24 +D1)

 

[awoohaw]

I don't think you need to do the count function at all. If you convert your start time to a decimal (in this case 4:00 am) and add your number of employees you get the nth smallest hour you seek. I think. 4:00 am * 24 = 4, add 10 to that and you get the 14th as smallest.

=SMALL(C3:C19,D2*24 +10)
=SMALL(C3:C19,D2*24 +D1)

you should press F4 on D2 and D1 in the formulas to make the references absolute, and possibly for C3:C19 as well (if you drag/copy the formula).

 

[awoohaw]

Book1
	B	C	D	E
1		10:00:00	Employees for the day:	10
2		14:00:00	Start of Day:	04:00:00
3	1	Jan 1900 01 00 01 00
4	2	Jan 1900 01 00 02 00
5	3	Jan 1900 01 00 03 00
6	4	Jan 1900 01 00 04 00
7	5	Jan 1900 01 00 05 00
8	6	Jan 1900 01 00 06 00
9	7	Jan 1900 01 00 07 00
10	8	Jan 1900 01 00 08 00
11	9	Jan 1900 01 00 09 00
12	10	Jan 1900 01 00 10 00
13	11	Jan 1900 01 00 11 00
14	12	Jan 1900 01 00 12 00
15	13	Jan 1900 01 00 13 00
16	14	Jan 1900 01 00 14 00
17	15	Jan 1900 01 00 15 00
18	16	Jan 1900 01 00 16 00
19	17	Jan 1900 01 00 17 00
Sheet5

Cell Formulas
Range	Formula
C1	C1	=SMALL(C3:C19,10)
C2	C2	=SMALL(C3:C19,$E$2*24 +$E$1)

 

[JOEE1979]

IT worked for a single column, but when I changed the sheet to make it 6 locations, it stopped working

Book1
	C	D	E	F	G	H	I
1			Employees	10
2		4:15:00 AM	Start	4:00:00 AM
3		Location 1	Location 2	Location 3	Location 4	Location 5	Location 6
4	1.0	1:00:00 AM	7:45:00 AM	1:45:00 AM	1:15:00 AM	1:30:00 AM	8:15:00 AM
5	2.0	2:00:00 AM	8:45:00 AM	2:45:00 AM	2:15:00 AM	2:30:00 AM
6	3.0		9:45:00 AM			3:30:00 AM	2:00:00 PM
7	4.0	3:45:00 AM		11:00:00 AM		4:30:00 AM	3:00:00 PM
8	5.0	4:45:00 AM			5:15:00 AM	5:30:00 AM	4:00:00 PM
9	6.0	5:45:00 AM	12:45:00 PM	6:45:00 AM	6:15:00 AM	6:30:00 AM
10	7.0		7:30:00 AM	1:00:00 PM	3:45:00 PM
11	8.0	8:00:00 AM	8:30:00 AM		4:45:00 PM		3:15:00 AM
12	9.0	9:00:00 AM					4:15:00 AM
13	10.0	10:00:00 AM	10:30:00 AM			3:00:00 AM
14	11.0			5:00:00 PM	11:15:00 AM	4:00:00 AM
15	12.0	12:00:00 PM		9:30:00 AM	12:15:00 PM	5:00:00 AM	11:30:00 AM
16	13.0	7:15:00 AM		1:45:00 PM	1:15:00 PM	6:00:00 AM	12:30:00 PM
17	14.0		2:30:00 PM	2:45:00 PM	2:15:00 PM	7:00:00 AM	1:30:00 PM
18	15.0	9:15:00 AM	3:30:00 PM		3:15:00 PM
Sheet1

Cell Formulas
Range	Formula
D2	D2	=SMALL(D4:I18,$F$2*24 +$F$1)

 

[awoohaw]

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.