Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Multiple IF factors
I have a spreadsheet with
A - Day of week B - Time of Day C - etc - Employee names Day Time Amy P Angel M Chad S Deborah J Monday 6:00 AM AUX Monday 6:15 AM AUX Monday 6:30 AM AUX Monday 6:45 AM AUX Monday 7:00 AM AUX Monday 7:15 AM AUX Monday 7:30 AM Phone AUX Monday 7:45 AM Phone AUX Monday 8:00 AM Phone Break Monday 8:15 AM Phone AUX Monday 8:30 AM Aux Phone AUX Monday 8:45 AM AUX Phone AUX Monday 9:00 AM AUX Phone AUX Monday 9:15 AM AUX Phone Phone Monday 9:30 AM AUX Break Phone Monday 9:45 AM AUX Phone Phone Rows indicate what job function they are doing each 15 minutes throughout the day. I need to create a formula that will tell me which employees are doing a particular job function (break, lunch, etc), on a specific day (Tues) for a certain time (9:15 am) I have a separate worksheet started with Day of week, job function and time and list each employee under it, Monday Phones 10:00 AM Amy P ????? Angel M ????? Chad S ????? Deborah J ??? Can anyone help me? |
#2
|
|||
|
|||
Hi!
This solution is based on your sample data as posted. Assume your table is on Sheet1 A1:F17. On Sheet2 you have in: A1 = Monday A2 = Phone A3 = 9:45 AM A4 = (blank) In A5 enter this formula with the key combo of CTRL,SHIFT,ENTER: =INDEX(Sheet1!C$1:F$1,SMALL(IF((Sheet1!A$2:A$17=A$ 1)* (Sheet1!B$2:B$17=A$3)*(Sheet1!C$2:F$17=A$2),COLUMN (A:D)),ROW(1:1))) Copy down until you get #NUM! errors. This formula will return: A5 = Angel M A6 = Deborah J A7 = #NUM! You can suppress the #NUM! errors by using an error trap in the formula but that will make the formula twice as long: =IF(ISERROR(SMALL(IF((Sheet1!A$2:A$17=A$1)*(Sheet1 ! B$2:B$17=A$3)*(Sheet1!C$2:F$17=A$2),COLUMN(A:D)),R OW (1:1))),"",INDEX(Sheet1!C$1:F$1,SMALL(IF((Sheet1! A$2:A$17=A$1)*(Sheet1!B$2:B$17=A$3)*(Sheet1! C$2:F$17=A$2),COLUMN(A:D)),ROW(1:1)))) An alternative is to use the shorter formula and then use conditional formatting to hide the errors. Example: If you would normally expect to have 5 employees that meet the criteria of Monday, Phone, 9:45 AM, then you would want to copy the formula to AT LEAST 5 cells, So: Select the range A5:A9 Goto FormatConditional Formatting Formula is: =ISERROR(A5) Click the Format button. Set the font color to be the same as the fill color. OK out. You could also make the formula much shorter by using defined named ranges. Biff -----Original Message----- I have a spreadsheet with A - Day of week B - Time of Day C - etc - Employee names Day Time Amy P Angel M Chad S Deborah J Monday 6:00 AM AUX Monday 6:15 AM AUX Monday 6:30 AM AUX Monday 6:45 AM AUX Monday 7:00 AM AUX Monday 7:15 AM AUX Monday 7:30 AM Phone AUX Monday 7:45 AM Phone AUX Monday 8:00 AM Phone Break Monday 8:15 AM Phone AUX Monday 8:30 AM Aux Phone AUX Monday 8:45 AM AUX Phone AUX Monday 9:00 AM AUX Phone AUX Monday 9:15 AM AUX Phone Phone Monday 9:30 AM AUX Break Phone Monday 9:45 AM AUX Phone Phone Rows indicate what job function they are doing each 15 minutes throughout the day. I need to create a formula that will tell me which employees are doing a particular job function (break, lunch, etc), on a specific day (Tues) for a certain time (9:15 am) I have a separate worksheet started with Day of week, job function and time and list each employee under it, Monday Phones 10:00 AM Amy P ????? Angel M ????? Chad S ????? Deborah J ??? Can anyone help me? . |
#3
|
|||
|
|||
Thanks Biff, but I still have something wrong as I can't get the data to
fill is after 10:00 am. I have 286 rows of info and Columns thru "S" The formula I used was: =IF(ISERROR(SMALL(IF((Schedule!A$2:A$286=A$1)*(Sch edule!B$2:B$286=A$3)*(Schedule!C$2:S$286=A$2),Colu mn(A:Q)),ROW(1:1))),"",INDEX(Schedule!C$1:S$1,SMAL L(IF((Schedule!A$2:A$286=A$1)*(Schedule!B$2:B$286= A43)*(Schedule!C$2:S$286=A$2),Column(A:Q))),Row(1: 1)))) Can you see what I've done wrong? Thanks for your help! I do appreciate it very much. Kamille |
#4
|
|||
|
|||
Hi!
The formula looks OK. I created a test file the size that you mentioned: 286 rows by 19 columns. Times start at 6:00 AM to 8:00 PM in 15 min increments for Monday through Friday. Randomly filled the table with "phone", "aux", and "break". It works for my test file. No problems! There's nothing wrong with the formula you're using. I'm using the same EXACT formula. Are you sure that 10:00 AM is entered as a TRUE time? Would you like to see my test file? Biff -----Original Message----- Thanks Biff, but I still have something wrong as I can't get the data to fill is after 10:00 am. I have 286 rows of info and Columns thru "S" The formula I used was: =IF(ISERROR(SMALL(IF((Schedule!A$2:A$286=A$1)*(Sc hedule! B$2:B$286=A$3)*(Schedule!C$2:S$286=A$2),Column(A:Q )),ROW (1:1))),"",INDEX(Schedule!C$1:S$1,SMALL(IF((Schedu le! A$2:A$286=A$1)*(Schedule!B$2:B$286=A43)*(Schedule! C$2:S$286=A$2),Column(A:Q))),Row(1:1)))) Can you see what I've done wrong? Thanks for your help! I do appreciate it very much. Kamille . |
#5
|
|||
|
|||
Nope, re-formated my time fields and it's working great now.
Thanks again for all your help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I get the mode, min, and max with multiple criteria? | Excel Discussion (Misc queries) | |||
adding certain cells in multiple worksheets in multiple workbooks | Excel Worksheet Functions | |||
XML / parent with multiple children and with multiple children | Excel Discussion (Misc queries) | |||
How do I avoid saving multiple Excel/Wordfiles for versioning purp | Excel Discussion (Misc queries) | |||
multiple entries | Excel Worksheet Functions |