| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 E5 = s F6 = s G7 = blank H8 = Blank I9 = blank J10 = Blank K11 = Blank L12 = Blank M13 = Blank N14 = Blank O15 = s P16 = Blank Q17 = Blank R18 = Blank S19 = Blank T20 = s U21 = s V22 = s W23 = s X24 = blank Y25 = blank Z26 = blank AA27 = blank AB28 = s AC29 = s AD30 = s AE31 = s Hope this makes sense |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Number of instances
Your example data seems to be in a diagonal pattern going down and
across the sheet. Wouldn't all the data for one member of staff be on the same line? And wouldn't you want to have the sickness analysis carried out for each member of staff? By my reckoning there are 4 instances of sickness in your example data. A bit more clarification, please. Pete On Jul 13, 11:50 am, 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 E5 = s F6 = s G7 = blank H8 = Blank I9 = blank J10 = Blank K11 = Blank L12 = Blank M13 = Blank N14 = Blank O15 = s P16 = Blank Q17 = Blank R18 = Blank S19 = Blank T20 = s U21 = s V22 = s W23 = s X24 = blank Y25 = blank Z26 = blank AA27 = blank AB28 = s AC29 = s AD30 = s AE31 = s Hope this makes sense |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Number of instances
Given an interpretation of the rule you had in your previous posting (sse
below) , I colud argue there are only 2 instances. Q15 is within 5 of T20 and W23 is also within 5 of AB28: 1 instance? You need to spell out much more clearly what the rule(s) is(are). "Pete_UK" wrote: Your example data seems to be in a diagonal pattern going down and across the sheet. Wouldn't all the data for one member of staff be on the same line? And wouldn't you want to have the sickness analysis carried out for each member of staff? By my reckoning there are 4 instances of sickness in your example data. A bit more clarification, please. Pete On Jul 13, 11:50 am, 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 E5 = s F6 = s G7 = blank H8 = Blank I9 = blank J10 = Blank K11 = Blank L12 = Blank M13 = Blank N14 = Blank O15 = s P16 = Blank Q17 = Blank R18 = Blank S19 = Blank T20 = s U21 = s V22 = s W23 = s X24 = blank Y25 = blank Z26 = blank AA27 = blank AB28 = s AC29 = s AD30 = s AE31 = s Hope this makes sense |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Number of instances
Pete,
Data is one row per person: Op mis-posted! "Pete_UK" wrote: Your example data seems to be in a diagonal pattern going down and across the sheet. Wouldn't all the data for one member of staff be on the same line? And wouldn't you want to have the sickness analysis carried out for each member of staff? By my reckoning there are 4 instances of sickness in your example data. A bit more clarification, please. Pete On Jul 13, 11:50 am, 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 E5 = s F6 = s G7 = blank H8 = Blank I9 = blank J10 = Blank K11 = Blank L12 = Blank M13 = Blank N14 = Blank O15 = s P16 = Blank Q17 = Blank R18 = Blank S19 = Blank T20 = s U21 = s V22 = s W23 = s X24 = blank Y25 = blank Z26 = blank AA27 = blank AB28 = s AC29 = s AD30 = s AE31 = s Hope this makes sense |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Number of instances
Thanks Toppers.
To Louis: I answered a similar query in October 2006 - the thread was titled "Occasions in a sickness record" and it was posted in public.excel. Perhaps this might be of use to you if you search the archives for it. Hope this helps. Pete On Jul 13, 7:32 pm, Toppers wrote: Pete, Data is one row per person: Op mis-posted! "Pete_UK" wrote: Your example data seems to be in a diagonal pattern going down and across the sheet. Wouldn't all the data for one member of staff be on the same line? And wouldn't you want to have the sickness analysis carried out for each member of staff? By my reckoning there are 4 instances of sickness in your example data. A bit more clarification, please. Pete On Jul 13, 11:50 am, 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 E5 = s F6 = s G7 = blank H8 = Blank I9 = blank J10 = Blank K11 = Blank L12 = Blank M13 = Blank N14 = Blank O15 = s P16 = Blank Q17 = Blank R18 = Blank S19 = Blank T20 = s U21 = s V22 = s W23 = s X24 = blank Y25 = blank Z26 = blank AA27 = blank AB28 = s AC29 = s AD30 = s AE31 = s Hope this makes sense- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count the number of Instances | Excel Discussion (Misc queries) | |||
| In a set of dates, can you count the number of Jan '07 instances? | Excel Worksheet Functions | |||
| Counting number of instances before a certain date | Excel Worksheet Functions | |||
| Counting number of instances in a range | Excel Worksheet Functions | |||
| Find all text instances in a sheet and add one number from each row | Excel Discussion (Misc queries) | |||
Linear Mode
