consecutive value occurance count?

I have to calculate bonus payments for people working O/S. I have a 5 year calendar in month blocks (60 months/columns). In each month there is a percentage 'time O/S' figure. If an employee is O/S for 3 consecutive months they get bonus 'A', 6 consective months bonus 'B', etc.
How can I evaluate the 60 columns, returning the number of times 100% occurs in 3 consecutive months/columns.
I have found examples of similar solutions but they will return a value of 3 if there are 5 consecutive months of 100%...
ie. 100 100 100 100 100
= 100 100 100 *** ***
& *** 100 100 100 ***
& *** *** 100 100 100
= 3
but I need it to equal 1

Hope this makes sense to someone else???

Thanks...

See attached file where I inserted 'bonus' function... I hope it can help.

Regards,
Antonio

I have 60 columns across sheet representing months

Each column has a % value.

I need to count the number of times 100% appears in 3 consecutive columns as 'A'

I also need to count the number of times 100% appears in 6 consecutive columns as 'B'

But, if 100% appears in 5 consecutive columns, I need 'A' to equal 1 (not 3), as although there are technically 3 sets of 100% values the employee has only been there for one 'A' qualifying period...

This is what I was trying to represent, the *** represented the 100% value that were not being evaluated in each of the three possible match's.

Hope that's a bit clearer, sorry it's very hard to explain

The issue I have is that in the given period an employee may qualify for ("bonus A" 12 times and "Bonus B" 3 times) or ("bonus A" 2 times and "bonus B" 6 times) or millions of other combinations thereof...

So I need to return a numeric value for each bonus that will go under a column heading "Bonus A"(Number of times: 3 consecutive months @ 100% O/S), "Bonus B"(Number of times: 6 consecutive months @ 100% O/S).

This is where I believe the difficulty comes, returning a value of 1 for five consective months 100% Overseas. The other thing is it must only count 100% O/S values as there will be many months when there are values of 10%, 25%, etc not credited toward the bonus.

for example-

100, 15, 25, 100, 100, 50, 100, 100, 5, 100, 100, 0, 100, 100, 10

where:
'A' = 0
'B' = 0

or

100, 100, 100, 100, 100, 100, 100, 100, 0, 100, 100, 100, 100, 100, 10

where:
'A' = 1
'B' = 1

or

0, 0, 0, 0, 0, 0, 25, 50, 75, 100, 100, 75, 50, 75, 100, 100, 100, 100

where:
'A' = 1
'B' = 0

Hope this makes a bit more sense now...(sure know I'm confused)

Regards,
David

I modified the previous function to do what you need.

Regards,
Antonio

Thanks, nearly 2am here (Australia). Been at it since 6am, back at it in 4 hrs. Will try your code in the morning and post my results...

Should have gone to bed 2 hrs ago... Good Night!

You guys are great!!!

Thanks NBVC,

Your solution is just ablut giveing me what I need. Unfortunately it is not correctly condidering whether the values are consecutive... please se attached example xls.

PS. Thanks for your help Antonio, hope you don't mind, I put NBVC's code into your example for clarity...

Thanks again,
David

I modified the function to work as you need.

I hope it's what you need.

Regards,
Antonio

NBVC,
Your solution works perfectly, thankyou for your help.

Antonio,
Thankyou for your solutions and especially for the example that helped me explain the true requirement. Your solution works until I have both A & B bonus for the same person, the B bonus cancels the A bonus when both are sometimes true.

I cannot thankyou both enough for your help with this...

Kindest Regards,
David

Hi,

The HR people changed the criteria for bonus eligibilty on me... however I've been able to change the formula's provided by NBVC (thankyou) to address this change.

Where I am stuck is I now need to know the sum of months for which the employee is entitled to each bonus. I'm quite sure it's an easy mod to the existing formula with a count function, but I can't seem to get it.

Please see attached example for details...

Thanks,
David

i haven't tried your solution yet... not at home download to USB stick for now, will try tomorrow, based on your solutions to date I'm sure it'll be perfect.

You're a champion, thanks.

David

Dear NVBC (or anyone else)...

Your last solution was perfect and the HR people were happy. Now the big boss says that all people that go overseas to work on this project should get a bonus not just the ones there for 100% of month. I've modified the example (and criteria) and attached it. Basically I now need to sum the percentages for Bonus 'A' and for Bonus 'B'.

Thanks in advance,
David

I really appreciate your help with this. Thankyou so much for taking the time to seek further help rather than just saying "sorry, too hard". I'm so impressed that I have a problem that is so difficult... anyhow, if you could get it working in my example that'd be great, I won't even pretend to follow the proposed solution...

Thanks again,
David

Hi NBVC,

I don't seem to be able to get this to work, not sure if I'm missing something or if it's just stupid factor?

Anyhow, are the 3 cells at the bottom required and if so does it matter where they are? If I delete them from your example it still works fine.

When I enter the main formulas into my spreadsheet I get a null value when expected, but if I should have a value > 0 it displays "#NAME?"

Any ideas, other than installing a brain upgrade?

Thanks
David