All posts by Admin
Back By Popular Demand – The Awesome Excel Dashboards Course!
Regional Excel Differences – Leave Your Mark
Advanced Excel Training!

“Jawdropping… For the advanced Excel user, Daniel’s Blog and the Excel Hero Academy are
treasure chests full to the brim with invaluable Excel information, techniques and ideas.” – Robert Mundigl 
“Daniel’s expertise in Excel is unrivaled in my opinion… Daniel’s Excel training has literally changed the way I view Excel and use it for business purposes.” – Vedantham Srinivas

“I was especially impressed with the ExcelAccess set of tools that enables Excel to used to retrieve data from and update an Access database.” – Dennis Plum

“I challenge ‘anyone’ to put together a better course than EHA for a comparable cost. I’ll go out on a limb here say, it aint gonna happen.” – Calvin LeBlanc

“I’ve never seen anything like Daniel’s course. Nothing is remotely like it. Daniel’s course has paid for itself time and time again.” – Michael Shallal

“History will show that Daniel Ferry has built a fabulous body of work.” – Brendan Drew

“There is nothing like Daniel’s course out there. The content is like discovering gold.” – John Hackwood

“This is an empowerment system with so many extras. Thank you, thank you for a fabulous Excel journey and experience.” – Danièle Questiaux

“I am very very happy with the course, best I have ever invested in. I am glad I ordered the download option as I believe I will keep learning from your modules for a long time to come.” – Ajay Gajree

“The content and videos in Daniel’s class are the best I have seen.” – Wanda Norrick

“Forget all the businessy stuff. Daniel is to Excel as Neo is to The Matrix. By the end of the course, you’ll be ‘walking the path’ with your new powers of Excelfu. Learn from The One!” – Mark Sessoms
Awesome Excel Dashboard Course
Excel Formulas Can Make a Graph
Enchanted Radar Chart
Infographic with Excel
We created some interesting charts combining different chart types and using the builtin features in a bit of an unusual way.
In this article we would like to show how to create the chart you can see above, and how you can make it dynamic, based on the cell selected by the user.
The data we use on the chart comes from this infographic:
http://awesome.good.is/transparency/web/1010/politicalclimatechart/interactive.html
The numbers are percentages representing the fraction of the people with regard to the particular issue that is the top priority.
We designed a chart a similar to the original one, because the circle format could visualize this kind of data more effectively than a linear one. All of the 20 measured issues (categories) are visible on the chart now, but you can add more data to the table. The names in the file are built to be dynamically growing, so the new data will immediately appear on the chart.
To make the comparison easier
we turned the chart inside out and used activecell based highlighting together with a marker line.
Chart components
On the chart you will find 4 data series, three of which are filled with the radar type chart, and the fourth one is a doughnut chart with a label. The dynamic chart title serves to make the chart more “readable”.
It is important to note that nothing is drawn manually. The only pain is that Excel cannot set the angle of the label added with the help of the doughnut series, so it is adjusted manually.
Filled radar data series:
 The white columns growing from outside to the centre of the circle represent the data. But the series itself is the green area – it “cuts out” the data columns from the white background of the radar chart.
 The black outline around a white column highlights the data selected by the active cell (row)
 The grey dotted circle is a contourline and it indicates the value of the category to make comparable with the others.
Doughnut data series:
 The black circle at the outer rim was created as doughnuttype which makes creating the labels easier.
