Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to lookup the minimum, 2nd minimum and 3rd minimum......... | Excel Worksheet Functions | |||
Time display | Excel Discussion (Misc queries) | |||
Time Display ? | Excel Worksheet Functions | |||
Formula for placings, using 4 times and greater than minimum time | Excel Worksheet Functions | |||
counting time slots | Excel Worksheet Functions |