# February 2012 Archives

## Motion Induced Blindness

Hello All.

My name is Ian Huitson "Hui".

I am quite honored that Daniel has invited me to become a regular contributor here at the Excelhero.com/Blog and hope I can maintain the high standard on content that this site has become renowned for. I look forward to your feedback on this and future posts.

A few months ago I spotted a new optical illusion over at http://www.michaelbach.de/ot/mot_mib/index.html

I figured I'd give it a go in Excel and this post documents my approach to a solution.

Motion Induced Blindness

What to see

On the right you see a rotating array of blue crosses and 3 yellow dots. Now fixate on the centre (watch the flashing red/green spot). Note that the yellow spots disappear once in a while: singly, in pairs or all three simultaneously, right?
In reality, the 3 yellow spots are continuously present, honest! This is captively called "motion induced blindness" or MIB.

What to see (copied from michaelbach.de)

Below you see a rotating array of blue crosses and 3 yellow dots. Now fixate on the centre (watch the flashing red/green spot). Note that the yellow spots disappear once in a while: singly, in pairs or all three simultaneously, right?

n reality, the 3 yellow spots are continuously present, honest! This is captively called "Motion Induced Blindness" or MIB.

What to see

On the right you see a rotating array of blue crosses and 3 yellow dots. Now fixate on the centre (watch the flashing red/green spot). Note that the yellow spots disappear once in a while: singly, in pairs or all three simultaneously, right?
In reality, the 3 yellow spots are continuously present, honest! This is captively called "motion induced blindness" or MIB. The actual MIB Excel model is much smoother than this animated GIF representation.

The MIB Model

There were three approaches I thought about using for this optical illusion.

1.   Use a single series to define all the points (49) and place a cross at each point. 2.   Use 2 series to define each cross, there are 49 crosses. 3.    Use a Bitmap for the Background including crosses and rotate it.

Choice of Attack

The first method wouldn't suit the needs of the illusion as each marker doesn't rotate as the series is rotated, but instead stays fixed relative to the ordinal axis.

The second method would require a large number of co-ordinates for each cross, that is 4 X and 4 Y co-ordinates for each cross and there are 49 Crosses, for a total of 98 series, and 196 co-ordinates. Time consuming but at least the crosses will rotate. This is the methodology I choose for the model. The Third method of rotating a fixed bitmap although very feasible, wasn't I felt in the spirit of doing it all in an Excel Chart. I should note that this method would allow for much faster rotation than has been achieved using Method 2.

You can follow along with the real model and all associated preparatory workings in the attached file: Motion Induced Blindness.xlsm. All preparatory workings described below are on worksheet "2".

Setup

Firstly I setup a table of numbers -3 to +3 in X and Y and then added/subtracted a small amount to each one to represent the width of the cross. I settled on 0.15 as it looks about right.

This gave me a table of X and Y values for each point.

 Offset 0.15 Pt No X1 X2 Y Circle Quadrant 1 -3.15 -2.85 -3 3 2 -2.15 -1.85 -3 3 3 -1.15 -0.85 -3 3 4 -0.15 0.15 -3 3 5 0.85 1.15 -3 4 6 1.85 2.15 -3 4 7 2.85 3.15 -3 4 8 -3.15 -2.85 -2 3 9 -2.15 -1.85 -2 3 10 -1.15 -0.85 -2 3 etc

Rotation

The problem with X and Y values is that to rotate them around a point it is easier to use Polar coordinates, but Excel requires Cartesian Coordinates to plot.

So the process would be

1.       Setup the 49 points of 4 sets of X, Y pairs

2.       Transform them to polar co-ordinates.

3.       Setup a named formula for each end point

4.       Add a radial increment to the revised polar coordinates

5.       Use a named formula to convert the polar coordinates to Cartesian coordinates

6.       Plot

7.       Repeat from Pt 4.

Polar Co-ordinates

Point 2 in the above sequence means converting each set of coordinates into polar co-ordinates consisting of Radius r and Angle Ø. Solving the above we have

r           =sqrt(x2 + y2)

= Sqrt(-2.852 + -22)

Ø          = Atan(y/x)

=Atan(-2/-2.85)

This is done for every end point of each cross section, 98 pairs of X, Y Co-ordinates.

Now we have the polar co-ordinates of the end points, we can setup the rotation equations.

That is the equations to convert the original polar co-ordinates back to X & Y values, which Excel needs to plot.

This is done by 2 simple equations:

X = r * Cos(Ø)

Y = r * Sin(Ø)

X = 3.731*cos(3.707)

Y = 3.731*sin(3.707)

Now we can add a rotation angle, lets use t.

So that the new position after rotation is

X = r * Cos(Ø + t )

Y = r * Sin(Ø + t )

Xrot = 3.731*cos(3.707 + t)

Yrot = 3.731*sin(3.707 + t)

This is done for each point of the cross for all crosses.

 x1 y1 4.350*cos(3.903+t) 4.350*sin(3.903+t) 3.691*cos(4.091+t) 3.691*sin(4.091+t) 3.213*cos(4.346+t) 3.213*sin(4.346+t) 3.004*cos(4.662+t) 3.004*sin(4.662+t) 3.118*cos(4.988+t) 3.118*sin(4.988+t) 3.525*cos(5.265+t) 3.525*sin(5.265+t) 4.138*cos(5.472+t) 4.138*sin(5.472+t) 3.731*cos(3.707+t) 3.731*sin(3.707+t) 2.936*cos(3.891+t) 2.936*sin(3.891+t) 2.307*cos(4.191+t) 2.307*sin(4.191+t) etc.