How do we manipulate our data to build up the series?
Primary data display – insideout columns
The array for the green data series should look something like this: (remember, the data here is 1 – <percentage value>)
1
0.25
0.25
0.25
0.25
0.25
0.25
0.25
0.25
1
0.39
0.39
0.39
0.39
0.39
0.39
0.39
0.39
1
0.26
0.26
.
.
.
How can this array been created as a named formula – without using helper cells on the worksheet?
Capturing input data
First we define the parameters of the data series:
Named formula  Note  Refers to 
n_ColPoints  Predefined number of data points for one category + 1 point for the gap  =9 
n_Category  Number of categories in the data table. In the example n_Category = 20  =COUNTA( Sheet1!$A$1:$A$100 ) 
n_TotPoints  Total number of data points on the radar chart In the example n_TotPoints = 180 
=n_ColPoints * n_Category 
You can change the value of n_ColPoints  lowering it will result in a more triangularshaped column, increasing will diminish the gap. In the example we use 9, as this gives a good result regarding visibility.
Note: Number of categories are limited to 100 in the dynamic named formulae, however the number of effectively displayable categories is far less.
Create a dynamic range for the input data:
rng_h  n_Category could be used in the INDEX formula to make the data table dynamic  =Sheet1!$B$2:INDEX( Sheet1!$B$2:$B$100, n_Category) 
Building the main data series
And here comes a bit more difficult part…
The structure of the array we would like to have is simple: a gap data followed by the 1st category data repeated 8 times, gap again, 2nd category data repeated 8 times, gap, etc.
Step 1 – Repeated values for the chart columns
First of all, let’s see that if we can separate the problem of category data from the problem of gap points! If we build up an array without gaps but having 9 times the category data, it will not be a big deal to change the 1st, 10th, 19th, 18th, 37th,…,172nd data to the gapvalue.
So let’s start with this array as intermediate result – every value listed 9 times, and there is 20 category values, so we need an array of 180 elements:
0.75
0.75
0.75
0.75
0.75
0.75
0.75
0.75
0.75
0.61
0.61
0.61
0.61
0.61
0.61
0.61
0.61
0.61
0.74
0.74
0.74
.
.
.
And what do we have as input?
0,75
0,61
0,74
.
.
.
So we want to have a onedimensional vector of 180 elements using another onedimensional vector with 20 elements and we need to repeat each element nine times. A very powerful function of Excel could be used in this case: MMULT ! What is the array we should multiply by our rng_h to have the above mentioned result? If you are familiar with the rules of matrix multiplication, you will easily find the answer:
Step 2 – Build a helper matrix for the value selection
The next step is to find a way to build up this 180 x 20 sized matrix of 1/0 values. It is simple if you see the pattern: 1 = TRUE, 0 = FALSE, so this matrix is a 2D representation of which data point belongs to which category in the 180 length array:
Step 3 – Build a helper array for the value addressing
Now we are close! We need the lefthandside vector from the above picture which tells us which category the data point belongs to. It consists of 180 elements: repeating number 1 nine times, number 2 nine times, going up to 20 repeated nine times. We can create it easily using the INT formula!
Let’s summarize what we have now!
We will need two helpingvectors:
arr_pnt  rowsequence to number the data points from 1 to n_TotPoints {1;2;3;….;180}  =ROW( Sheet1!$A$1: INDEX(Sheet1!$A: $A,n_TotPoints)) 
arr_cat  columnsequence to number the categories from 1 to n_Category {1,2.3,…..,20}  =COLUMN( OFFSET(Sheet1!$A$1,,,,n_Category)) 
Determine the structure of the data points:
arr_pnt_cat  this array – as a sequence – identifies which category the data point belongs to. It consists of 180 numbers, repeating 9 times each number from 1 to n_Category: {1;1,1;1;1;1;1;1;1;2;2;2……..;20;20}  =INT( (arr_pnt1) / n_ColPoints )+1 or an other solution: =MATCH( arr_pnt, arr_gap, 1 ) (see arr_gap below) 
So here is the formula for the 1/0 matrix from the first picture, with double negation to convert the TRUEFALSE to 1/0: –(arr_pnt_cat=arr_cat)
And the matrix multiplication will create us the array without gaps:
MMULT(–(arr_pnt_cat=arr_cat), rng_h)
Step 4 – Inserting gap values
To create the gap values, we need to change the 1st, 2nd…172nd value of this array to 0. The idea is this: if the sequence number of the data point (arr_pnt) is an element of the set of gaps then it should be 0. So we will need the set of gaps:
arr_gap  sequence number of data points used to separate the categories (the gap) {1;10,19;28;37;…..;172}  =(ROW(rng_h) MIN(ROW(rng_h)))*n_ColPoints+1 
With a MATCH and ISERROR we can have the 0s for the gap points:
ISERROR(MATCH(arr_pnt, arr_gap, 0)
One minus this whole array turns the series inside out So here is the final named formula for the main data series!
arr_01  Data series representing the insideout columns. Repeats 1 minus data percentage 8 times alternating gap points with value 1.  =1MMULT(–(arr_pnt_cat=arr_cat),rng_h)* ISERROR(MATCH(arr_pnt,arr_gap,0)) 
After creating a filled radar chart adding arr_01 to data series you should see something like on picture 3. Please change the maximum of the axis to 1,1. The minimum should be 0.
Category labels
Now we have a static insideout chart. The easiest way to add data labels is by using a doughnut chart with number of slices equal to the number of categories. We will need a very simple array as data series for this doughnut consists of 1s for each category. Power of 0 would not be a solution in this case, because Excel interprets 0^0 as #NUM error, so if we have 0 value or missing data in rng_h, the doughnut ring chart will not be in line with the columns – that is the reason why we use ISNUMBER here, and *1 converts the boolean values to numbers.
label_1  ISNUMBER for the data range rng_h gives back TRUE where we have data. You need to multiply by 1 to convert it to numeric values: {1;1,1;…;1}  =ISNUMBER(rng_h)*1 
Add label_1 to the chart series, change the chart type to Doughnut and set the hole size to maximum.
Create this named range for labels:
labels  Dynamic range for labels  =OFFSET(rng_h,,1) 
And insert it to the series formula of the ring via the formula bar:
=SERIES( “label_1″,
Enchanted_radar_chart.xlsm!labels,
Enchanted_radar_chart.xlsm!label_1, 4)
Now you can add labels to the chart, you need to change it to show the category names. Unfortunately Excel can not adjust the angle of the labels according to the slices – the labels must be rotated manually.
Activecell based highlight
Excel’s CELL formula without the second parameter makes it possible to determine the active cell. (You can read more about it here.)
active_row 
The formula gives back the row number within the data table.  =CELL(“row”) CELL(“row”,rng_h)+1 
Now we build up the black outline highlighting the data of the active category. We do not take care of the rest of the categories, so all these data must be zero. We use only the part of the data pointcategory array (arr_pnt_cat) where the numbers are equal to the number of the active category:
arr_act_cat  This array contains 0s for all the categories except the active rowcategory (9 times 1).  =(arr_pnt_cat=active_row) * arr_pnt_cat 
With exactly the same logic used to build up the main data series we can create the data series for the black outline:
arr_a1  Comparing to arr_01 the only difference is in the 10 matrix: here we use the activecategory based arr_ac t_cat instead of arr_pnt_cat which contains all of the indexes. 
=1MMULT(–(arr_act_cat=arr_cat),rng_h)* ISERROR(MATCH(arr_pnt,arr_gap,0)) 
Add the arr_a1 series to the chart. It should be a filled radar chart. Set the marker fill colour to none but set a line at least 2,5 pt wide with black colour.
This circle represents the value of the active category and makes the comparison easier . The only thing to do is to set up a series that contains this value for all of the 180 data points.
arr_a2  Choose the active data from the data table using INDEX and create a vector of this data by multiplying with a 1vector. If the active row is not in the data table, we do not need this circle, this is why multiplied with is_in_rng  =(1 – arr_pnt ^ 0* INDEX(rng_h,active_row))* is_in_rng 
Where is_in_rng is:
is_in_rng  We have chosen this nice formula to determine if the active row is within the data range or not. If the active row is below the table, MEDIAN gives back the last row of the table, so the equation will be false. Similarly when the active row is above the table MEDIAN gives the first row of rng_h (which is CELL(“row”,rng_h) so the equation will be false too. 
=CELL(“row”)= MEDIAN( CELL(“row”), CELL(“row”,rng_h), MAX(ROW(rng_h))) 
Add the series arr_a2 to the chart. It should be filled radar. Set the marker fill colour to none but set a line at least 2,5 pt wide with black colour and dotted style.
Because cell selection does not trigger calculation, you need to add a oneliner code to the worksheet object. In the VB Editor click on the sheet name under your project, and copy this code to the code window:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Target.Parent.Calculate
End Sub
It tells Excel to calculate the worksheet for each and every selection change.
Dynamic Title
Only one thing left: to make the chart title dynamic showing the name and value from the active row.
TT  This name will be added to the chart title. The formula itself is a concatenation on the active data, the name of active category and some line breaks CHAR(10).  =IF(is_in_rng, TEXT(OFFSET(OFFSET(Sheet1!$A$1,active_row,),,1),”0%”) & CHAR(10) & “of Democrats viewed” & CHAR(10) & LOWER(OFFSET(Sheet1!$A$1,active_row,)) & CHAR(10) & “as a top priority” & CHAR(10)&”in 2011″,””) 
On the formula bar assign the name TT to the chart title box.
Download
You can download the example file described in the article:
Enchanted_radar_chart.xlsm
And you can find some more charts with this technique here:
https://sites.google.com/site/e90e50fx/home/infographicchartwithexcel
by Frankens Team
With special thanks to Suzanne Laing for the language corrections!
To learn MUCH more about Excel formulas, please go here:
If you like the Excel formulas information and other powerful techniques shared here on the Excel Hero blog, please sign up for our newsletter:
One Year Anniversary – Excel Hero LinkedIn Group
 “Tease out the methods into simple steps so that it presents in a very understandable way” (Don Love) A great book was also recommended by Don and others: Professional Excel Development, 2nd Ed. by Bovey, Walentin, Bullen and Green
 Document your work
 Share your knowledge with your colleagues
 And an advice for outside consultants: involve employees of the company, educate them the most important or common areas (Zack Barresse)
☺
 Solutions from No One H. and Roberto Mensa:
 =SUM(1*TRIM(MID(SUBSTITUTE(A1,”,”,REPT(” “,999)),ROW(INDIRECT(“1:”&(1+LEN(A1)LEN(SUBSTITUTE(A1,”,”,””)))))*999998,999)))
 =SUM(–MID(SUBSTITUTE(A1,”,”,REPT(” “,99)),1+99*(ROW(OFFSET(A1,,,1+LEN(A1)LEN(SUBSTITUTE(A1,”,”,””))))1),99))
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?
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
coordinates for each cross, that is 4 X and 4 Y coordinates for each cross
and there are 49 Crosses, for a total of 98 series, and 196 coordinates. 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 coordinates.
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 Coordinates
Point 2 in the above sequence means converting each set of
coordinates into polar coordinates consisting of Radius r and Angle Ø.
Solving the above we have
r =sqrt(x^{2}
+ y^{2})
= Sqrt(2.85^{2 }+ 2^{2})
Ø = Atan(y/x)
=Atan(2/2.85)
This is done for every end point of each cross section, 98 pairs
of X, Y Coordinates.
Now we have the polar coordinates of the end points, we can
setup the rotation equations.
That is the equations to convert the original polar
coordinates 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 coordinates, 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 coordinates 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/automatingrepetitivetasks.
The VBA routine is available in Module
2 of the attached Sample File, “Load_Named_Ranges()”.
Add Chart Series
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 
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 variablet = 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
Download
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.
Download here: Motion Induced Blindness.xlsm
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.
For more about my Excel work please visit:
http://chandoo.org/wp/abouthui/