Thank you for taking the time to read this post.
I have a school timetable for a member of our peripatetic staff.
Each teacher is allowed 10 minutes travel time 'between' schools (therefore not the 1st of the day).
My formula identifies the unique days each teacher teaches (some are part time), but for the life of me I can't figure out how to accommodate the varying timetable discrepancies.
So far...
=((L4-SUM(IF(
FREQUENCY(IF('TT 2013-14'!$B2:B319=B$3,IF('TT 2013-14'!C2:C319<>"",MATCH('TT 2013-14'!C2:C319,'TT 2013-14'!C2:C319,0))),ROW('TT 2013-14'!C2:C319)-ROW(B3)+1),1)))*Setup!J5)
Where L4 is the number of schools, and the frequency calculates how many days they work. SetupJ5 is the travel time allowance (10 minutes)
In English the formula should be:
Calculate the number of days the teacher teaches. If the teacher teaches more than 1 school a day, allow 10 minutes travel time for each.
I have attached the master info sheet from which all other sheets derive their data (therefore the formula I need will not be on the sheet accompanying this post)
Any help in pushing me in the right direction would be most welcome.
Thanks
Glen
(PS, although I have used the 'frequency' function, I am not at all confident in my understanding of it (even though my development formula works as intended!)
Bookmarks