Struggling with Excel formula

  cedricthecat 12:32 13 Aug 2007
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 2007

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

  cedricthecat 13:05 13 Aug 2007

Blimey!

Quick, neat and it works!

Many thanks!!

  Picklefactory 13:13 13 Aug 2007

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 2007

"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.

Elsewhere on IDG sites

The Evil Within 2 review-in-progress

Adobe shows still-in-development tools, including automatically colourising black-and-white photos

iPhone X news: Release date, price, new features & specs

Comment transformer un iPhone en borne Wi-Fi ?