Struggling with Excel formula

  cedricthecat 12:32 13 Aug 07
Locked

I'm trying to get my head round a formula, but I just can't do it!

I'm recording sick leave at work, and need to have information on both the number of days off, and the number of episodes (so 5 days off in one go would be 1 episode)

I'm recording the sick days in Column E, as:-

0
0.5 (half a day)
1 (full day)

In Column K I have a total for episodes, for which I created this formula (example for Row 25)

=IF(K24=1,0,IF(E24>=0.4,0,IF(E25>=0.4,1,0)))

This basically marks Column K with a 1 every time a new episode starts, which can then be used for a total in the final row of the spreadsheet.

However, I've also got a sheet on the go for keeping track of these episodes over a rolling year, (thanks VoG!) and I've now realised that it would be far more pertinent to have the episodes marked on the last day of the sickness period, not the first day!

I just can't get my head round how to do this! Can anyone help at all?!

  Picklefactory 12:51 13 Aug 07

Try
=IF(AND(E7>0,E8=0),1,0) for row 7, click and drag

  cedricthecat 13:05 13 Aug 07

Blimey!

Quick, neat and it works!

Many thanks!!

  Picklefactory 13:13 13 Aug 07

You're welcome, I can only assume VoG™ has popped to the loo, as he didn't beat me to that one by a country mile.

  cedricthecat 13:28 13 Aug 07

"You're welcome, I can only assume VoG™ has popped to the loo, as he didn't beat me to that one by a country mile"

Brilliant!

This thread is now locked and can not be replied to.

Surface Pro (2017) vs Surface Pro 4

Where HTML5 is headed next

MacBook Pro v Surface Pro 5