Automate finding the zone of proximal development in guttman chart

[rline101]

Hi all
I don't know if what I'm trying to do is possible, or not. I can achieve it manually by looking and typing in the numbers, but I would like to automate it.

I've made a guttman chart in excel. Everything is automated except for this part which I'm really struggling with. So at present, I have something like this, which is a sample only, of a class and an assessment:

1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0 1 0 1 0 0 0 1 1 0
1 1 1 0 1 1 1 1 1 0 0 0 1 0 1 1 0 1 1 0 1 1 0 1 0 1
1 1 1 1 1 1 1 1 0 1 1 1 1 1 0 0 0 0 1 0 1 1 0 1 0 1
1 1 1 1 0 0 0 1 1 1 0 1 0 1 1 1 1 1 1 1 0 0 1 1 0 0
1 1 1 1 1 0 0 0 1 1 1 1 1 0 0 0 0 1 0 1 0 0 0 0 1 0
1 1 1 1 1 1 1 1 1 1 1 1 0 0 1 1 1 0 0 0 0 0 0 0 0 0
1 0 1 1 1 1 1 1 1 1 0 0 1 1 0 0 0 1 0 1 1 1 1 0 0 0
1 1 1 1 1 1 1 1 0 0 1 1 1 1 1 1 0 1 0 0 0 0 0 0 1 0
1 0 0 1 1 0 1 0 1 1 1 0 0 1 0 0 1 0 1 0 0 0 1 0 1 0
1 1 1 1 1 1 0 1 1 1 1 0 1 0 1 0 1 0 1 0 0 0 0 0 0 0
1 1 1 1 0 1 1 0 0 0 0 1 0 1 1 1 0 0 0 1 1 0 1 1 0 0
1 1 1 1 1 1 0 1 0 0 1 0 1 1 0 0 0 0 1 1 0 0 0 0 0 0
1 1 0 0 1 0 0 1 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1 0 0 0 1 1 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 1 0 0 0 0 0 0 0 1 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0
1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0

Each row represents a student. Each column represents a skill. It's sorted by student and by skills. So in general, the lower rows get less skills correct. 1 means the student got the skill correct. 0 means the student got the skill wrong. The first column has almost all 1s, indicating that almost all students got this question correct, so it's the "easiest" question.

The zone of proximal development is that area for each student where the numbers alter between 0s and 1s. So for the student in the top row, their ZPD might be from skills (columns) 16 to 21 inclusive. The bottom student might have a ZPD of 2 to 3. The student 3rd from the top might have a ZPD between 14 and 26.

Any 1s on their own in a sea of 0s are considered aberrations, and similarly for 0s in a sea of 1s.

So I'm trying to find a way of automatically producing those zones. I'd like to have returned something like:

Row 1: Start=16, End=21
Row 3: Start=14, End=26
etc

It would need to ignore any aberrations and just locate the main area where the 1s become a mix of 1s and 0s. I'm happy to use VBA.

Is this sort of thing possible? Is it beyond excel? Hoping for some pointers in the right direction. Thanks.

 

[zxcvbnmasdf]

Consider using the conditional formatting option within excel. This will colour-differentiate between the 1s and 0s. A loose boundary will appear, diagonaally from bottom-left to top-right. This represents the ZPD.

<colgroup><col><col><col span="25"><col></colgroup><tbody>
</tbody>