Results 1 to 5 of 5

20061128, 08:11 #1
 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
BraddyIf you are a fool at forty, you will always be a fool

20061128, 12:16 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Help to understand a formula (Excel 2002/2003)
Please post the workbook.

20061128, 13:38 #3
 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.
BraddyIf you are a fool at forty, you will always be a fool

20061128, 14:20 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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 onsite time. So if total time (travel+onsite) 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.

20061128, 14:36 #5
 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.
BraddyIf you are a fool at forty, you will always be a fool