matching consecutive values

M

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
 
S

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?
 
M

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top