Display 3 Minimum Time slots
 

I Have Data as Follows

A B C D E
Time 1st 2nd 3rd 4th
7:00 0 1
7:10 0 0
7:20 1 2
7:30 4 1
7:40 3 3
7:50 8 9
8:00 10 11
8:10 8 5
8:20 15 11
Etc

7:00, 7:10, 7:20 etc are clock time and 0 , 1 , 4 etc are No of Employees
who ented the gate.

I want to find out the 3 Clock time for each day where min Employees has
entered the gate for each day. want to Insert a Formula Below each Column.

Eg Sol. for Data Above (3 Least busy time Slots)

A B C
7:00 7:10
7:10 7:00
7:20 7:30

Basically I want to Know for each day, 3 Slots of time where entry in minimum.

Thanks

Display 3 Minimum Time slots
 

Have a look at the SMALL function in Excel Help.

Pete

On Mar 13, 1:25*pm, claude jerry
wrote:
I Have Data as Follows

A * * * * * *B * * *C * * * D * * * E *
Time * * 1st * *2nd * 3rd * * 4th
7:00 * * *0 * * * 1
7:10 * * *0 * * * 0
7:20 * * * 1 * * *2
7:30 * * *4 * * * 1
7:40 * * *3 * * * 3
7:50 * * *8 * * * 9
8:00 * * *10 * * 11
8:10 * * 8 * * * 5
8:20 * * 15 * * *11
Etc

7:00, 7:10, 7:20 etc are clock time and 0 , 1 , 4 etc are No of Employees
who ented the gate.

I want to find out the 3 Clock time for each day where min Employees has
entered the gate for each day. want to Insert a Formula Below each Column.

Eg Sol. for Data Above (3 Least busy time Slots)

A * * * *B * * * * * C * *
* * * * *7:00 * * * 7:10
* * * * *7:10 * * *7:00
* * * * *7:20 * * *7:30

Basically I want to Know for each day, 3 Slots of time where entry in minimum.

Thanks

Display 3 Minimum Time slots
 

Try this in B11, and copy down and across

=INDEX($A$2:$A$10,MATCH(SMALL(B$2:B$10+ROW($A$2:$A $10)*10^(-3),ROW(A1)),B$2:B$10+ROW($A$2:$A$10)*10^(-3),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

Note that you cannot use a whole column in array formulae (prior to excel
2007), but must use an explicit range.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"claude jerry" wrote in message
...
I Have Data as Follows

A B C D E
Time 1st 2nd 3rd 4th
7:00 0 1
7:10 0 0
7:20 1 2
7:30 4 1
7:40 3 3
7:50 8 9
8:00 10 11
8:10 8 5
8:20 15 11
Etc

7:00, 7:10, 7:20 etc are clock time and 0 , 1 , 4 etc are No of Employees
who ented the gate.

I want to find out the 3 Clock time for each day where min Employees has
entered the gate for each day. want to Insert a Formula Below each Column.

Eg Sol. for Data Above (3 Least busy time Slots)

A B C
7:00 7:10
7:10 7:00
7:20 7:30

Basically I want to Know for each day, 3 Slots of time where entry in
minimum.

Thanks

Display 3 Minimum Time slots
 

Thanks Bob. it works fine
Just a small question

What does *10^(-3), row() do ?

and what should I do to modify the formula say if I need 5 least time slots
insted of 3

Thanks

"Bob Phillips" wrote:

Try this in B11, and copy down and across

=INDEX($A$2:$A$10,MATCH(SMALL(B$2:B$10+ROW($A$2:$A $10)*10^(-3),ROW(A1)),B$2:B$10+ROW($A$2:$A$10)*10^(-3),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

Note that you cannot use a whole column in array formulae (prior to excel
2007), but must use an explicit range.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"claude jerry" wrote in message
...
I Have Data as Follows

A B C D E
Time 1st 2nd 3rd 4th
7:00 0 1
7:10 0 0
7:20 1 2
7:30 4 1
7:40 3 3
7:50 8 9
8:00 10 11
8:10 8 5
8:20 15 11
Etc

7:00, 7:10, 7:20 etc are clock time and 0 , 1 , 4 etc are No of Employees
who ented the gate.

I want to find out the 3 Clock time for each day where min Employees has
entered the gate for each day. want to Insert a Formula Below each Column.

Eg Sol. for Data Above (3 Least busy time Slots)

A B C
7:00 7:10
7:10 7:00
7:20 7:30

Basically I want to Know for each day, 3 Slots of time where entry in
minimum.

Thanks

Display 3 Minimum Time slots
 

I added the ROW($A$2:$A$10)*10^(-3) to cater for duplicates, otherwise 7:00
showed twice, so I added a small variable increment onto each value to
uniqify it.

If you want 5, just copy it down a further 2 rows.
--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"claude jerry" wrote in message
...
Thanks Bob. it works fine
Just a small question

What does *10^(-3), row() do ?

and what should I do to modify the formula say if I need 5 least time
slots
insted of 3

Thanks

"Bob Phillips" wrote:

Try this in B11, and copy down and across

=INDEX($A$2:$A$10,MATCH(SMALL(B$2:B$10+ROW($A$2:$A $10)*10^(-3),ROW(A1)),B$2:B$10+ROW($A$2:$A$10)*10^(-3),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets),
do
not try to do this manually.
When editing the formula, it must again be array-entered.

Note that you cannot use a whole column in array formulae (prior to excel
2007), but must use an explicit range.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"claude jerry" wrote in message
...
I Have Data as Follows

A B C D E
Time 1st 2nd 3rd 4th
7:00 0 1
7:10 0 0
7:20 1 2
7:30 4 1
7:40 3 3
7:50 8 9
8:00 10 11
8:10 8 5
8:20 15 11
Etc

7:00, 7:10, 7:20 etc are clock time and 0 , 1 , 4 etc are No of
Employees
who ented the gate.

I want to find out the 3 Clock time for each day where min Employees
has
entered the gate for each day. want to Insert a Formula Below each
Column.

Eg Sol. for Data Above (3 Least busy time Slots)

A B C
7:00 7:10
7:10 7:00
7:20 7:30

Basically I want to Know for each day, 3 Slots of time where entry in
minimum.

Thanks