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

1. ## 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

3. ## 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.

4. ## 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. ## 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.