Number query

Surreybloke

Board Regular
Joined
Apr 1, 2010
Messages
145
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?
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,079
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Wouldn't
=COUNTIF(range, 5)
give you that?
 

Surreybloke

Board Regular
Joined
Apr 1, 2010
Messages
145
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,079
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

Board Regular
Joined
Apr 1, 2010
Messages
145

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,079
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
but you also have sequences of 1, 1,2 and 1,2,3 - do they not count for anything?
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

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

Board Regular
Joined
Apr 1, 2010
Messages
145
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

Board Regular
Joined
Apr 1, 2010
Messages
145
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,670
Messages
5,597,480
Members
414,143
Latest member
lonnie451

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top