Number query

[Surreybloke]

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?

 

[RoryA]

Wouldn't
=COUNTIF(range, 5)
give you that?

 

[Surreybloke]

As I mentioned though, I need to count the number of occurences of the maximum number in each sequence only. So where you have 1, 2, 3, 4 ,5, in my bands where I want to count the number of times someone has been off sick for 1 day only, 2 days only, 3 days only, 4 days only, or more than 5 days, if I just used COUNTIF in each of my ranges, it would return a 1 for each day of sickness.

So to summarise, this is what I need to be able to show:

1 2 3 4 5 0 0 1 0 0 1 2 3 0 0 0 0 0 0 1 2 0 0 0

So each value of more than 0 indicates a day of sickness, and where they are off for more than one day in a row, the value will go up by 1.

The reporting part needs to show as follows:

1 day off: 0
2 days off: 0
3 days off: 0
4 days off: 0
5+ days off: 1

 

[RoryA]

Why are 2 and 3 days off both 0, not 1?
The 5 was specifically for more than 5, for which the countif would work. For the others you'd need a sumproduct I think, dependent on your answer to my question.

 

[Surreybloke]

They are zero because the final number of days taken off was 5. I only want to show the fact that they took 5 days off in a row. Its therefore not necessary to include it within the other criteria for 4 days off, 3 days off, 2 days off and 1 day off because I am counting it just the once in the 5 days or more off criteria.

 

[RoryA]

but you also have sequences of 1, 1,2 and 1,2,3 - do they not count for anything?

 

[Andrew Poulsom]

With your data in A1:X1 in A3:A7 enter the numbers 1 to 5. Formula in B3:

=SUMPRODUCT(--(A$1:W$1=A3),--(B$1:X$1=0))

copied to B4:B6. Formula in B7:

=SUMPRODUCT(--(A$1:W$1=A7),--(B$1:X$1=0))

But like Rory, I don't agree with your expected results.

 

[Surreybloke]

but you also have sequences of 1, 1,2 and 1,2,3 - do they not count for anything?

I was only looking at the first part of the overall sequence to demonstrate what I'm wanting to eventually get. Those other parts of the sequence would of course fall into the other criteria.

 

[Surreybloke]

The overall summary would show:

1 day: 1
2 days: 1
3 days: 1
4 days: 0
5+ days: 1

As I mentioned though, I need to count the number of occurences of the maximum number in each sequence only. So where you have 1, 2, 3, 4 ,5, in my bands where I want to count the number of times someone has been off sick for 1 day only, 2 days only, 3 days only, 4 days only, or more than 5 days, if I just used COUNTIF in each of my ranges, it would return a 1 for each day of sickness.

So to summarise, this is what I need to be able to show:

1 2 3 4 5 0 0 1 0 0 1 2 3 0 0 0 0 0 0 1 2 0 0 0

So each value of more than 0 indicates a day of sickness, and where they are off for more than one day in a row, the value will go up by 1.

The reporting part needs to show as follows:

1 day off: 0
2 days off: 0
3 days off: 0
4 days off: 0
5+ days off: 1

 

[Andrew Poulsom]

The overall summary would show:

1 day: 1
2 days: 1
3 days: 1
4 days: 0
5+ days: 1

That's what my formulas return.