Index + Match Help

Carlito

New Member

I have 4 columns of timetable data: room number, weekday, start time, end time. I have a separate table with weekday across the top row, hour slot (09:00, 10:00, 11:00...) on the next row and the list of room numbers in a column down the left hand side. I need to write a formula for the table area that will interrogate the source data and return '1' if a room is in use during an hour slot and '0' if it isn't.

I have cobbled together an INDEX + MATCH formula that works as long as the 'room use time' begins on the hour, but it falls down when half-hours are involved (as the match isn't exact). I suspect that I need to nest an IF statement that checks whether the room use time falls between time A + time B, but I'm stumped if I can write it correctly.

Many thanks for any help, and let me know if more info is required.

Hui

Excel Ninja
Staff member
Carlito

Can you post an example file somewhere?

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook

Carlito

New Member
Ah yeah, that might help! A sample data file is here: http://www.speedyshare.com/files/25045954/Timetable_File.xls

The source data is on the left hand side of the sheet, and the table I want to populate is on the right. I have left an attempted formula in cell H3.

Each row of the source data represents a session in a room, with start and end times in columns C + D. I need a formula for the table on the right that returns '1' for each timeslot where a room is in use. The only other caveat is that when a session starts on the half hour it isn't counted (so 11:30 doesn't count against the 11am timeslot), but it counts for the next timeslot (11:30 to 12:30 counts against 12:00 timeslot).

Cell H3 should be 0, I3 1, J3 0, K3 1, L3 1.

I hope my explanation is clear enough to follow, and enormous thanks to anyone who can help!

Hui

Excel Ninja
Staff member
Carlito

In C2:D33 I changed the Text to Times

In H2:Au2 I changed the Text to Times

I setup 4 Named Ranges

[pre]
Code:
``````Room	=OFFSET(Timetable!\$A\$1,1,0,COUNTA(Timetable!\$A:\$A)-1,1)
Day	=OFFSET(Timetable!\$B\$1,1,0,COUNTA(Timetable!\$B:\$B)-1,1)
Start	=OFFSET(Timetable!\$C\$1,1,0,COUNTA(Timetable!\$C:\$C)-1,1)
End	=OFFSET(Timetable!\$D\$1,1,0,COUNTA(Timetable!\$D:\$D)-1,1)[/pre]
and then in

H3: =SUMPRODUCT((Room=\$G3)*(Day=H\$1)*(End&#62;=H\$2+(1/48))*(End&#60;=I\$2))``````

Copy H3 across and down to AU5

Carlito

New Member
Hui

Thanks for the help. It doesn't seem to give the results I'm after, though: cells H3-M3 give 0, 1, 1, 2, 3, 4. The only numbers returned should be 1 and 0.

I have defined the named ranges you list, and formatted the time cells as hh:mm. Is there something silly I'm missing? (quite possible)

Hui

Excel Ninja
Staff member
I'm getting

'H3:M3 0,1,0,1,1,1'

Formating the Times doesn't change the contents from Text to Times (Numbers), it only changes how they're displayed

You originally had the times saved as Text values

They need to be numbers as your doing maths on them

I retyped all your times as 10:00 11:00 etc, sorry I should have been clearer

I have uploaded my file with changes to:

http://rapidshare.com/files/429402446/Carlito.xls

Carlito

New Member
Aha, that was it. Works perfectly now! Thanks so much for all your help. Much appreciated.