Matrix Arithmetic

To draw a line on a scatter chart, Excel needs 2 X values either in a Range or an Array as well as 2 Y values in a Range or Array.

Thankfully I've been a member of Daniel's Excel Hero Academy. In a Module on Matrix Arithmetic we learn that we can add 2 named formulas together to make an array in a Named Formula.

We need to do this to end up with an Array representing the X and Y values for each of the 98 segments of the 49 Crosses.

X Values = { X1, X2 }

Y Values = { Y1, Y2 }

As an Excel Named Formula I used:

Named Formula   Formula

sx_08                     = {1,0} * 3.731*cos(3.707+t)  + {0,1} * 3.482*cos(3.753+t)

sy_08                     = {1,0} * 3.731*sin(3.707+t)  + {0,1} * 3.482*sin(3.753+t)

This is done for all the 98 cross segments.

To simplify the construction of all these, the co-ordinates, transformation to polar coordinates and construction of the rotated transform formulas was done in Excel (Refer Worksheet "2" in the example file).

This allows errors in co-ordinates to be checked.

Once all the named formula are ready to be uploaded, I have used a technique involving a simple VBA Named Formula upload subroutine. This is described in my post at: http://chandoo.org/wp/2011/06/23/automating-repetitive-tasks.

The VBA routine is available in Module 2 of the attached Sample File, "Load_Named_Ranges()".

Once the named formula are constructed and loaded, it is simply a matter of adding a blank scatter chart to Excel and setting up a table of Series Names, X value and Y Values:

 Chart Series Name X values Y values S01 =1!sx_01 =1!sy_01 S02 =1!sx_02 =1!sy_02 S03 =1!sx_03 =1!sy_03 S04 =1!sx_04 =1!sy_04 S05 =1!sx_05 =1!sy_05 S06 =1!sx_06 =1!sy_06 S07 =1!sx_07 =1!sy_07 S08 =1!sx_08 =1!sy_08 S09 =1!sx_09 =1!sy_09 S10 =1!sx_10 =1!sy_10 Etc

Once again I have setup a table of Named Formula name, together with X and Y Named Formula and used a small VBA routine to add these series to the chart.

The VBA routine to do this is available in Module 2 of the attached Sample File, as "Add_Cht_Series()".

The 3 Yellow Spots

The 3 yellow spots are a manually loaded series in the chart using an Array of coordinates.

X Series                              ={1.5, 0, -1.5}

Y Series                              ={1.5, -1.8, 1.5}

The Marker was set to Yellow and size 15

The Line Type was set to None

The Centre Spot

The centre spot was a manually loaded series in the chart

X Series                              =0

Y Series                              =0

The Marker was set to Red and size 12.

The Line Type was set to None.

Animation

Animation of the chart is achieved by adding a simple Named Formula "t" and the changing the value of t and updating the chart.

This is done through a simple VBA routine "Rotate()"

This is described below

Sub Rotate()

Dim t As Double 'Dimension the only variable

t = 361 'Start at 361 Degrees

Do While [AA1]   'Loop while cell AA1 is True

t = t - 1 'Decrease rotation angle by 1 Deg

If t = 0 Then t = 360 'If Rotation = 0 go back to 360

ActiveWorkbook.Names.Add Name:="t", RefersToR1C1:=(t * 2 * Pi / 360)

' Add a named Formula t with value = t * 2 * Pi / 360

' t expressed in radians

DoEvents 'Refresh screen

If (t >= 0 And t < 90) Or (t >= 180 And t < 270) Then 'If t in a range set Centre Marker color Red or Green

ActiveSheet.ChartObjects("Chart 2").Chart.SeriesCollection(99).Format.Fill.ForeColor.RGB = RGB(255, 0, 0)

Else

ActiveSheet.ChartObjects("Chart 2").Chart.SeriesCollection(99).Format.Fill.ForeColor.RGB = RGB(0, 255, 0)

End If

Loop

End Sub

The above example is attached below:

Worksheet 1, contains the working model.

Worksheet 2, contains the original source data as well as all transformations of it.

FINALLY

This is my second post at ExcelHero.com and I'd like to thank Daniel for allowing me to post here again.

I am a member of the inaugural Excel Hero Academy and MVP of the Excel Hero Academy 2 & 3, where Daniel explains a lot of the techniques you will see throughout this site as well as so much more.

It is one of these techniques that made this project possible.

I am a regular contributor at Chandoo.org where I answer questions at the Forums and have contributed over 30 Posts.

## Time to Learn!

| 1 Comment | 0 TrackBacks

It is with great pride that I announce that the third class of the Excel Hero Academy is now open for registration! We are definitely growing.  I've hired my first employee!

If you have been waiting for an opportunity to truly understand how Excel works, the time is now.

But hurry because registration will only stay open for two weeks and the next class will not be until summertime.

But whether you decide to enroll or not, please let me know what you think of our brand new Enrollment Page!

.  ### Monthly Archives  