matching consecutive values

[Matthew]

i'm very stuck.

i have a workbook that is a list of shops and their audit scores for
the last 24 months.

each store gets at least 1 audit % score every qtr could be 2 or 3
though.......

so imagine the sheet.

650X24

what i am looking for is an easy way of finding if.

the store is CURRENTLY sat on 3 consecutives audit scores of less
that 76%.

if the store has had a period of more than 3 audits of less than 76%
and is now better than 76%

The stupid thing is I can do this the hard way (ie pen and paper) but
I can not do it with XL..

I'm feeling thick so please help me....

either macro or nice formular which ever works


Thanks

Mathew

 

[Shane Devenshire]

Hi,

I'm not clear on what you mean by Currently sat on 3 consecutives audit
scores of less than 76%? Do you mean 3 consecutive score of unsatisfactory
(<76%). Then you ask if the store has had more then 3 (4+) scores of less
than<76% - are these consecutive or not in this second case? In your 650X24
sheet how do you determine what is current are these the last three non blank
cells on a given row?

 

[Matthew]

Hi,

I'm not clear on what you mean by Currently sat on 3 consecutives audit
scores of less than 76%?  Do you mean 3 consecutive score of unsatisfactory
(<76%).  Then you ask if the store has had more then 3 (4+) scores of less
than<76% - are these consecutive or not in this second case?  In your 650X24
sheet how do you determine what is current are these the last three non blank
cells on a given row?

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

Having re-read my question a few times let me try again.


1st Column = Shop No
Cols 2 through 14 are months
In any month a shop can have an audit but usualy gets one a qtr.
The are not spaced evenly, ie an audit in Jan followed by the next in
June for example, the shop has had the required 1 per qtr.

The challenge is to look at the 12 cols and without using a large
nested if statement determine the frequency of the <76% and if the are
consequtive.


Obviously the easy way would be for the managers to do their jobs and
not fail audits


Hope you can help or its pens and paper for me .


Matthew