| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count the number of Instances
I have a spread sheet that records sickness levels, what I'm after is a
formula or maybe even a macro that will return the about of sick instances plus amount of days. The problem is that it needs to somehow have a bit that due to shift patterns of 4 on 4 off reconises that if the gap is more than 5 days part from the last instance then it is a new instance if it is less than 5 then it's the same instance Example At the start of D3 running right out are the days of the month starting from Jan 01 to Dec 31st In A4 down to A100 are a list of names If i use January as an example i need the answer to come to 3 instances and a total of 12 days off Cell D4 = s D5 = s D6 = s D7 = blank D8 = Blank D9 = blank D10 = Blank D11 = Blank D12 = Blank D13 = Blank D14 = Blank D15 = s D16 = Blank D17 = Blank D18 = Blank D19 = Blank D20 = s D21 = s D22 = s D23 = s D24 = blank D25 = blank D26 = blank D27 = blank D28 = s D29 = s D30 = s D31 = s Hope this makes sense |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count the number of Instances
I think maybe you oversimplify, for instance if someone is sick the
first day and the last day of the shift cycle, then there are 2 instances, but the "2 day gap" would call it one instance. louiscourtney wrote: I have a spread sheet that records sickness levels, what I'm after is a formula or maybe even a macro that will return the about of sick instances plus amount of days. The problem is that it needs to somehow have a bit that due to shift patterns of 4 on 4 off reconises that if the gap is more than 5 days part from the last instance then it is a new instance if it is less than 5 then it's the same instance Example At the start of D3 running right out are the days of the month starting from Jan 01 to Dec 31st In A4 down to A100 are a list of names If i use January as an example i need the answer to come to 3 instances and a total of 12 days off Cell D4 = s D5 = s D6 = s D7 = blank D8 = Blank D9 = blank D10 = Blank D11 = Blank D12 = Blank D13 = Blank D14 = Blank D15 = s D16 = Blank D17 = Blank D18 = Blank D19 = Blank D20 = s D21 = s D22 = s D23 = s D24 = blank D25 = blank D26 = blank D27 = blank D28 = s D29 = s D30 = s D31 = s Hope this makes sense |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count the number of Instances
Slightly confused as I thought dates went ACROSS a row (per person), not down
a column as your example indicates (to me). "louiscourtney" wrote: I have a spread sheet that records sickness levels, what I'm after is a formula or maybe even a macro that will return the about of sick instances plus amount of days. The problem is that it needs to somehow have a bit that due to shift patterns of 4 on 4 off reconises that if the gap is more than 5 days part from the last instance then it is a new instance if it is less than 5 then it's the same instance Example At the start of D3 running right out are the days of the month starting from Jan 01 to Dec 31st In A4 down to A100 are a list of names If i use January as an example i need the answer to come to 3 instances and a total of 12 days off Cell D4 = s D5 = s D6 = s D7 = blank D8 = Blank D9 = blank D10 = Blank D11 = Blank D12 = Blank D13 = Blank D14 = Blank D15 = s D16 = Blank D17 = Blank D18 = Blank D19 = Blank D20 = s D21 = s D22 = s D23 = s D24 = blank D25 = blank D26 = blank D27 = blank D28 = s D29 = s D30 = s D31 = s Hope this makes sense |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count the number of Instances
Sorry Toppers you are right it should have been D4 E4 F4 G4 H4 I4 etc etc
Would be great if you can help "Toppers" wrote: Slightly confused as I thought dates went ACROSS a row (per person), not down a column as your example indicates (to me). "louiscourtney" wrote: I have a spread sheet that records sickness levels, what I'm after is a formula or maybe even a macro that will return the about of sick instances plus amount of days. The problem is that it needs to somehow have a bit that due to shift patterns of 4 on 4 off reconises that if the gap is more than 5 days part from the last instance then it is a new instance if it is less than 5 then it's the same instance Example At the start of D3 running right out are the days of the month starting from Jan 01 to Dec 31st In A4 down to A100 are a list of names If i use January as an example i need the answer to come to 3 instances and a total of 12 days off Cell D4 = s D5 = s D6 = s D7 = blank D8 = Blank D9 = blank D10 = Blank D11 = Blank D12 = Blank D13 = Blank D14 = Blank D15 = s D16 = Blank D17 = Blank D18 = Blank D19 = Blank D20 = s D21 = s D22 = s D23 = s D24 = blank D25 = blank D26 = blank D27 = blank D28 = s D29 = s D30 = s D31 = s Hope this makes sense |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count the number of Instances
Too late here in UK to start on this (!) as I think it will need a macro to
count the instances. I'll try and look tomorrow evening. "louiscourtney" wrote: Sorry Toppers you are right it should have been D4 E4 F4 G4 H4 I4 etc etc Would be great if you can help "Toppers" wrote: Slightly confused as I thought dates went ACROSS a row (per person), not down a column as your example indicates (to me). "louiscourtney" wrote: I have a spread sheet that records sickness levels, what I'm after is a formula or maybe even a macro that will return the about of sick instances plus amount of days. The problem is that it needs to somehow have a bit that due to shift patterns of 4 on 4 off reconises that if the gap is more than 5 days part from the last instance then it is a new instance if it is less than 5 then it's the same instance Example At the start of D3 running right out are the days of the month starting from Jan 01 to Dec 31st In A4 down to A100 are a list of names If i use January as an example i need the answer to come to 3 instances and a total of 12 days off Cell D4 = s D5 = s D6 = s D7 = blank D8 = Blank D9 = blank D10 = Blank D11 = Blank D12 = Blank D13 = Blank D14 = Blank D15 = s D16 = Blank D17 = Blank D18 = Blank D19 = Blank D20 = s D21 = s D22 = s D23 = s D24 = blank D25 = blank D26 = blank D27 = blank D28 = s D29 = s D30 = s D31 = s Hope this makes sense |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| In a set of dates, can you count the number of Jan '07 instances? | Excel Worksheet Functions | |||
| count # of instances in cell (a b a) answer 2 formula if possible | Excel Worksheet Functions | |||
| Formula to use to count instances of in Excel | Excel Worksheet Functions | |||
| Count Instances | Excel Discussion (Misc queries) | |||
| Count number of instances in sheet e.g. how many males/females | Excel Worksheet Functions | |||
Linear Mode
