Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Help to understand a formula (Excel 2002/2003)

    Hi

    I would really appreciate some help trying to understand the formula below>

    =IF($Q$23+$T$23<=8,0,IF(SUM(CELL("CONTENTS",OFFSET ($U$7,COUNT($S$8:$S$18),-2))-$S$7)*24<=8,0,($Q$23+$T$23)-(8+CELL("CONTENTS",OFFSET($S$7,COUNT($Q$8:$Q$19),-2)))))

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help to understand a formula (Excel 2002/2003)

    Please post the workbook.

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Help to understand a formula (Excel 2002/2003)

    Hi Hans

    Thank you for your response, I have had to delete a few days to get it down to size, I have marked the cell yellow on the last day, to show where the fomula is.


    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help to understand a formula (Excel 2002/2003)

    Let's take the formula apart step by step. The formula is basically

    =IF(Q23+T23<8,0,something)

    Q23 contains travel time and T23 contains on-site time. So if total time (travel+on-site) is less than 8 hours, return 0. Else return

    IF(SUM(CELL("CONTENTS",OFFSET($U$7,COUNT($S$8:$S$1 8),-2))-$S$7)*24<=8,0,($Q$23+$T$23)-(8+CELL("CONTENTS",OFFSET($S$7,COUNT($Q$8:$Q$19),-2))))

    I'm not sure why SUM and CELL are used here, they appear to make the formula more complicated than necessary.

    COUNT($S$8:$S$18) is the number of populated cells (with numbers) in S8:S18. This is 4. OFFSET($U$7,COUNT($S$8:$S$18),-2)) is the cell 4 rows down and 2 columns to the left of U7, i.e. the last departure time. The start of day in S7 is subtracted from this time; if the difference is less than 8 hours, 0 is returned. Otherwise, OFFSET($S$7,COUNT($Q$8:$Q$19),-2) is the last travel time. Total time is taken and 8 hours plus the last travel time is subtracted.

    So it appears to be some kind of overtime calculation. As far as I can tell, it can be replaced with the much simpler formula

    =MAX(24*(MAX(S8:S18)-S7)-8,0)

    MAX(S8:S18) is the last departure time. Start of day is subtracted, and the difference is multiplied with 24 to give a number of hours. Regular work time of 8 is subtracted, and if the difference is negative (i.e. less than 8 hours worked), 0 is returned.

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Help to understand a formula (Excel 2002/2003)

    Hi Hans

    I have just inherited this file from someone else.

    Thank you so much for your explantion and the alternative shortened formula.

    I am as usual in your debt.

    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

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