Surreybloke
Board Regular
- Joined
- Apr 1, 2010
- Messages
- 155
- Office Version
- 365
- Platform
- Windows
I've got a spreadsheet that adds a number, everytime it shows in another worksheet. I.e.
1 2 3 4 5 0 0 0 1 2 3 4 0 0 0 1 2 3 4 5 6 7 8 0 0 0 0 0 1 2 0
So each cell from the second column onwards is structured as follows:
=sum('Data'!A2)+A1 which in this case would give you 2 as the value in the second column, and so on and so on. I haven't been able to work out an alternative to the problem where when the next cell has a value of 0 and so repeats the final number again, other than by manually entering a 0 in the first cell where the repitition occurs.
My main query though is that for the work I'm doing, I need to calculate how many times someone has been off sick in various bands, i.e. 1 day, 2 days, 3 days, 4 days, 5 days +. A countif formula would obviously do the job, but you would then have multiple counting going on as I only want the highest number in each sequence to be counted, and not all the others preceding it. Is there a way of doing this in Excel so that in the case of the above sequence of numbers, I could report that this person was off sick for 5 days or more on 2 occasions?
1 2 3 4 5 0 0 0 1 2 3 4 0 0 0 1 2 3 4 5 6 7 8 0 0 0 0 0 1 2 0
So each cell from the second column onwards is structured as follows:
=sum('Data'!A2)+A1 which in this case would give you 2 as the value in the second column, and so on and so on. I haven't been able to work out an alternative to the problem where when the next cell has a value of 0 and so repeats the final number again, other than by manually entering a 0 in the first cell where the repitition occurs.
My main query though is that for the work I'm doing, I need to calculate how many times someone has been off sick in various bands, i.e. 1 day, 2 days, 3 days, 4 days, 5 days +. A countif formula would obviously do the job, but you would then have multiple counting going on as I only want the highest number in each sequence to be counted, and not all the others preceding it. Is there a way of doing this in Excel so that in the case of the above sequence of numbers, I could report that this person was off sick for 5 days or more on 2 occasions?
