Instances that occur in a sequence

**timmycl_7** · 03-14-2008, 07:08 AM

Hi,

I have a problem, I have a formula which counts the number of instances that occur and assigns the value as 1 for every instance, however I want the formual to also recognise that if a number of instances occur in succession a value of 1 should also be assigned.

Is this possible?

E.g. if a person is absent for 1 day the formula assigns a value of 1
if a person is off for 3 days in succession the formula assigns a value of 1

Regards

**NBVC** · 03-14-2008, 07:45 AM

Can you show the data setup and the current formula used?

**timmycl_7** · 03-14-2008, 09:13 AM

The sheet is used as a work schedule as well. 9 Column headings -

Date, Shift1, Shift2 etc

The employees initials are entered into the field according to when they are supposed to be working. The spreadsheet is updated daily, if an employee is sick, the employees intials + sick are enterd. Currently I have just used a countif formula to count the number of times every instance of (Employee Initials)+sick.

I would say changnig the structure of the table might not be an option as I have not designed the table.

**NBVC** · 03-14-2008, 09:24 AM

I would like to see a sample if possible...Zipped XL spreadsheet.

**timmycl_7** · 03-14-2008, 09:59 AM

Sorry,

I have attached a sample this time. Sheet 1 contains the information and sheet 2 contains my formula. In the example PY is off 4 consective days. I want this to be counted as 1. Also CM was sick 1 day, I also want this to be counted as 1. I'm trying to count the number of occasions rather than the number of days.

Thanks

**NBVC** · 03-14-2008, 10:45 AM

See attached....

I added 5 helper rows (1 for each shift)... you can hide these if you wish.

These helper rows calculate the counts of singles and groups of 3 or more "sick" for each shift. Note these are array formulas... if you edit them you have to confirm them with CTRL+SHIFT+ENTER not just ENTER so that the { } brackets appear around the formula.

Then you just sum these up in your summary.

**timmycl_7** · 03-14-2008, 01:02 PM

That's brilliant NBVC,, I will try and implement that into the worksheet on Monday. Thanks for your help.

Regards

**timmycl_7** · 03-19-2008, 09:33 AM

NBVC,

thanks for your help, I'm having some problems with implementing the formula on my worksheet though. When I enter the formula and press ctrl+shift+enter a dialog saying "file not found".

Also when check your sampe, if I change the data into original table the helper rows don't get updated.

Do you have any ideas of why the formulas aren't working

Regards

**NBVC** · 03-19-2008, 09:46 AM

As for the formulas not updating... perhaps you have manual calculations set...

Try going to Tools|Options and in the Calculation tab check on the Automatic calculations.

I am not sure why you are getting "File not found". I've never seen that... are you perhaps on a Mac... I think with a Mac there is a different way of entering an array formula..I think it's Command+Return.

**timmycl_7** · 03-19-2008, 12:35 PM

NBVC.

If you look at the examples. Sheets HWRC 08 + HWRC Monitoring, I think I have entered the exact same formula as the one in your sample.

I checked tools options, it is set to automatic calculations.

I need the formula to be able to monitoring days off, though if days are taken off in succession I want the formula to recognise this as one occurrence.

Again, thanks for your help its really appreciated

Regards

**NBVC** · 03-19-2008, 12:52 PM

You did not define your Sheetname in the range reference properly... that is why you get the #NAME? error. The Single quotes are supposes to surround the whole sheet name, not part of it... and your ranges stopped at C13 when, in fact you have 371 items in your database.

The column references need to be updated manually for each shift. You only need to do this for the first column (i.e. the PY column)... then you copy those formulas across the other columns.

See updated sheet attached.

**timmycl_7** · 03-20-2008, 06:55 AM

NBVC,

Thanks for all your help, that is working perfectly now.

Regards
Tim

**timmycl_7** · 03-20-2008, 07:23 AM

NBVC,

sorry for this and I appreciate your patience. I have one last question. I created a validation list to control user entry. Would this have any effect on the formula as it doesn't seem to work with the validation list. It works perfect without the validation list however.

Regards