Automate finding the zone of proximal development in guttman chart

rline101

Board Regular
Joined
Dec 22, 2005
Messages
71
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.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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.
[TABLE="width: 556"]
<colgroup><col><col><col span="25"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top