Here’s another take on the Eye Jitter illusion.

Does the central large square with the little squares inside float and dance above the background of circles?

Sometime shaking your screen a little enhances the illusion. Seriously!

This was fairly straightforward from the Excel charting perspective. The large square is constructed from error bars. The little squares and the circles are markers on an XY (Scatter) chart. This week’s file is an .xls so it should work for everyone, but for some reason the circles look terrible in versions prior to Excel 2007, and the illusion suffers as a result.

I’ll have another next week – and it’s completely different than all the previous illusions…

Here’s the workbook:

— UPDATE —

After Lori’s fine work I decided to upload a new workbook that shows the named formula approach alluded to in the discussion below. The workbook contains two charts. The first uses Lori’s formulas. The second chart uses my suggestions. Neither is more correct, but it should be instructive looking at both. To do so, you will need to use the Name Manager. The charts are not formatted, so the optical illusion is not there. The Named Formula discussion is simply about preparing the data for the chart, and not the formatting that produces the illusion. Here’s the workbook:

Daniel,

I think the link to download the file is broken…

Sam,

Thanks. It’s fixed now.

Regards,

Daniel Ferry

excelhero.com/blog

Learning a lot from the blog, these samples are fun and instructive.

I also like using the named formula approach. For this one, I copied the chart and created one series for each type of point, deleting any additional ones, and also replaced error bars with connecting lines. The formulas are:

=SERIES(“Lines”,{4.5,11.5,11.5,4.5,4.5},{4.5,4.5,11.5,11.5,4.5},1)

=SERIES(“Dots”,’1’!xDot,’1’!yDot,2)

=SERIES(“Squares”,’1’!xSq,’1’!ySq,3)

D:={1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}

S:={5,6,7,8,9,10,11}

xSq:=S+0*TRANSPOSE(S)

ySq:=0*S+TRANSPOSE(S)

xDot:=0*D+TRANSPOSE(D)

yDot:=IF(ISNA(MATCH(D,S,0)*TRANSPOSE(MATCH(D,S,0))),D+0*TRANSPOSE(D),NA())

2D arrays are allowed in chart series which can be quite useful, it seems the semicolons get automatically converted to commas. In some cases you may need to force array evaluation eg by appending +{0}.

@Ihm –

Excellent work!

As an alternative which might make it clearer for the readers, you could set the named formulas for the squares to this:

xSq:={0,0,0,0,0,0,0} + {5;6;7;8;9;10;11}

ySq:={5,6,7,8,9,10,11} + {0;0;0;0;0;0;0}

which shows how the commas and semicolons produce the columns and rows in the resulting arrays. This is similar to the Live Calendar:

http://www.excelhero.com/blog/2010/01/live-calendar-musings.html

If you wanted to avoid having the dots appear as y=0 for each dot that overlaps the squares, I would do this:

Overlap:=ISNA(MATCH(D,S,0)*TRANSPOSE(MATCH(D,S,0)))

xDot:=IF(Overlap,0*D+TRANSPOSE(D),1)

yDot:=D+0*TRANSPOSE(D)

which causes those points to be rendered at the ordered pair (1,1). Of course you could just manually set the vertical axis so that zero is not shown, but sometimes that is not an option as in my Number Spiral:

http://www.excelhero.com/blog/2010/03/excel-a-presentation-platform.html

Just some things to think about, but absolutely outstanding work!

Regards,

Daniel Ferry

excelhero.com/blog

Hi.

I’m trying to replicate what Ihm said in their post, and what Daniel said in his reply. I kind of understand the general principle, but I’m struggling with the details. Is there any chance of a simple explanation of exactly how to set up the named formulas ? (I’m using 2003).

Thanks very much.

Gerald, try this link: http://cjoint.com/?eopqfPscf2

Daniel, thanks I’d actually have gone with your approach too here as it’s much quicker to setup.

Here’s another example that tracks smooth lines: http://cjoint.com/?eooSyAXQ1C

HTH. Lori

@Gerald –

See Lori’s link above. Also, I added an update at the bottom of the original post with a link to a workbook that contrasts both approaches to the Named Formulas.

Regards,

Daniel Ferry

excelhero.com/blog

@Lori –

Very interesting work on the DAC Example…

Is the green dot supposed to track along the rectangular or smooth curve? In Excel 2002 the green dot does not move for me at all when holding F9. In Excel 2007 every recalc causes an error message. I tracked down the error to the MOD function in your named formula, t. The MOD function in Excel 2007 apparently does not like such a large number for the first parameter. I changed the definition of t and it now works in Excel 2007 and 2002 for me, but the green dot is tracking along the smooth curve. Is that correct? Here is how I changed your t:

t:=MOD(24*3600*NOW()-3480390000,MAX(x)-MIN(x))+MIN(x)

Nice technique for varying t by the way…

I use Excel 2007 almost exclusively these days. I just realized that my previous comment may not have made sense if you are using earlier versions. If you look at my — UPDATE — workbook, in Excel 2007, the first chart which is based on your formulas plots a bunch of DOTs at y=0. I just noticed that this behavior does not manifest in Excel 2002, and so my suggestion on how to plot them instead at the ordered pair (1,1) is not needed unless you are using Excel 2007. Odd, and odder still that I had not noticed this before!

Regards,

Daniel Ferry

excelhero.com/blog

I only tested it with Excel 2010 Beta and forgot that this incorporates the long overdue MOD function fix – well spotted!

I put the file together quickly thinking you might be able to do some more creative stuff with it. It incorporates an original analog smooth curve, and a square curve generated with a hypothetical DAC from data sampled every second.

It was really intended just to demonstrate how to use names to track the smoothed lines in Excel and draw step graphs.

Had a chance to take another look at your updated file and it is indeed odd behavior, repeating points is a good alternative.

Converting names to values in the series formula, by pressing F9 on the ‘…’!yDot part and hitting enter, removes the extra dots – at least in my version. So it appears #N/A is plotted as 0 in named formulas or arrays but ignored in other cases.

@Lori –

Yes, the behavior does seem inconsistent at times. In some projects when using the named formula technique I’ve had to rely a variety of techniques to ensure that certain points or even entire series get plotted only when I want them to. The NA() function often is not enough. In the Number Spiral, I named a blank cell, “null”. This cell was tucked out of the way. When I did not want a series to show, I would set it’s “t” to “null” and when I wanted to show it, I would set t to do its normal thing. This worked well.

BTW, my brain is churning away on the DAC example. I may come up with something interesting. Thanks.

Regards,

Daniel Ferry

excelhero.com/blog

@Gerald –

Were those workbooks sufficient? Or is more explanation needed?

Regards,

Daniel Ferry

excelhero.com/blog

Sorry Daniel, I’m embarrassed to say I haven’t looked at them yet, been too busy on other stuff.

@Gerald –

No problem. I just wanted you to know I’m here to help, when/if you need it…

Daniel Ferry

excelhero.com