+ Reply to Thread
Results 1 to 3 of 3

Travel allowance based on num of schools visited in a week, - but not the 1st of the day

  1. #1
    Registered User
    Join Date
    02-22-2013
    Location
    Lichfield, UK
    MS-Off Ver
    Excel 2003
    Posts
    12

    Travel allowance based on num of schools visited in a week, - but not the 1st of the day

    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!)
    Attached Files Attached Files
    Win 7, Office 2010 but save as 2003 for others

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Travel allowance based on num of schools visited in a week, - but not the 1st of the d

    Your workbook contains formulas with references to worksheets that are not in the workbook.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    02-22-2013
    Location
    Lichfield, UK
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Travel allowance based on num of schools visited in a week, - but not the 1st of the d

    Thanks Shg,

    Didn't want to burden folks with all the other stuff.
    Here is the full workbook

    Thanks for taking the time
    G

    PS, was expecting the post to appear in the formulas forum...Hmmmm
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1